Prepare a SQL Statement for Subsequent Runs

TimesTen application is recommended to prepare a single SQL statement and run it as many times as needed without re-preparing the same SQL statement each time it ran.

The ttIsql utility has a set of built-in commands to work with prepared SQL statements.

These commands are summarized below:

prepare - Prepares a SQL statement. Corresponds to a SQLPrepare ODBC call.
exec - Runs a prepared statement. Corresponds to a SQLExecute ODBC call.
execandfetch - Runs a previously prepared statement and fetches all result rows. Corresponds to a SQLExecute call followed by one or more calls to SQLFetch.
fetchone - Fetches only one row for a previously run statement. Corresponds to exactly one SQLFetch call.
fetchall - Fetches all result rows for a previously run statement. Corresponds to one or more SQLFetch calls.
close - Closes the result set cursor on a previously run statement that generated a result set. Corresponds to a SQLFreeStmt call with the SQL_CLOSE option.
free - Closes and deletes a previously prepared statement. Corresponds to a SQLFreeStmt call with the SQL_DROP option.
describe * - Describes all the prepared statements including the input parameters and the result columns.

The ttIsql utility prepared statement commands also handle SQL statement parameter markers. When parameter markers are included in a prepared SQL statement, ttIsql automatically prompts by name for the value of each parameter in the statement at runtime.

This example uses the prepared statement commands of the ttIsql utility to prepare an INSERT statement into a table containing a NUMBER and a CHAR column. The statement is prepared and then runs twice with different values for each of the statement's two parameters. The ttIsql utility timing command is used to display the elapsed time required to run the primary ODBC function call associated with each command.

Command> connect "DSN=database1";
Connection successful: 
DSN=database1;DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Command> timing 1;
Command> create table t1 (key number not null primary key, value char(20));
Execution time (SQLExecute) = 0.007247 seconds.

Command> prepare insert into t1 values (:f, :g);
Execution time (SQLPrepare) = 0.000603 seconds.

Command> exec;
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/' to leave the remaining parameters unbound and execute the command.
Enter Parameter 1 'F' (NUMBER) > 1;
Enter Parameter 2 'G' (CHAR) > 'abc';
1 row inserted.
Execution time (SQLExecute) = 0.000454 seconds.

Command> exec;
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/' to leave the remaining parameters unbound and execute the command.
Enter Parameter 1 'F' (NUMBER) > 2;
Enter Parameter 2 'G' (CHAR) > 'def';
1 row inserted.
Execution time (SQLExecute) = 0.000300 seconds.

Command> free;
Command> select * from t1;
< 1, abc >
< 2, def >
2 rows found.
Execution time (SQLExecute + Fetch Loop) = 0.000226 seconds.

The prepare command is immediately followed by the SQL statement to prepare. Whenever a SQL statement is prepared in ttIsql, a unique command ID is assigned to the prepared statement. The ttIsql utility uses this ID to keep track of multiple prepared statements. A maximum of 256 prepared statements per connection can exist in a ttIsql session simultaneously. When the free command runs, the command ID is automatically disassociated from the prepared SQL statement.

To see the command IDs generated by ttIsql when using the prepared statement commands, use the describe * command to list all prepared statements with their IDs. Command IDs can be referenced explicitly when using ttIsql's prepared statement commands.

This example prepares and runs a SELECT statement with a predicate containing one NUMBER parameter. The exec 2 and exec 3 run the prepared statements. After the execution of the 2 and 3 prepared statements, it shows “Cursor is open. Ncharencoding is LOCALE.”

Command> prepare 1 select 1 from dual;
Command> prepare 2 select 2 from dual;
Command> prepare 3 select 3 from dual;
Command>exec 2;
Command>exec 3;
Command>describe *;

There are 3 prepared commands.

Prepared Statement [1];
SQL: select 1 from dual

Columns:
EXP		NUMBER(10) Not NULL 

Prepared Statement [2];
SQL: select 2 from dual

Cursor is open. Ncharencoding is LOCALE.
Columns:
EXP		NUMBER(10) Not NULL 

Prepared Statement [3];
SQL: select 3 from dual

Cursor is open. Ncharencoding is LOCALE.
Columns:
EXP	
result columns.

This example shows free 2 command to remove the prepared command, prepare 2.

Command>free 2;
Command>describe *;

Prepared Statement [1];
SQL: select 1 from dual

Columns:
EXP	NUMBER(10) Not NULL 

Prepared Statement [3];
SQL: select 3 from dual

Cursor is open. Ncharencoding is LOCALE.
Columns:
EXP	NUMBER(10) Not NULL 

Command>exec 2;
The prepared command with id=2 was not found.
The command failed.

This example uses the prepared command, prepare 10 and prepare 11 to INSERT values into the table t2 and run the command to insert two rows into the table. The fetchone command is used to fetch only one result row generated by the statement. The execandfetch 12 command displays all rows of the table t2.

Command> create table t2 ( a int, b varchar(30));
Command> create sequence s;
Command> set autocommit 0;
Command> prepare 10 insert into t2 values( s.nextval, 'A');
Command> prepare 11 insert into t2 values( s.nextval, 'B');
Command> prepare 12 select * from t2;
Command> describe *;

There are 3 prepared commands.

Prepared Statement [10]:
  SQL: insert into t2 values( s.nextval, 'A')
  Columns:
    (none)

Prepared Statement [11]:
  SQL: insert into t2 values( s.nextval, 'B')
  Columns:
    (none)

Prepared Statement [12]:
  SQL: select * from t2
  Columns:
    A        NUMBER (38)
    B        VARCHAR2 (30)

Command> exec 10;
1 row inserted.
Command> exec 11;
1 row inserted.
Command> exec 12;

Command> fetchone;
< 1, A >
1 row found.

Command> fetchone;
< 2, B >
1 row found.

Command> execandfetch 12;
< 1, A >
< 2, B >
2 rows found.

Command> exec 10;
1 row inserted.
Command> execandfetch 12;
< 1, A >
< 2, B >
< 3, A >
3 rows found.
Command>