PREPARE SQL STMT

PREPARE SQL STMT mk Sun, 10/13/2013 - 16:33

The PREPARE statement compiles an SQL statement into its execution plan on the server. The plan is given a unique id number and stored in the query cache of the user session. A subsequent EXECUTE command retrieves it from the cache, applies the given parameter values and executes it. When done you can close a prepared statement (or ALL) via DEALLOCATE command (available from release Jun2020 (11.37.7) onwards).

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 15(...)
+----------+---------+-------+--------+--------+---------------+
| 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

sql>select * from sys.prepared_statements;
+-----------+----------+-------------+----------------------------------------------+----------------------------+
| sessionid | username | statementid | statement                                    | created                    |
+===========+==========+=============+==============================================+============================+
|         1 | monetdb  |          15 | prepare select * from tables where name = ?; | 2020-06-18 18:25:47.499219 |
+-----------+----------+-------------+----------------------------------------------+----------------------------+
1 tuple

sql>select * from sys.prepared_statements_args;
+-------------+----------+-------------+------------+-------+--------+--------+--------+---------------+
| statementid | type     | type_digits | type_scale | inout | number | schema | table  | column        |
+=============+==========+=============+============+=======+========+========+========+===============+
|          15 | int      |          32 |          0 |     0 |      0 | null   | tables | id            |
|          15 | varchar  |        1024 |          0 |     0 |      1 | null   | tables | name          |
|          15 | int      |          32 |          0 |     0 |      2 | null   | tables | schema_id     |
|          15 | varchar  |     1048576 |          0 |     0 |      3 | null   | tables | query         |
|          15 | smallint |          16 |          0 |     0 |      4 | null   | tables | type          |
|          15 | boolean  |           1 |          0 |     0 |      5 | null   | tables | system        |
|          15 | smallint |          16 |          0 |     0 |      6 | null   | tables | commit_action |
|          15 | smallint |          16 |          0 |     0 |      7 | null   | tables | access        |
|          15 | tinyint  |           8 |          0 |     0 |      8 | null   | tables | temporary     |
|          15 | varchar  |        1024 |          0 |     1 |      9 | null   | null   | null          |
+-------------+----------+-------------+------------+-------+--------+--------+--------+---------------+
10 tuples

sql>execute 15('_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

sql>exec 15('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

sql>deallocate 15;
operation successful
sql>

As shown above you can query prepared statements information from system views sys.prepared_statements and sys.prepared_statements_args (available from release Jun2020 (11.37.7) onwards).