Prepared Statements in ODBC/JDBC

Note to reviewers: More complete documentation for the ODBC and JDBC drivers is being planned. This Release Notes section deals only with prepared statements.

Vertica now supports prepared statements in both the ODBC and JDBC drivers.

ODBC Driver

When you call SQLPrepare() with a string containing a SQL statement, the driver sends the string to the server and stores the statement identifier for later execution. The string is stored on the server and thus is not sent again when the prepared statement is executed more than once.
When you call SQLBindParameters(),the driver binds the statement parameters but does not communicate with the server.
Question for reviewers: is SQLBindParameters singular or plural? The text does not agree with the example code. Has the example been tested?
When you call SQLExecute, the driver sends the statement identifier and parameter values to the server and returns the result set or an error. The driver also returns semantic and syntactic errors at this point.

ODBC Example

SQLPrepare(hstmt,

"UPDATE Parts SET Price = ? WHERE PartID = ?", SQL_NTS);

SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,

SQL_C_FLOAT, SQL_REAL, 7, 0, &Price, 0, &PriceInd);

SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,

SQL_C_ULONG, SQL_INTEGER, 10, 0, &PartID, 0, &PartIDInd);

SQLExecute(hstmt);

JDBC Driver

The following functionality is now supported in the JDBC driver:

When you call prepareStatement() with a string containing an SQL statement, the driver sends it to the server and stores the statement identifier for later execution. The string is stored on the server and thus is not sent again when the prepared statement is executed more than once.
When you call any of the following methods, the driver binds the statement parameters but does not communicate with the server:
setNull(), setInt(), setLong(), setString(), setBoolean(), setDouble(), setFloat(), setDate(), setTime(), setTimestamp()
When you call execute(), executeQuery(), or executeUpdate(), the driver sends the statement identifier and parameter values to the server and returns a result set or an error. The driver also returns semantic and syntactic errors at this point.

JDBC Example

PreparedStatement pst = dbconn.prepareStatement(

"select * from fact where type=?");

pst.setString(1, "trades");

ResultSet rs = pst.executeQuery();

JDBCSetupTest.printResults(rs, resultStream);

Notes

SQL PREPARE statement are not supported.
Named prepared statements are not supported.
Error conditions, codes and messages are determined by ODBC and JDBC standards.
The ODBC and JDBC drivers do not always return errors when statements are prepared. Some errors are returned when the prepared statement is executed.

For More Information

See the following sections of the SQL Programmer's Guide: