Skip navigation links

Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 12 SQL Statement Syntax :: 12.7 SQL Syntax for Prepared Statements
13 Storage Engines »
Section Navigation      [Toggle]

12.7. SQL Syntax for Prepared Statements

Support for server-side prepared statements was added in MySQL 4.1. This support takes advantage of the efficient client/server binary protocol, provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET. For example, the C API provides a set of function calls that make up its prepared statement API. See Section 17.2.4, “C API Prepared Statements”. Other language interfaces can provide support for prepared statements that use the binary protocol by linking in the C client library, one example being the mysqli extension, available in PHP 5.0 and later.

Beginning with MySQL 4.1.3, an alternative SQL interface to prepared statements is available. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level:

  • You can use it when no programming interface is available to you.

  • You can use it from any program that allows you to send SQL statements to the server to be executed, such as the mysql client program.

  • You can use it even if the client is using an old version of the client library. The only requirement is that you be able to connect to a server that is recent enough to support SQL syntax for prepared statements.

SQL syntax for prepared statements is intended to be used for situations such as these:

  • You want to test how prepared statements work in your application before coding it.

  • An application has problems executing prepared statements and you want to determine interactively what the problem is.

  • You want to create a test case that describes a problem you are having with prepared statements, so that you can file a bug report.

  • You need to use prepared statements but do not have access to a programming API that supports them.

SQL syntax for prepared statements is based on three SQL statements:

  • PREPARE stmt_name FROM preparable_stmt

    The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, “?” characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The “?” characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

    If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.

    The scope of a prepared statement is the client session within which it is created. Other clients cannot see it.

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

    After preparing a statement, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter markers in the statement.

    You can execute a given prepared statement multiple times, passing different variables to it or setting the variables to different values before each execution.

  • {DEALLOCATE | DROP} PREPARE stmt_name

    To deallocate a prepared statement, use the DEALLOCATE PREPARE statement. Attempting to execute a prepared statement after deallocating it results in an error.

A prepared statement is specific to the connection in which it was created. If you terminate a client session without deallocating a previously prepared statement, the server deallocates it automatically.

To guard against too many prepared statements being created simultaneously, the max_prepared_stmt_count system variable can be set.

The following SQL statements can be used in prepared statements: ALTER TABLE, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yet supported.

The following examples show two equivalent ways of preparing a statement that computes the hypotenuse of a triangle given the lengths of the two sides.

The first example shows how to create a prepared statement by using a string literal to supply the text of the statement: 

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

The second example is similar, but supplies the text of the statement as a user variable: 

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

SQL syntax for prepared statements cannot be used in nested fashion. That is, a statement passed to PREPARE cannot itself be a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

SQL syntax for prepared statements is distinct from using prepared statement API calls. For example, you cannot use the mysql_stmt_prepare() C API function to prepare a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement.

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by “;” characters).


User Comments

Posted by Luciano Fantuzzi on April 14 2007 12:37am[Delete] [Edit]

Keywords: ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

As the manual says, you can't use a PREPARE statement inside a FUNCTION or a TRIGGER. Besides, if you're trying to CALL to a PROCEDURE (inside the FUNCTION or TRIGGER) which uses a PREPARE statement (or other command not supported) it won't neither work.

The following examples will result in error:

Example 1)

|  ------------------------------------------------
|  mysql> DELIMITER //
|      -> CREATE FUNCTION a() RETURNS TINYINT(1)
|      -> BEGIN
|      -> SET @s:="SELECT USER()";
|      -> PREPARE stmt FROM @s;
|      -> EXECUTE stmt;
|      -> END;//
|  ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
|  ------------------------------------------------

Example 2)
|  ------------------------------------------------
|  mysql> DELIMITER //
|      -> CREATE PROCEDURE a(IN vStmt VARCHAR(1024))
|      -> BEGIN
|      -> SET @s:=vStmt;
|      -> PREPARE stmt FROM @s;
|      -> EXECUTE stmt;
|      -> END;//
|  Query OK, 0 rows affected (0.05 sec)
|
|  mysql> CREATE FUNCTION b()RETURNS TINYINT(1)
|      -> BEGIN
|      -> CALL a("SELECT USER()");
|      -> RETURN TRUE;
|      -> END;//
|  Query OK, 0 rows affected (0.06 sec)
|
|  mysql> SELECT b();//
|  ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
|  ------------------------------------------------

Hope be helpful.

Posted by J Jorgenson on July 26 2007 6:20pm[Delete] [Edit]

Beware that the result(s) of a Stored Procedure are create for each SELECT/EXECUTE where the data is not put 'INTO' a variable.

An 'EXECUTE prep_stmt USING @var' does NOT allow saving the result INTO a @variable (as of MySQL 5.0.20), thus you'll have another resultset.


Add your own comment.