DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) SQLPS

Info Catalog (mysql.info.gz) Replication SQL (mysql.info.gz) SQL Syntax
 
 13.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) or MySQL Connector/J (for Java programs).  For
 example, the C API provides a set of function calls that make up its
 prepared statement API.   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.  (The mysqli
 extension in PHP 5.0 does this, for example.)
 
 Beginning with MySQL 4.1.3, an alternative interface to prepared
 statements is available:  SQL syntax for prepared statements.  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 will work in your
      application before doing the application coding.  Or perhaps an
      application has a problem executing prepared statements and you
      want to determine what the problem is interactively.
 
    * 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;
 
      EXECUTE STMT_NAME [USING @VAR_NAME [, @VAR_NAME] ...];
 
      {DEALLOCATE | DROP} PREPARE STMT_NAME;
 
 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.
 
 If a prepared statement exists with the same name, 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 will exist.
 
 The scope of a prepared statement is the client session within which it
 is created. Other clients cannot see it.
 
 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 it
 different variables or setting the variables to different values before
 each execution.
 
 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 statements can be used as 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
 with 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.
 
 Also, 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.
 
Info Catalog (mysql.info.gz) Replication SQL (mysql.info.gz) SQL Syntax
automatically generated byinfo2html