Skip to main content

PREPARE SQL STMT

The PREPARE statement compiles an SQL statement into its execution plan on the server. The plan is given a name and stored in the query cache. A subsequent EXECUTE command retrieves it from the cache, applies the given parameter values and executes it.

PREPARE statement executions can be given positional arguments to replace any literal constant in the query.  Each argument is denoted with a '?'.

sql>prepare select * from tables where name = ?;
execute prepared statement using: EXEC 5(...)
clk: 4.123 ms
+----------+---------+-------+--------+--------+---------------+
| type     | digits  | scale | schema | table  | column        |
+==========+=========+=======+========+========+===============+
| int      |      32 |     0 |        | tables | id            |
| varchar  |    1024 |     0 |        | tables | name          |
| int      |      32 |     0 |        | tables | schema_id     |
| varchar  | 1048576 |     0 |        | tables | query         |
| smallint |      16 |     0 |        | tables | type          |
| boolean  |       1 |     0 |        | tables | system        |
| smallint |      16 |     0 |        | tables | commit_action |
| smallint |      16 |     0 |        | tables | access        |
| tinyint  |       8 |     0 |        | tables | temporary     |
| varchar  |    1024 |     0 | null   | null   | null          |
+----------+---------+-------+--------+--------+---------------+
10 tuples
clk: 4.123 ms

sql>execute 5('_tables');
+------+---------+-----------+-------+------+--------+---------------+--------+-----------+
| id   | name    | schema_id | query | type | system | commit_action | access | temporary |
+======+=========+===========+=======+======+========+===============+========+===========+
| 2067 | _tables |      2000 | null  |   10 | true   |             0 |      0 |         0 |
| 2115 | _tables |      2114 | null  |   10 | true   |             2 |      0 |         0 |
+------+---------+-----------+-------+------+--------+---------------+--------+-----------+
2 tuples
clk: 2.692 ms

sql>exec 5('table_types');
+------+-------------+-----------+-------+------+--------+---------------+--------+-----------+
| id   | name        | schema_id | query | type | system | commit_action | access | temporary |
+======+=============+===========+=======+======+========+===============+========+===========+
| 7322 | table_types |      2000 | null  |   10 | true   |             0 |      1 |         0 |
+------+-------------+-----------+-------+------+--------+---------------+--------+-----------+
1 tuple
clk: 2.364 ms