O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required