(mysql.info) sqlps

 
 13.7 SQL Syntax for Prepared Statements
 =======================================
 
 MySQL 5.0 provides support for server-side prepared statements. This
 support takes advantage of the efficient client/server binary protocol
 implemented in MySQL 4.1, 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 
 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 in
 PHP 5.0 (http://php.net/mysqli).
 
 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.
 
      If you terminate a client session without deallocating a
      previously prepared statement, the server deallocates it
      automatically.
 
 The following SQL statements can be used in prepared statements:
 `CREATE TABLE', `DELETE', `DO', `INSERT', `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 cannot be used within stored
 routines (procedures or functions), or triggers. This restriction is
 lifted as of MySQL 5.0.13 for stored procedures, but not for stored
 functions or triggers.
 
 As of MySQL 5.0.7, placeholders can be used for the arguments of the
 `LIMIT' clause when using prepared statements. See  select.
Info Catalog
(mysql.info) replication-sql
(mysql.info) sql-syntax
automatically generated byinfo2html