Understanding Mysql Prepare Preprocessing statements _mysql

MySQL 5.1 provides support for prefabricated statements on the server side. If you use the appropriate client-side programming interface, this support can play the advantage of an efficient client/server binary protocol implemented in MySQL 4.1. The candidate interfaces include the MySQL C API client library (for C programs), the MySQL connector/j (for Java programs), and the MySQL connector/net. For example, the C API can provide a set of function calls that make up a prefab statement API. Other language interfaces can provide support for prefabricated statements that use binary protocols (linked in the C client library). For prefabricated statements, there is also a SQL interface to take advantage of. This interface is less efficient than using binary protocols throughout the Prefab statement API, but it does not require programming, because at the SQL level, you can use this interface directly :

· You can use this interface when you are unable to take advantage of the programming interface.

· Some programs allow you to send SQL statements to servers that will be executed, such as the MySQL client program. You can use this interface from these programs.

· You can use this interface even if the client is using an older version of the client library. The only requirement is that you be able to connect to a server that supports the SQL syntax of a prefab statement.

The SQL syntax for a prefab statement is used in the following situations:

· Before you make your code, you want to test how a prefab statement runs in your application. Or maybe an application has a problem executing a prefab statement, and you want to determine what the problem is.

· You want to create a test case that describes the problem that occurs when you use a prefab statement so that you can prepare a bug report.

· You need to use prefabricated statements, but you cannot use programming APIs that support prefabricated statements.

The SQL syntax for a prefab statement 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 is used to prepare a statement and give it a name stmt_name, which can then be referenced later. The statement name is not sensitive to the case. Preparable_stmt can be a literal string, or it can be a user variable that contains the statement text. The text must show a single SQL statement, not multiple statements. Using this statement, '? ' Characters can be used to make parameters that indicate where the data values are combined with the query when you execute the query. ‘?' Characters should not be quoted, even if you want to combine them with string values, and do not enclose them in quotes. Parameter makers can only be used where data values should appear, not in SQL keywords and identifiers.

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

The scope of a prefab statement is a client session. Within this session, the statement is created. It is not visible to other clients.

After you have prepared a statement, you can execute it by using an EXECUTE statement that references a prefab statement name. If a prefab statement contains any parameter manufacturing characters, you must provide a using clause that enumerates the user variables that contain the values to be combined with the parameters. Parameter values can only be supplied by a user variable, and the Using clause must accurately indicate the user variable. The number of user variables is as large as the number of parameter manufacturers in the statement.

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

To deallocate a prefab statement, you use the Deallocate prepare statement. An attempt to execute a prefab statement after an assignment is released can result in an error.

If you terminate a client session and you do not deallocate previously prefabricated statements, the server is automatically released from assignment.

The following SQL statements can be used in prefabricated statements: CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, update, and most show statements. No other statements are currently supported.

The following example shows two ways to prepare a statement. This statement is used to compute the hypotenuse of a triangle when a length of two sides is given.

The first example shows how to create a prefab statement by using a literal string to provide 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, and the difference is that the text of the statement is provided 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 |
+------------+
|     Ten |
+------------+
mysql> deallocate PREPARE stmt2;

For prepared statements, you can use the position retention character. The following statement returns a row from the TB1 table:

Mysql> SET @a=1;

Mysql> PREPARE STMT from "select * from TBL LIMIT?";

mysql> EXECUTE STMT USING @a;

The following statement returns the second to sixth row from the TB1 table:

mysql> SET @skip = 1; SET @numrows =5;

Mysql> PREPARE STMT from "select * from TBL LIMIT?,?";

mysql> EXECUTE STMT USING @skip, @numrows;

The SQL syntax for a prefab statement cannot be used in a nested style. That is, the statement that is passed to prepare cannot itself be a prepare, execute, or deallocate prepare statement.

The SQL syntax for a prefab statement differs from the use of a Prefab statement API call. For example, you cannot use the Mysql_stmt_prepare () C API function to prepare a prepare, execute, or deallocate prepare statement.

The SQL syntax for a prefab statement can be used in a stored procedure, but it cannot be used in a stored function or triggering program.

The above is the entire content of this article, I hope to help you learn.