Prepare Statement

The PREPARE statement compiles an SQL statement into its execution plan on the server. This is useful for statements which need to be executed many times but with different values each time, such as an INSERT or UPDATE or SELECT query. 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.

prepare-statement:
   PREPARE SQL_DML_statement-with-optional-question_mark-parameter-markers

call-statement:
   EXECUTE prepared-SQL-id ( [ parm1_val [ , parm2_val ] [, ... ] ] )

deallocate-statement:
   DEALLOCATE [ PREPARE ] { prepared-SQL-id | ALL }

Note: Instead of keyword EXECUTE you may also use EXEC.

You can query prepared statements information from system views sys.prepared_statements

Examples

PREPARE INSERT INTO towns (name, country, citizens) VALUES (?, ?, ?);

-- to find out which prepared statement id is assigned, run query:
SELECT statementid, statement, created FROM sys.prepared_statements;

-- example id is 0
EXECUTE 0('Amsterdam', 'NL', 856124);
EXECUTE 0('Berlin', 'DE', 3715930);

PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?;

-- to find out which prepared statement id is assigned, run query:
SELECT statementid, statement, created FROM sys.prepared_statements
 WHERE statement ILIKE 'PREPARE SELECT id, name, schema_id FROM tables WHERE name like ?%';

-- example id is 1
EXECUTE 1( '%type%' );
EXECUTE 1('%id%');

DEALLOCATE 1;
DEALLOCATE ALL;

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>