1. Operations Guide
  2. Using the ttIsql Utility
  3. Using ttIsql with Prepared and Parameterized SQL Statements

Using ttIsql with Prepared and Parameterized SQL Statements

Preparing a SQL statement just once and then running it multiple times is much more efficient for TimesTen applications than re-preparing the statement each time it is to be run. 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 previously 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.

  • fetchall - Fetches all result rows for a previously run statement. Corresponds to one or more SQLFetch calls.

  • fetchone - Fetches only one row for a previously run statement. Corresponds to exactly one SQLFetch call.

  • 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 a previously prepared statement. Corresponds to a SQLFreeStmt call with the SQL_DROP option.

  • describe - Describes the prepared statement 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 for the value of each parameter in the statement at runtime.

The example below 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 help 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.

Command> disconnect;
Disconnecting...
Execution time (SQLDisconnect) = 2.911396 seconds.

In the example above, 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 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, set the verbosity level to 4 using the verbosity command before preparing the statement, or 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. See ttIsql in the Oracle TimesTen In-Memory Database Reference or type help at the ttIsql command prompt.

The example below prepares and runs a SELECT statement with a predicate containing one NUMBER parameter. The fetchone command is used to fetch the result row generated by the statement. The showplan command is used to display the execution plan used by the TimesTen query optimizer when the statement runs. In addition, the verbosity level is set to 4 so that the command ID used by ttIsql to keep track of the prepared statement is displayed. 

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

Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
The command succeeded.
Command> INSERT INTO T1 VALUES (1, 'abc');
1 row inserted.
The command succeeded.
Command> autocommit 0;
Command> showplan 1;
Command> verbosity 4;
The command succeeded.
Command> prepare SELECT * FROM T1 WHERE KEY=?;
Assigning new prepared command id = 0.

Query Optimizer Plan:
 
  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkRangeScan
  TBLNAME:             T1
  IXNAME:              T1
  INDEXED CONDITION:   T1.KEY = _QMARK_1
  NOT INDEXED:         <NULL>
 
The command succeeded.
Command> exec;

Executing prepared command id = 0.
 
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 '_QMARK_1' (NUMBER) > 1
The command succeeded.
Command> fetchone;
Fetching prepared command id = 0.
< 1, abc                                                              >
1 row found.
The command succeeded.
Command> close;
Closing prepared command id = 0.
The command succeeded.
Command> free;
Freeing prepared command id = 0.
The command succeeded.
Command> commit;
The command succeeded.
Command> disconnect;
Disconnecting...
The command succeeded.
Command>

Note:

See Introduction to PL/SQL in TimesTen in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.