3. Prepared statements and batches

Prepares one statement, executes it three times with different values, frees it, and then runs a batch insert inside a transaction. Nothing here is OriginChainDB-specific — it is ordinary JDBC — which is the point.

// One PreparedStatement, executed many times, then a batch insert.
// The driver turns this into the RPC procedure family on its own -
// you never name those procedures yourself.
try (Connection conn = DriverManager.getConnection(url, user, password)) {

    try (PreparedStatement ps = conn.prepareStatement(
            "SELECT id, customer_ref FROM invoices"
          + " WHERE status = ? AND amount_cents > ?")) {

        for (String status : List.of("open", "settled", "void")) {
            ps.setString(1, status);
            ps.setInt(2, 50000);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(status + " " + rs.getInt("id"));
                }
            }
        }
    }   // close() frees the server-side handle

    conn.setAutoCommit(false);
    try (PreparedStatement ins = conn.prepareStatement(
            "INSERT INTO invoices (id, customer_ref, amount_cents, status)"
          + " VALUES (?, ?, ?, ?)")) {
        for (Object[] row : rows) {
            ins.setInt(1, (Integer) row[0]);
            ins.setString(2, (String) row[1]);
            ins.setInt(3, (Integer) row[2]);
            ins.setString(4, (String) row[3]);
            ins.addBatch();
        }
        int[] counts = ins.executeBatch();   // every statement runs
        System.out.println("inserted " + counts.length + " rows");
    }
    conn.commit();
}

A SQL Server driver does not send that statement the same way twice. It switches to a prepared handle, and which call it uses depends on the driver and on how many times you have executed:

execution 1   sp_executesql   statement text + parameter declarations
execution 2   sp_prepexec     prepare and execute in one round trip
                              -> returns a handle
execution 3   sp_execute      the handle, with fresh values
close         sp_unprepare    free the handle

All of it is served. That matters more than it sounds: a listener that answered only the first form would make "the driver works" true for exactly one execution of any prepared statement and false from the second onward — and false from the first for ODBC clients, which reach for the handle immediately. The interop battery asserts the whole family through the driver's own handle, not through our encoder.

-- Calling a procedure YOU wrote is a different thing, and is refused.
EXEC dbo.settle_invoice @id = 4101;
-- error: that procedure surface does not exist on this engine

OriginChainDB's own procedures are written in a PL/pgSQL-shaped subset and called over the SQL endpoint or the PostgreSQL wire. The TDS reference shows the same procedure written both ways.

• Handles are per connection. They are freed on every teardown path, they are never recycled after a close, and one connection can never see another's.
• executeBatch runs every statement in the batch. Earlier builds parsed only the first request in a message and dropped the rest, reporting success for statements that never ran; that is fixed and pinned.
• OUTPUT parameters are refused, because there is no variable-assignment surface that could produce one. Return values through a result set instead.
• Integer, floating point, bit, string, numeric and unique-identifier parameters decode exactly. Date and time parameters are best-effort in this preview — their epoch base differs from the engine's stored form.