PREPARE statement

You are viewing an old version of this article. View the current version here.

Syntax

PREPARE stmt_name FROM preparable_stmt

Description

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 (not a local variable, an SQL expression or a subquery) 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.

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

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.

Prepared statements can be PREPAREd and EXECUTEd in a stored procedure, but not in a stored function or trigger. Also, even if the statement is PREPAREd in a procedure, it will not be deallocated when the procedure execution ends.

A prepared statement can access user-defined variables, but not local variables or procedure's parameters.

The FOUND_ROWS() and ROW_COUNT() functions, if called immediatly after EXECUTE, return the number of rows read or affected by the prepared statements; however, if they are called after DEALLOCATE PREPARE, they provide information about this statement. If the prepared statement produces error or warnings, GET DIAGNOSTICS return information about them. DEALLOCATE PREPARE shouldn't clear the diagnostics area, unless it produces an error.

A prepared statement is executed with EXECUTE and released with DEALLOCATE PREPARE.

The max_prepared_stmt_count server system variable determines the number of allowed prepared statements that can be prepared on the server. If it is set to 0, prepared statements are not allowed. If the limit is reached, an error similar to the following will be produced:

ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 0)

Permitted statements

Not all statements can be prepared. Only the following SQL commands are permitted:

ALTER TABLE
ANALYZE TABLE
BINLOG
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DESCRIBE
DO
EXPLAIN
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES | QUERY CACHE | TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS}
GRANT
INSERT
INSTALL {PLUGIN | SONAME}
HANDLER READ
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
ROLLBACK
SELECT
SET
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
SET ROLE
SET SQL_LOG_BIN
SET TRANSACTION ISOLATION LEVEL
SHOW EXPLAIN
SHOW {DATABASES | TABLES | OPEN TABLES | TABLES STATUS | COLUMNS | INDEX | TRIGGERS | EVENTS | GRANTS | CHARACTER SET | COLLATION | ENGINES | PLUGINS [SONAME] | PRIVILEGES | PROCESSLIST | PROFILE | PROFILES | VARIABLES | STATUS | WARNINGS | ERRORS | TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS | AUTHORS | CONTRIBUTORS}
SHOW CREATE {DATABASE | TABLE | VIEW | PROCEDURE | FUNCTION | TRIGGER | EVENT}
SHOW {FUNCTION | PROCEDURE} CODE
SHOW BINLOG EVENTS
SHOW SLAVE HOSTS
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
SHUTDOWN
UNINSTALL {PLUGIN | SONAME}
UPDATE

Synonyms are not listed here, but can be used. For example, DESC can be used instead of DESCRIBE.

MariaDB starting with 10.1.1

Compound statements can be prepared too.

Note that if a statement can be run in a stored routine, it will work even if it is called by a prepared statement. For example, SIGNAL can't be directly prepared. However, it is allowed in stored routines. If the x() procedure contains SIGNAL, you can still prepare and execute the 'CALL x();' prepared statement.

When PREPARE is used with a statement which is not supported, the following error is produced:

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

Example

create table t1 (a int,b char(10));
insert into t1 values (1,"one"),(2, "two"),(3,"three");
prepare test from "select * from t1 where a=?";
set @param=2;
execute test using @param;
+------+------+
| a    | b    |
+------+------+
|    2 | two  |
+------+------+
set @param=3;
execute test using @param;
+------+-------+
| a    | b     |
+------+-------+
|    3 | three |
+------+-------+
deallocate prepare test;

Since identifiers are not permitted as prepared statements parameters, sometimes it is necessary to dinamically compose an SQL statement. This technique is called dynamic SQL). The following example shows how to use dynamic SQL:

CREATE PROCEDURE test.stmt_test(IN tab_name VARCHAR(64))
BEGIN
	SET @sql = CONCAT('SELECT COUNT(*) FROM ', tab_name);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;

MariaDB [(none)]> CALL test.stmt_test('mysql.user');
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

Use of variables in prepared statements:

PREPARE stmt FROM 'SELECT @x;';

SET @x = 1;

EXECUTE stmt;
+------+
| @x   |
+------+
|    1 |
+------+

SET @x = 0;

EXECUTE stmt;
+------+
| @x   |
+------+
|    0 |
+------+

DEALLOCATE PREPARE stmt;

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.