Prepared statements

This topic describes the prepared statement protocol and the support provided by the protocol for prepared statements. This topic also describes how to enable the prepared statement protocol in a Java client.

Introduction to the prepared statement protocol

The prepared statement protocol has two modes.

• Binary mode: This mode is based on the efficient client/server binary protocol. This mode is the most commonly used mode for program development.

• Text mode: This mode is based on SQL syntax. The prepared statements in text mode include PREPARE, EXECUTE, and DEALLOCATE PREPARE statements.

PolarDB-X supports the two modes. Prepared statements that use placeholders for parameter values provide the following benefits:

• Minimized overhead for statement parsing each time a statement is executed. In most cases, database applications process a large number of near-identical statements in which only the values of a few variables are different. To execute these near-identical statements in an efficient manner, you need only to change the values of variables in a prepared statement.

• Protection against SQL injection attacks.

Binary mode

The binary prepared statement protocol supports Java Database Connectivity (JDBC) and various programming languages. For more information about prepared statements supported by MySQL, see Prepared statements. The following list provides the information about prepared statements in PolarDB-X.

• The prepared statement protocol supports the following statements:

  • COM_STMT_PREPARE

  • COM_STMT_EXECUTE

  • COM_STMT_CLOSE

  • COM_STMT_RESET

• The prepared statement protocol supports all DML statements such as SELECT, UPDATE, DELETE, and INSERT.

• The prepared statement protocol does not support statements, excluding DML statements. For example, SHOW and SET statements cannot be used as prepared statements.

Enable the prepared statement protocol in a Java client.

• If you need to use the prepared statement protocol in a Java client, you need to explicitly add the useServerPrepStmts=true parameter to the URL connection string. If this parameter is not specified, prepared statements are processed as common statements by default.

• For example, you can specify the following parameter to enable the prepared statement protocol in the Java client: jdbc:mysql://xxxxxx:3306/xxxxxx? useServerPrepStmts=true .

The following code block provides an example on prepared statements in the Java client:

Class.forName("com.mysql.jdbc.Driver");
Connection connection =  DriverManager.getConnection("jdbc:mysql://xxxxxx:3306/xxxxxx?useServerPrepStmts=true", "xxxxx", "xxxxx");
String sql = "insert into batch values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql); 
preparedStatement.setInt(1, 0);
preparedStatement.setString(2, "polardb-x");
preparedStatement.executeUpdate();

Text mode

Execute the PREPARE statement to configure stmt_name for preparable_stmt. The value of the stmt_name parameter is not case-sensitive. preparable_stmt can be only a single prepared statement.

PREPARE stmt_name FROM preparable_stmt;

Execute the EXECUTE statement to execute the specified prepared statement. If the prepared statement contains parameter placeholders, a USING clause must be used to specify user-defined variables as parameters.

EXECUTE stmt_name [USING @var_name [, @var_name] ...];

Execute the DEALLOCATE PREPARE statement to release the prepared statement.

DEALLOCATE PREPARE stmt_name;

Example:

PREPARE stmt2 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 6;
SET @b = 8;
EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+

DEALLOCATE PREPARE stmt2;