MySQL Stored Procedure Programming

Performing Dynamic SQL with Prepared Statements

MySQL supports a facility known as server-side prepared statements , which provides an API-independent way of preparing a SQL statement for repeated execution efficiently and securely. Prepared statements are interesting from a stored programming perspective because they allow us to create dynamic SQL calls.

We create a prepared statement with the PREPARE statement:

    PREPARE statement_name FROMsql_text

The SQL text may contain placeholders for data values that must be supplied when the SQL is executed. These placeholders are represented by ? characters.

The prepared statement is executed with the, EXECUTE statement:

    EXECUTE statement_name [USING variable [,variable...]]

The USING clause can be used to specify values for the placeholders specified in the PREPARE statement. These must be supplied as user variables (prefixed with the @ character), which we described in Chapter 3.

Finally, we can drop the prepared statement with the DEALLOCATE statement:

    DEALLOCATE PREPARE statement_name

An example of using prepared statements within the MySQL command-line client is shown in Example 5-25.

Example 5-25. Using prepared statements

mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)"; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> mysql> SET @code='QB'; Query OK, 0 rows affected (0.00 sec) mysql> SET @name='MySQL Query Browser'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE prod_insert_stmt ...