New Feature. ISQL statements PREPARE, EXECUTE SQL, DEALLOCATE

[sim1984]

ISQL statements PREPARE, EXECUTE SQL, DEALLOCATE

It is proposed to add several commands to ISQL to make users work in ISQL more productive.

In graphical development environments, the concept of a prepared query is often used. A prepared query with the same text can be executed with different input parameter values. However, in ISQL, this capability is lacking. We propose extending ISQL with the following commands.

PREPARE

Syntax:

PREPARE _stmt_name_ AS <statement> <term>

The command prepares an SQL statement and stores it in ISQL memory under the name _stmt_name_. The prepared statement remains valid until the end of the session or until the DEALLOCATE command is executed.

If a new statement with the same name is prepared, the previous prepared statement is destroyed and the new prepared statement is stored in memory.

A prepared statement can be executed using the EXEC SQL command with a different set of input parameters.

EXEC SQL

Syntax:

EXEC SQL _stmt_name_ USING (<parameter_values>) <term>

<parameter_values> := <literal> [... , <literal>]

The EXEC SQL operator allows you to repeatedly execute a prepared statement with supplied query parameter values. Otherwise, it behaves exactly as if the statement and its supplied values ​​were executed directly in ISQL.

The EXEC SQL statement can be extended to execute a prepared statement in batch mode.

EXEC SQL _stmt_name_ USING BATCH (<parameter_values>) [, ... (<parameter_values>)] <term>

In the future, this operator can be adapted for data import.

EXEC SQL _stmt_name_ FROM CSV '_file_name_' OPTIONS (<options>) <term>

<options> := <option> [, ... <option>]

<option> := _option_name_ = <value>

DEALLOCATE

Syntax:

DEALLOCATE _stmt_name_ <term>

Releases a prepared statement.

Examples

PREPARE mystmt AS INSERT INTO MyTable (a, b) VALUES (?, ?);

EXEC SQL mystmt USING (1, 'a');
EXEC SQL mystmt USING (2, 'b');
...
COMMIT;

DEALLOCATE mystmt;