Prepared Statements, Bind Markers, Batch Files, COPY Caveats, and cqlsh Productivity

Learning outcomes

AtlasMart has moved beyond one-off shell commands. The team needs repeatable schema/data scripts for operators and safe recurring application queries for services. Those are different jobs: cqlsh files and COPY are administrative conveniences, while production request execution belongs in a maintained driver with prepared/bound statements, explicit routing and timeout/retry policy.

1. Draw the boundary: cqlsh is a client, not Cassandra itself

cqlsh ships with Cassandra and connects to one specified node over the native protocol. Commands such as DESCRIBE, TRACING, PAGING, CAPTURE, SOURCE and COPY are shell workflows layered around CQL. An application driver handles topology discovery, connections, pooling, prepared statements, routing, timeouts, retries and result decoding.

2. Make repeatable operator scripts without inventing a “batch” guarantee

# Disposable Chapter 05 cluster: three nodes, three racks, one DC, 16 vnodes/node# If these objects already exist from Chapters 01–04, reuse them instead of recreating them.docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait for node 1 to accept CQL, then start the two peers.docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 java -version

CREATE KEYSPACE IF NOT EXISTS atlasmart_cqlWITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3}AND durable_writes = true;CONSISTENCY QUORUM;CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_id (    product_id text PRIMARY KEY,    name text,    category text,    price_cents bigint,    status text,    description text,    updated_at timestamp);CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_category (    category text,    bucket tinyint,    product_id text,    name text,    status text,    price_cents bigint,    PRIMARY KEY ((category, bucket), product_id));CREATE TABLE IF NOT EXISTS atlasmart_cql.inventory_by_product (    product_id text,    location_id text,    quantity int,    status text,    promo_note text,    PRIMARY KEY ((product_id), location_id));

Create a local text file named chapter05_seed.cql containing ordinary statements:

CONSISTENCY QUORUM;INSERT INTO atlasmart_cql.products_by_id (product_id,name,category,price_cents,status)VALUES ('p-501','Portable SSD','electronics',8900,'active');INSERT INTO atlasmart_cql.products_by_id (product_id,name,category,price_cents,status)VALUES ('p-502','USB-C Hub','electronics',4200,'active');SELECT product_id,name,price_cents,status FROM atlasmart_cql.products_by_id;

docker cp chapter05_seed.cql atlasmart-cass-1:/tmp/chapter05_seed.cqldocker exec atlasmart-cass-1 cqlsh -f /tmp/chapter05_seed.cql# Equivalent from an interactive cqlsh session: SOURCE '/tmp/chapter05_seed.cql';

A batch file here simply means a file containing sequential CQL/cqlsh input. It does not create the atomicity or batch-log semantics of the CQL BEGIN BATCH ... APPLY BATCH statement. Cassandra batches are covered later because using them as a bulk-performance primitive is a classic anti-pattern.

3. cqlsh productivity: paging, capture and COPY have operational scopes

PAGING 50;EXPAND ON;CAPTURE '/tmp/ch05_capture.txt';SELECT product_id,name,category,price_cents,statusFROM atlasmart_cql.products_by_id;CAPTURE OFF;EXPAND OFF;COPY atlasmart_cql.products_by_id(product_id,name,category,price_cents,status)TO '/tmp/ch05_products.csv' WITH HEADER = TRUE;

docker exec atlasmart-cass-1 sh -lc 'cat /tmp/ch05_capture.txt'docker exec atlasmart-cass-1 sh -lc 'cat /tmp/ch05_products.csv' 

COPY is implemented by cqlsh. File paths are therefore meaningful to the machine/container running cqlsh, not magically to every Cassandra node. It is convenient for bounded CSV interchange and labs. For large production migration or continuous ingestion, use workload-appropriate bulk/streaming tooling and measure throughput, retries, backpressure and failure recovery instead of assuming COPY scales because it is easy to type.

4. Prepared statements are protocol objects, not concatenated CQL

The native protocol has distinct PREPARE and EXECUTE messages. A server parses prepared CQL and returns an identifier plus metadata. The driver binds typed values and can use known partition-key variables to compute a routing key. Prepared statements should normally be reused for recurring application requests.

The ASF Java driver release checked for this chapter is 4.19.3. Its Maven group is org.apache.cassandra; the API package names remain com.datastax.oss.driver... for compatibility.

<dependencies>  <dependency>    <groupId>org.apache.cassandra</groupId>    <artifactId>java-driver-core</artifactId>    <version>4.19.3</version>  </dependency></dependencies>

import java.net.InetSocketAddress;import com.datastax.oss.driver.api.core.CqlSession;import com.datastax.oss.driver.api.core.cql.PreparedStatement;import com.datastax.oss.driver.api.core.cql.BoundStatement;import com.datastax.oss.driver.api.core.cql.Row;try (CqlSession session = CqlSession.builder()    .addContactPoint(new InetSocketAddress("atlasmart-cass-1", 9042))    .withLocalDatacenter("dc1")    .build()) {  PreparedStatement insert = session.prepare(      "INSERT INTO atlasmart_cql.products_by_id " +      "(product_id,name,category,price_cents,status) VALUES (?,?,?,?,?)");  session.execute(insert.bind("p-503", "Webcam", "electronics", 6100L, "active"));  PreparedStatement find = session.prepare(      "SELECT product_id,name,price_cents,status " +      "FROM atlasmart_cql.products_by_id WHERE product_id=?");  BoundStatement bound = find.bind("p-503");  Row row = session.execute(bound).one();  System.out.println(row == null ? "not found" : row.getString("name"));}

Run this code from a JVM that can resolve/reach atlasmart-cass-1:9042, such as a build container attached to atlasmart-cassandra. The observable result should print Webcam; also inspect server/client metrics later rather than claiming performance from one call.

5. Deliberately wrong application pattern: CQL string concatenation

// Wrong: query text varies with data and quoting becomes application logic.String cql = "SELECT product_id,name FROM atlasmart_cql.products_by_id " +             "WHERE product_id='" + userSuppliedProductId + "'";session.execute(cql);

Beyond injection/quoting risk, literalized recurring statements prevent clean reuse of prepared metadata and can cause many distinct prepared texts if an application tries to prepare each literal variation. Bind markers keep query structure stable and data typed separately.

Prepared execution still needs operational policy: consistency level, timeouts, cancellation, idempotency classification, retry behavior and schema-change handling. “Prepared” does not mean “transactional,” “exactly once,” or “safe to retry regardless of mutation semantics.”

6. Production judgment and verification

Use cqlsh for controlled interactive/admin work, diagnostics and small bounded import/export. Use a maintained driver for applications. Pin client versions, test native-protocol compatibility, cache/reuse recurring prepared statements, and keep query text bounded. Treat shell history, captured output and CSV files as potentially sensitive data.

Verification checklist

• The -f/SOURCE script executes ordinary statements without claiming CQL batch atomicity.
• PAGING/CAPTURE/COPY behavior is understood as cqlsh client behavior.
• The Java example uses driver 4.19.3, PREPARE/bind semantics and no value concatenation.
• The session declares local datacenter dc1.
• You can explain why COPY is convenient but not a universal bulk-loader.

Summary and next bridge

You now have separate tools for separate responsibilities: cqlsh for controlled operator workflows and the ASF Java driver for recurring application execution. Lesson 5 returns to distributed metadata and watches one schema mutation propagate across nodes under controlled temporary disagreement.

# Chapter-only reset or deliberate preservation# Keep atlasmart_cql if you are continuing directly to the next lesson.# Full reset (deletes only the dedicated course containers/volumes/network)docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-datadocker network rm atlasmart-cassandra

Authoritative references

• Apache Cassandra downloads — Current Cassandra and ASF Java-driver release baseline.
• CQL data definition — Official keyspace/table DDL and schema options.
• CQL data manipulation — Official INSERT/UPDATE/DELETE/SELECT, timestamps, TTL, WRITETIME and filtering semantics.
• cqlsh documentation — Official shell commands, tracing, paging, SOURCE/CAPTURE/COPY behavior and compatibility boundary.
• Native protocol — PREPARE/EXECUTE protocol semantics and version framing.
• Apache Cassandra Java Driver prepared statements — Prepared/bound statement behavior, caching and routing metadata.
• Maven Central: ASF Java driver 4.19.3 — Current artifact coordinates for the Apache Cassandra Java Driver.