17.3. Preparing a Statement


The PREPARE statement is used to define an SQL statement that will be executed later. PREPARE takes two arguments: a name to assign to the statement once it has been prepared, and the text of an SQL statement. Prepared statement names are not case sensitive. The text of the statement can be given either as a literal string or as a user variable containing the statement.

The statement may not be complete, because data values that are unknown at preparation time are represented by question mark ('?') characters that serve as parameter markers. At the time the statement is executed, you provide specific data values, one for each parameter in the statement. The server replaces the markers with the data values to complete the statement. Different values can be used each time the statement is executed.

The following example prepares a statement named namepop. When executed later with a country code as a parameter value, the statement will return a result set containing the corresponding country name and population from the world database.

 mysql> PREPARE namepop FROM '     '> SELECT Name, Population     '> FROM Country     '> WHERE Code = ?     '> '; Query OK, 0 rows affected (0.02 sec) Statement prepared 

The message Statement prepared indicates that the server is ready to execute the namepop statement. On the other hand, if the server finds a problem as it parses the statement during a PREPARE, it returns an error and does not prepare the statement:

 mysql> PREPARE error FROM '     '> SELECT NonExistingColumn     '> FROM Country     '> WHERE Code = ?     '> '; ERROR 1054 (42S22): Unknown column 'NonExistingColumn' in 'field list' 

If you PREPARE a statement using a statement name that already exists, the server first discards the prepared statement currently associated with the name, and then prepares the new statement. If the new statement contains an error and cannot be prepared, the result is that no statement with the given name will exist.

MySQL does not allow every type of SQL statement to be prepared. Those that may be prepared are limited to the following:

  • SELECT statements

  • Statements that modify data: INSERT, REPLACE, UPDATE, and DELETE

  • CREATE TABLE statements

  • SET, DO, and many SHOW statements

A prepared statement exists only for the duration of the session in which it is created, and is visible only to the session in which it is created. When a session ends, all prepared statements for that session are discarded.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net