Reuse a parameterized statement
Connection requirements
This recipe needs an existing listener using shared credentials. A per-user TDS session is read-only. Database RBAC grants or row-level policies require that per-user model, so the writes below do not apply there.
Check credential requirements. Use the HTTP API for authorized writes when this listener is read-only. Current PostgreSQL database logins are also read-only.
Prepare a supported SQL statement and bind new values for each execution. The examples cover repeated execution and a transaction batch.
Before you run it: Per-user credentials and current PostgreSQL database logins are read-only. Driver preparation does not support arbitrary stored procedures.
Start with the Java connection example and invoice table. This is a fragment: supply its url, user and password, import java.sql.* and java.util.List, and define rows as four-value entries (id, customer_ref, amount_cents, status) using unused IDs. The read loop can run without the later insert batch when the connection is read-only.
- Prepare the statementKeep the parameterized query on one connection.
- Bind the next valuesPass values separately from the SQL text.
- Execute and reuseCheck each result before changing the bound values.
- Close the statementRelease its handle and verify any transaction outcome.
Follow the example
- Confirm shared-credential write access.
- Prepare on the connection.
- Bind values and execute.
- Close the statement and check committed rows.
Run the example
// 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();
}Supported driver calls
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 handleThese are driver protocol calls, not procedures you write yourself. The recorded compatibility check exercised repeated execution and closing the handle; check the supported driver and listener version before adopting the pattern.
Unsupported uses
Those procedure names belong to the protocol, not to your schema. Serving them is what makes a prepared statement work; it is not stored-procedure support. Calling a procedure you wrote is refused, as is every other procedure by name or by numeric id.
-- 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 engineOriginChainDB has a separate PL/pgSQL-shaped procedure interface through the SQL endpoint. It is not T-SQL stored-procedure support. See the TDS procedure comparison. The current PostgreSQL database-login path is read-only.
Important details
- 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.
executeBatchruns 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.
Microsoft, SQL Server and T-SQL are trademarks of the Microsoft group of companies. Named here only to describe protocol and dialect compatibility. OriginChainDB is not affiliated with, endorsed by, or sponsored by Microsoft, and does not distribute Microsoft software.
Expected result
The driver uses supported prepared-statement calls without concatenating values into SQL.