TTCmd Reference

A TTCmd object encapsulates a single SQL statement that is used multiple times in an application program. You can think of TTCmd as a value-added C++ wrapper around the ODBC statement handle (SQLHSTMT). TTCmd has three categories of public methods.

Also see TTCmd Usage.

Public Members

Member	Description
`TTCMD_PARAM_INPUTOUTPUT_TYPE`	This is used to specify whether a parameter is input, output, or input/output when registering the parameter. Supported values are `PARAM_IN`, `PARAM_INOUT`, and `PARAM_OUT`. See Registering Parameters.

Public Methods for General Use and Non-Batch Operations

This section summarizes then describes the TTCmd public methods for general use and non-batch operations.

Public Methods Summary for General Use and Non-Batch Operations

Method	Description
`Close()`	Closes the result set when the application has finished fetching rows.
`Drop()`	Frees a prepared SQL statement and all resources associated with it.
`Execute()`	Invokes a SQL statement that has been prepared for execution.
`ExecuteImmediate()`	Invokes a SQL statement that has not been previously prepared.
`FetchNext()`	Fetches rows from the result set, one at a time. It returns 0 when a row was successfully fetched or 1 when no more rows are available.
`getColumn()`	Retrieves the value in the specified column of the current row of the result set.
`getColumnLength()`	Returns the length of the specified column, in bytes.
`getColumnNullable()`	Retrieves the value in the specified column of the current row of the result set and returns a boolean to indicate whether the value is `NULL`.
`getHandle()`	Retrieves the underlying ODBC statement handle.
`getMaxRows()`	Returns the current limit on the number of rows returned by a `SELECT` statement.
`getNextColumn()`	Retrieves the value in the next column of the current row of the result set.
`getNextColumnNullable()`	Retrieves the value in the next column of the current row of the result set and returns a boolean to indicate whether the value is `NULL`.
`getParam()`	Each call gets the output value of a specified output or input/output parameter after executing a prepared SQL statement.
`getQueryThreshold()`	Retrieves the query threshold value.
`getRowCount()`	Returns the number of rows that were affected by the recently executed SQL operation.
`isColumnNull()`	Indicates whether the value in the specified column of the current row is `NULL`.
`Prepare()`	Associates a SQL statement with the `TTCmd` object.
`printColumn()`	Prints the value in the specified column of the current row to an output stream.
`registerParam()`	Registers a parameter for binding. This is required for output or input/output parameters.
`setMaxRows()`	Sets a limit on the number of rows returned by a `SELECT` statement.
`setParam()`	Each call sets the value of a specified parameter before executing a prepared SQL statement.
`setParamLength()`	Sets the length, in bytes, of the specified input parameter.
`setParamNull()`	Sets the value of a parameter to `NULL` before executing a prepared SQL statement.
`setQueryThreshold()`	Sets a threshold time limit for execution of each SQL statement. If it is exceeded, a warning is written to the support log.
`setQueryTimeout()`	Sets a timeout value for SQL statements.

Close()

void Close()

If a SQL SELECT statement is executed using the Execute() method, a cursor is opened which may be used to fetch rows from the result set. When the application is finished fetching rows from the result set, it must be closed with the Close() method.

Failure to close the result set may result in locks being held on rows for too long, causing concurrency problems, memory leaks, and other errors.

A TTStatus object is thrown as an exception if an error occurs.

Drop()

void Drop()

If a prepared SQL statement will not be used in the future, the statement and resources associated with it can be freed by a call to the Drop() method. The TTCmd object may be reused for another statement if Prepare() is called again.

It is more efficient to use multiple TTCmd objects to execute multiple SQL statements. Use the Drop() method only if a particular SQL statement will not be used again.

A TTStatus object is thrown as an exception if an error occurs.

Execute()

void Execute()

This method invokes a SQL statement that has been prepared for execution with the Prepare() method, after any necessary parameter values are defined using setParam() calls. A TTStatus object is thrown as an exception if an error occurs.

If the SQL statement is a SELECT statement, this method executes the query but does not return any rows from the result set. Use the FetchNext() method to fetch rows from the result set one at a time. Use the Close() method to close the result set when all appropriate rows have been fetched. For SQL statements other than SELECT, no cursor is opened, and a call to the Close() method is not necessary.

ExecuteImmediate()

int ExecuteImmediate(TTConnection* cP, const char* sqlp)

This method invokes a SQL statement that has not been previously prepared.

ExecuteImmediate() is a convenient alternative to using Prepare() and Execute() when a SQL statement is to be executed only a small number of times. Use ExecuteImmediate() for DDL statements such as CREATE TABLE and DROP TABLE, and infrequently used DML statements that do not return a result set (for example, DELETE FROM table_name).

ExecuteImmediate() is incompatible with SQL statements that return a result set. In addition, statements executed through ExecuteImmediate() cannot subsequently be queried by getRowCount() to get the number of rows affected by a DML operation. Because of this, ExecuteImmediate() calls getRowCount() automatically, and its value is the integer return value of this method.

A TTStatus object is thrown as an exception if an error occurs.

FetchNext()

int FetchNext()

After executing a prepared SQL SELECT statement using the Execute() method, use the FetchNext() method to fetch rows from the result set, one at a time.

After fetching a row of the result set, use the appropriate overloaded getColumn() method to fetch values from the current row.

If no more rows remain in the result set, FetchNext() returns 1. If a row is returned, FetchNext() returns 0.

After executing a SELECT statement using the Execute() method, the result set must be closed using the Close() method after all desired rows have been fetched. Note that after the Close() method is called, the FetchNext() method cannot be used to fetch additional rows from the result set.

A TTStatus object is thrown as an exception if an error occurs.

getColumn()

void getColumn (int cno, TYPE* valueP)
void getColumn (int cno, TYPE* valueP, SQLLEN* byteLenP)

The getColumn() method, as well as the getColumnNullable() method, fetches the values for columns of the current row of the result set. Before getColumn() or getColumnNullable() can be called, the FetchNext() method must be called to fetch the next (or first) row from the result set of a SELECT statement. SQL statements are executed using the Execute() method.

Table 3-2 below shows the supported TimesTen column types and the appropriate versions of getColumn() and getColumnNullable() to use for each parameter type. (But note that getColumnNullable() also returns a boolean value, which is not indicated in the table. Refer to the getColumnNullable() method documentation later in this section.)

Each getColumn() call retrieves the value associated with a particular column. Columns are referred to by ordinal number, with "1" indicating the first column specified in the SELECT statement. In all cases the first argument passed to the getColumn() method, cno, is the ordinal number of the column whose value is to be fetched. The second argument, valueP, is a pointer to a variable that stores the value of the specified column. The type of this argument varies depending on the type of the column being returned. For NCHAR, NVARCHAR, and binary types, as shown in the table, the method call also specifies byteLenP, a pointer to an integer value for the number of bytes written into the valueP buffer.

The TTClasses library does not support a large set of data type conversions. The appropriate version of getColumn() must be called for each output column in the prepared SQL. Calling the wrong version, such as attempting to fetch an integer column into a char* value, results in a thrown exception (TTStatus object).

When fetching integer-type data from NUMBER columns, getColumn() supports the following variants: SQLTINYINT, SQLSMALLINT, SQLINTEGER, and SQLBIGINT. They are appropriate only for NUMBER fields with the scale parameter set to zero, such as NUMBER(p) or NUMBER(p,0). The functions have the following range of precision.

Function	Precision Range
`SQLTINYINT`	0<=`p`<=2
`SQLSMALLINT`	0<=`p`<=4
`SQLINTEGER`	0<=`p`<=9
`SQLBIGINT`	0<=`p`<=18

To ensure that all values in the column fit into the variable that the application uses to retrieve information from the database, you can use SQLBIGINT for all table columns of data type NUMBER(p), where 0 <= p <= 18. For example:

getColumn(int cno, SQLBIGINT* iP)

This table shows the supported SQL data types and the appropriate input signatures of getColumn and getColumnNullable to use for each data type. The data type support also applies to getNextColumn, getNextColumnNullable, and getParam.

Table 3-2 getColumn() and getColumnNullable() Input Signatures for Supported TimesTen Table Column Types

Data Type	getColumn() and getColumnNullable() Input Signatures Supported
`TT_TINYINT`	`getColumn[Nullable](int cno, SQLTINYINT* iP)`
`TT_SMALLINT`	`getColumn[Nullable](int cno, SQLSMALLINT* iP)`
`TT_INTEGER`	`getColumn[Nullable](int cno, SQLINTEGER* iP)`
`TT_BIGINT`	`getColumn[Nullable](int cno, SQLBIGINT* iP)`
`BINARY_FLOAT`	`getColumn[Nullable](int cno, float* fP)`
`BINARY_DOUBLE`	`getColumn[Nullable](int cno, double* dP)`
`NUMBER`	`getColumn[Nullable](int cno, char** cPP) getColumn[Nullable](int cno, char* cP) getColumn[Nullable](int cno, SQLTINYINT* iP) getColumn[Nullable](int cno, SQLSMALLINT* iP) getColumn[Nullable](int cno, SQLINTEGER* iP) getColumn[Nullable](int cno, SQLBIGINT* iP)` Note: The `char*` version allows TTClasses to pass in an array of preallocated storage, and TTClasses copies the `char` output fetched from the database into this array. The integer type methods are appropriate only for columns declared with the scale parameter set to zero.
`TT_CHAR` `CHAR` `TT_VARCHAR` `VARCHAR2`	`getColumn[Nullable](int cno, char** cPP) getColumn[Nullable](int cno, char* cP)` Note: The `char*` version enables you to preallocate the output buffer.
`TT_NCHAR` `NCHAR` `TT_NVARCHAR` `NVARCHAR2`	`getColumn[Nullable](int cno, SQLWCHAR wcPP) getColumn[Nullable](int cno, SQLWCHAR wcPP, SQLLEN* byteLenP)` Note: Optionally use the `byteLenP` parameter for the number of bytes in the returned value.
`BINARY` `VARBINARY`	`getColumn[Nullable](int cno, void** binPP, SQLLEN* byteLenP) getColumn[Nullable](int cno, void* binP, SQLLEN* byteLenP)` Note: The `void*` version enables you to preallocate the output buffer.
`DATE` `TT_TIMESTAMP` `TIMESTAMP`	`getColumn[Nullable](int cno, TIMESTAMP_STRUCT* tsP)`
`TT_DATE`	`getColumn[Nullable](int cno, DATE_STRUCT* dP)`
`TT_TIME`	`getColumn[Nullable](int cno, TIME_STRUCT* tP)`

Other TimesTen table column types are not supported in this release of the TTClasses library.

getColumnLength()

SQLULEN getColumnLength(int cno)

Returns the length, in bytes, of the value in column number cno of the current row, not counting the NULL terminator. Or it returns SQL_NULL_DATA if the value is NULL. (For those familiar with ODBC, this is the value stored by ODBC in the last parameter, pcbValue, from SQLBindCol after a call to SQLFetch.) When there is a non-null value, the length returned is between 0 and the column precision, inclusive. See getColumnPrecision().

For example, assume a VARCHAR2(25) column. If the value is null, the length returned is -1. If the value is 'abcde', the length returned is 5.

This method is generally useful only when accessing columns of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY, and VARBINARY.

getColumnNullable()

bool getColumnNullable(int cno, TYPE* valueP)
bool getColumnNullable(int cno, TYPE* valueP, SQLLEN* byteLenP)

The getColumnNullable() method is similar to the getColumn() method and supports the same data types and signatures as documented in Table 3-2 above. However, in addition to the behavior of getColumn(), the getColumnNullable() method returns a boolean indicating whether the value is the SQL NULL pseudo-value. If the value is NULL, the second parameter is set to a distinctive value (for example, -9999) and the return value from the method is TRUE. If the value is not NULL, it is returned through the variable pointed to by the second parameter and the getColumnNullable() method returns FALSE.

getHandle()

SQLHSTMT getHandle()

If you must manipulate the underlying ODBC statement object, use this method to retrieve the statement handle.

getMaxRows()

SQLULEN getMaxRows()

This method returns the current limit of the number of rows returned by a SELECT statement from this TTCmd object. A return value of 0 means all rows are returned. Also see setMaxRows().

getNextColumn()

void getNextColumn(TYPE* valueP)
void getNextColumn(TYPE* valueP, SQLLEN* byteLenP)

The getNextColumn() method, as well as the getNextColumnNullable() method, fetches the value of the next column of the current row of the result set. Before getNextColumn() or getNextColumnNullable() can be called, the FetchNext() method must be called to fetch the next (or first) row from the result set of a SELECT statement. When you use getNextColumn(), the columns are fetched in order. You cannot change the fetch order.

See Table 3-2 for the supported SQL data types and the appropriate method version to use for each data type. This information can be used for getNextColumn(), except there is no cno column number parameter for getNextColumn().

getNextColumnNullable()

bool getNextColumnNullable(TYPE* valueP)
bool getNextColumnNullable(TYPE* valueP, SQLLEN* byteLenP)

The getNextColumnNullable() method is similar to the getNextColumn() method. However, in addition to the behavior of getNextColumn(), the getNextColumnNullable() method returns a boolean indicating whether the value is the SQL NULL pseudo-value. If the value is NULL, the second parameter is set to a distinctive value (for example, -9999) and the return value from the method is TRUE. If the value is not NULL, it is returned through the variable pointed to by the second parameter, and the method returns FALSE. When you use getNextColumnNullable(), the columns are fetched in order. You cannot change the fetch order.

See Table 3-2 for the supported SQL data types and the appropriate method versions to use for each data type. This information can be used for getNextColumnNullable(), except there is no cno column number parameter for getNextColumnNullable().

getParam()

bool getParam(int pno, TYPE* valueP)
bool getParam(int pno, TYPE* valueP, SQLLEN* byteLenP)

Each getParam() version is used to retrieve the value of an output or input/output parameter, specified by parameter number, after executing a prepared SQL statement. SQL statements are prepared before use with the Prepare() method and are executed with the Execute() method. The getParam() method is used to provide a variable of appropriate data type for the value for each output parameter after executing the statement.

The first argument passed to getParam() is the position of the parameter for the output value. The first parameter in a SQL statement is parameter 1. The second argument passed to getParam() is a variable for the output value. Overloaded versions of getParam() take different data types for the second argument.

The getParam() method supports the same data types documented in Table 3-2. Instead of cno for column number, however, getParam() takes pno for parameter number. For NCHAR, NVARCHAR, and binary types, as shown in that table, the method call specifies byteLenP, a pointer to an integer value for the number of bytes in the parameter value.

The getParam() return is a boolean that is TRUE if the parameter value is NULL, or FALSE otherwise.

The TTClasses library does not support a large set of data type conversions. The appropriate overloaded version of getParam() must be called for each output parameter in the prepared SQL. Calling the wrong version (attempting to use an integer parameter for a char* value, for example) may result in program failure.

See Binding Output or Input/Output Parameters for examples using getParam().

For REF CURSORs, the following signature is supported to use a TTCmd object as a statement handle for the REF CURSOR (data type SQL_REFCURSOR). See Working with REF CURSORs for information and an example.

bool getParam(int pno, TTCmd** rcCmd)

getQueryThreshold()

int getQueryThreshold()

Returns the threshold value for the TTCmd object, as described in setQueryThreshold().

If no value has been set with setQueryThreshold(), this method returns the value of the ODBC connection option TT_QUERY_THRESHOLD (if set) or of the TimesTen general connection attribute QueryThreshold.

getRowCount()

int getRowCount()

This method can be called immediately after Execute() to return the number of rows that were affected by the executed SQL operation. For example, after execution of a DELETE statement that deletes 10 rows, getRowCount() returns 10.

isColumnNull()

bool isColumnNull(int cno)

This method provides another way to determine whether the value in column number cno of the current row is NULL, returning TRUE if so, or FALSE otherwise.

Also see information about the getColumnNullable() method.

Prepare()

void Prepare(TTConnection* cP, const char* sqlp)

This method associates a SQL statement with the TTCmd object. It takes two parameters:

A pointer to a TTConnection object

The connection object should be connected to the database by a call to TTConnection::Connect().
A const char* parameter for the SQL statement being prepared

Note:

To avoid unwanted round trips between client and server in client/server connections, the Prepare() method performs what is referred to as a "deferred prepare", where the request is not sent to the server until required. See TimesTen Deferred Prepare in Oracle TimesTen In-Memory Database C Developer's Guide.
By default (when connection attribute PrivateCommands=0), TimesTen shares prepared SQL statements between connections, so subsequent prepares of the same SQL statement on different connections execute very quickly.

printColumn()

void printColumn(int cno, STDOSTREAM& os, const char*  nullString) const

This method prints the value in column number cno of the current row to the output stream os. Use this for debugging or for demo programs. Use nullString to specify what should be printed if the column value is NULL (for example, "NULL" or "?").

registerParam()

void registerParam(int pno, TTCMD_PARAM_INPUTOUTPUT_TYPE inputOutputType,
                       short sqltype)
void registerParam(int pno, TTCMD_PARAM_INPUTOUTPUT_TYPE inputOutputType, 
                   short sqltype, int precision)
void registerParam(int pno, TTCMD_PARAM_INPUTOUTPUT_TYPE inputOutputType, 
                   short sqltype, int precision, short scale)

Use this method to register a parameter for binding. This is required for output and input/output parameters and can also be used as appropriate to specify SQL type, precision (maximum number of digits that are used by the data type, where applicable), and scale (maximum number of digits to the right of the decimal point, where applicable). See Registering Parameters.

setMaxRows()

void setMaxRows(const int nMaxRows)

This method sets a limit on the number of rows returned by a SELECT statement. If the number of rows in the result set exceeds the set limit, the TTCmd::FetchNext() method returns 1 when it has fetched the last row in the requested set size. Also see getMaxRows().

The default is to return all rows. To reset a limit to again return all rows, call setMaxRows() with nMaxRows set to 0. The limit is only meaningful for SELECT statements.

setParam()

void setParam(int pno, TYPE value)
void setParam(int pno, TYPE* valueP)
void setParam(int pno, TYPE* valueP, int byteLen)

All overloaded setParam() versions are described in this section.

Each setParam() version is used to set the value of a parameter, specified by parameter number, before executing a prepared SQL statement. SQL statements are prepared before use with the Prepare() method and are executed with the Execute() method. If the SQL statement contains any parameter markers (the "?" character used where a literal constant would be valid), values must be assigned to these parameters before the SQL statement can be executed. The setParam() method is used to define a value for each parameter before executing the statement. See Dynamic Parameters in Oracle TimesTen In-Memory Database SQL Reference.

Table 3-3 below shows the supported SQL data types and the appropriate versions of setParam() to use for each type. SQL data types not mentioned are not supported in this version of TTClasses. For NCHAR, NVARCHAR, and binary types, as shown in the table, the method call specifies byteLen, an integer value for the number of bytes in the parameter value.

The first argument passed to setParam() is the position of the parameter to be set. The first parameter in a SQL statement is parameter 1. The second argument passed to setParam() is the value of the parameter. Overloaded versions of setParam() take different data types for the second argument.

The TTClasses library does not support a large set of data type conversions. The appropriate overloaded version of setParam() must be called for each parameter in the prepared SQL. Calling the wrong version (attempting to set an integer parameter to a char* value, for example) may result in program failure.

Values passed to setParam() are copied into internal buffers maintained by the TTCmd object. These buffers are statically allocated and bound by the Prepare() method. The parameter value is the value passed into setParam() at the time of the setParam() call, not the value at the time of a subsequent Execute() method call.

See Binding Input Parameters and Binding Output or Input/Output Parameters for examples using setParam(). See Binding Duplicate Parameters regarding duplicate parameters.

Note:

To set the length of the value for a bound parameter, see setParamLength().
To set a value of NULL for a bound parameter, see setParamNull().

Table 3-3 setParam() Signatures for Supported TimesTen Table Column Types

Data Type	setParam() Variants Supported
`TT_TINYINT`	`setParam(int pno, SQLTINYINT value)`
`TT_SMALLINT`	`setParam(int pno, SQLSMALLINT value)`
`TT_INTEGER`	`setParam(int pno, SQLINTEGER value)`
`TT_BIGINT`	`setParam(int pno, SQLBIGINT value)`
`BINARY_FLOAT` `REAL`	`setParam(int pno, float value)`
`BINARY_DOUBLE` `DOUBLE`	`setParam(int pno, double value)`
`NUMBER`	`setParam(int pno, char* valueP) setParam(int pno, const char* valueP) setParam(int pno, SQLCHAR* valueP) setParam(int pno, SQLTINYINT value) setParam(int pno, SQLSMALLINT value) setParam(int pno, SQLINTEGER value) setParam(int pno, SQLBIGINT value)` Note: The integer versions are appropriate only for columns declared with the scale parameter set to zero, such as `NUMBER(8)` or `NUMBER(8,0)`.
`TT_CHAR` `CHAR` `TT_VARCHAR` `VARCHAR2`	`setParam(int pno, char* valueP) setParam(int pno, const char* valueP) setParam(int pno, SQLCHAR* valueP)`
`TT_NCHAR` `NCHAR` `TT_NVARCHAR` `NVARCHAR2`	`setParam(int pno, SQLWCHAR* valueP, int byteLen)`
`BINARY` `VARBINARY`	`setParam(int pno, const void* valueP, int byteLen)`
`DATE` `TT_TIMESTAMP` `TIMESTAMP`	`setParam(int pno, TIMESTAMP_STRUCT& valueP)`
`TT_DATE`	`setParam(int pno, DATE_STRUCT& valueP)`
`TT_TIME`	`setParam(int pno, TIME_STRUCT& valueP)`

setParamLength()

(Version for non-batch operations)

void setParamLength(int pno, int byteLen)

Sets the length, in bytes, of the bound value for an input parameter specified by parameter number, before execution of the prepared statement.

Note:

There is also a batch version of this method. See setParamLength().

setParamNull()

(Version for non-batch operations)

void setParamNull(int pno)

Sets a value of SQL NULL for a bound input parameter specified by parameter number.

Note:

There is also a batch version of this method. See setParamNull().

setQueryThreshold()

void setQueryThreshold(const int nSecs)

Use this method to specify a threshold time limit, in seconds, for the TTCmd object. (This applies to any SQL statement, not just queries.) If the execution time of a statement exceeds the threshold, a warning is written to the support log. Execution continues and is not affected by the threshold. Also see getQueryThreshold().

The setQueryThreshold() method has the same effect as using SQLSetStmtOption to set TT_QUERY_THRESHOLD or setting the TimesTen general connection attribute QueryThreshold.

See Setting a Timeout or Threshold for Executing SQL Statements.

setQueryTimeout()

void setQueryTimeout(const int nSecs)

Use this method to specify how long, in seconds, any SQL statement (not just a query) executes before timing out. By default there is no timeout.

This has the same effect as using SQLSetStmtOption to set SQL_QUERY_TIMEOUT or setting the TimesTen general connection attribute SQLQueryTimeout (or SQLQueryTimeoutMsec, to use milliseconds).

See Setting a Timeout or Threshold for Executing SQL Statements.

Public Methods for Obtaining TTCmd Object Properties

There are several useful methods for asking questions about properties of the bound input parameters and output columns of a prepared TTCmd object. These methods generally provide meaningful results only when a statement has previously been prepared.

This section summarizes then describes the methods for obtaining TTCmd object properties.

Public Methods Summary for Obtaining TTCmd Object Properties

Method	Description
`getColumnName()`	Returns the name of the specified column.
`getColumnNullability()`	Indicates whether data in the specified column can have the value `NULL`.
`getColumnPrecision()`	Returns the precision of the specified column.
`getColumnScale()`	Returns the scale of the specified column.
`getColumnType()`	Returns the ODBC data type of the specified column.
`getNColumns()`	Returns the number of output columns.
`getNParameters()`	Returns the number of input parameters.
`getParamNullability()`	Indicates whether the value of the specified parameter can be `NULL`.
`getParamPrecision()`	Returns the precision of the specified parameter in a prepared statement.
`getParamScale()`	Returns the scale of the specified parameter in a prepared statement.
`getParamType()`	Returns the ODBC data type of the specified parameter.
`isBeingExecuted()`	Indicates whether the statement represented by the `TTCmd` object is being executed.

getColumnName()

const char* getColumnName(int cno)

Returns the name of column number cno.

getColumnNullability()

int getColumnNullability(int cno)

Indicates whether column number cno can NULL data. It returns SQL_NO_NULLS, SQL_NULLABLE, or SQLNULLABLE_UNKNOWN.

getColumnPrecision()

SQLULEN getColumnPrecision(int cno)

Returns the precision of data in column number cno, referring to the size of the column in the database. For example, for a VARCHAR2(25) column, the precision returned would be 25.

This value is generally interesting only when generating output from table columns of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY, and VARBINARY.

getColumnScale()

int getColumnScale(int cno)

Returns the scale of data in column number cno, referring to the maximum number of digits to the right of the decimal point.

getColumnType()

int getColumnType(int cno)

Returns the data type of column number cno. The value returned is the ODBC type of the parameter (for example, SQL_INTEGER, SQL_REAL, SQL_BINARY, SQL_CHAR) as found in sql.h. Additional TimesTen ODBC types (SQL_WCHAR, SQL_WVARCHAR) can be found in the TimesTen header file timesten.h.

getNColumns()

int getNColumns()

Returns the number of output columns.

getNParameters()

int getNParameters()

Returns the number of input parameters for the SQL statement.

getParamNullability()

int getParamNullability(int pno)

Indicates whether parameter number pno can have the value NULL. It returns SQL_NO_NULLS, SQL_NULLABLE, or SQLNULLABLE_UNKNOWN.

Note:

In earlier releases this method returned bool instead of int.

getParamPrecision()

SQLULEN getParamPrecision(int pno)

Returns the precision of parameter number pno, referring to the maximum number of digits that are used by the data type. Also see information for getColumnPrecision(), above.

getParamScale()

int getParamScale(int pno)

Returns the scale of parameter number pno, referring to the maximum number of digits to the right of the decimal point.

getParamType()

int getParamType(int pno)

Returns the data type of parameter number pno. The value returned is the ODBC type (for example, SQL_INTEGER, SQL_REAL, SQL_BINARY, SQL_CHAR) as found in sql.h. Additional TimesTen types (SQL_WCHAR, SQL_WVARCHAR) can be found in the TimesTen header file timesten.h.

isBeingExecuted()

bool isBeingExecuted()

Indicates whether the statement represented by the TTCmd object is being executed.

Public Methods for Batch Operations

TimesTen supports the ODBC function SQLBindParams for batch insert, update and delete operations. TTClasses provides an interface to the ODBC function SQLBindParams.

Performing batch operations with TTClasses is similar to performing non-batch operations. SQL statements are first compiled using PrepareBatch(). Then each parameter in that statement is bound to an array of values using BindParameter(). Finally, the statement is executed using ExecuteBatch().

See the TTClasses bulktest sample program in the TimesTen Classic Quick Start for an example of using a batch operation. Refer to About TimesTen Quick Start and Sample Applications.

This section summarizes then describes the TTCmd methods that expose the batch INSERT, UPDATE, and DELETE functionality to TTClasses users.

Public Methods Summary for Batch Operations

Method	Description
`batchSize()`	Returns the number of statements in the batch.
`BindParameter()`	Binds an array of values for one parameter of a statement prepared using `PrepareBatch()`.
`ExecuteBatch()`	Invokes a SQL statement that has been prepared for execution by `PrepareBatch()`. It returns the number of rows in the batch that were updated.
`PrepareBatch()`	Prepares batch `INSERT`, `UPDATE`, and `DELETE` statements.
`setParamLength()`	Sets the length, in bytes, of the value of the specified bound parameter before execution of the prepared statement.
`setParamNull()`	Sets the specified bound parameter to `NULL` before execution of the prepared statement.

batchSize()

u_short batchSize()

Returns the number of statements in the batch.

BindParameter()

void BindParameter(int pno, unsigned short batSz, TYPE* valueP)
void BindParameter(int pno, unsigned short batSz, TYPE* valueP, size_t maxByteLen)
void BindParameter(int pno, unsigned short batSz, TYPE* valueP, 
                   SQLLEN* userByteLenP, size_t maxByteLen)

The overloaded BindParameter() method is used to bind an array of values for a specified parameter in a SQL statement compiled using PrepareBatch(). This allows iterating through a batch of repeated executions of the statement with different values. The pno parameter indicates the position in the statement of the parameter to be bound, starting from the left, where the first parameter is 1, the next is 2, and so on.

Table 3-4 below shows the supported SQL data types and the appropriate versions of BindParameter() to use for each parameter type.

Also see Binding Duplicate Parameters.

The batSz (batch size) value of this call must match the batSz value specified in PrepareBatch(), and the bound arrays should contain at least the batSz number of values. You must determine the correct data type for each parameter. If an invalid parameter number is specified, the specified batch size is a mismatch, or the data buffer is null, then a TTStatus object is thrown as an exception and a runtime error is written to the TTClasses global logging facility at the TTLog::TTLOG_ERR logging level.

Before each invocation of ExecuteBatch(), the application should fill the bound arrays with valid parameter values. Note that you can use the setParamNull() method to set null values, as described in setParamNull(). (Be aware that for batch mode, you must use the two-parameter version of setParamNull() that specifies rowno. The one-parameter version is for non-batch use only.)

For the SQL types TT_CHAR, CHAR, TT_VARCHAR, and VARCHAR2, an additional maximum length parameter is required in the BindParameter() call:

maxByteLen of type size_t is for the maximum length, in bytes, of any value for this parameter position.

For the SQL types TT_NCHAR, NCHAR, TT_NVARCHAR, NVARCHAR2, BINARY, and VARBINARY, two additional parameters are required in the BindParameter() call, an array of parameter lengths and a maximum length:

userByteLenP is an array of SQLLEN parameter lengths, in bytes, to specify the length of each value in the batch for this parameter position in the SQL statement. This array must be at least batSz in length and filled with valid length values before ExecuteBatch() is called. (You can store SQL_NULL_DATA in the array of parameter lengths for a null value, which is equivalent to using the setParamNull() batch method.)
maxByteLen is as described above. This indicates the maximum length value that can be specified in any element of the userByteLenP array.

For data types where userByteLenP is not available (or as an alternative where it is available), you can optionally use the setParamLength() batch method to set data lengths, as described in setParamLength(), and use the setParamNull() batch method to set null values, as described in setParamNull().

See the example in the next section, ExecuteBatch(), for examples of BindParameter() usage.

Table 3-4 BindParameter() Signatures for Supported TimesTen Table Column Types

SQL Data Type	BindParameter() Variants Supported
`TT_TINYINT`	`BindParameter(int pno, short batSz, SQLTINYINT* user_tiP)`
`TT_SMALLINT`	`BindParameter(int pno, short batSz, SQLSMALLINT* user_siP)`
`TT_INTEGER`	`BindParameter(int pno, short batSz, SQLINTEGER* user_iP)`
`TT_BIGINT`	`BindParameter(int pno, short batSz, SQLBIGINT* user_biP)`
`BINARY_FLOAT`	`BindParameter(int pno, short batSz, float* user_fP)`
`BINARY_DOUBLE`	`BindParameter(int pno, short batSz, double* user_dP)`
`NUMBER`	`BindParameter(int pno, short batSz, char** user_cPP, size_t maxByteLen)`
`TT_CHAR` `CHAR` `TT_VARCHAR` `VARCHAR2`	`BindParameter(int pno, short batSz, char** user_cPP, size_t maxByteLen)`
`TT_NCHAR` `NCHAR` `TT_NVARCHAR` `NVARCHAR2`	`BindParameter(int pno, short batSz, SQLWCHAR** user_wcPP, SQLLEN* userByteLenP, size_t maxByteLen)`
`BINARY` `VARBINARY`	`BindParameter(int pno, short batSz, const void** user_binPP, SQLLEN* userByteLenP, size_t maxByteLen)`
`DATE` `TT_TIMESTAMP` `TIMESTAMP`	`BindParameter(int pno, short batSz, TIMESTAMP_STRUCT* user_tssP)`
`TT_DATE`	`BindParameter(int pno, int batSz, DATE_STRUCT* user_dsP)`
`TT_TIME`	`BindParameter(int pno, int batSz, TIME_STRUCT* user_tsP)`

ExecuteBatch()

SQLULEN ExecuteBatch(unsigned short numRows)

After preparing a SQL statement with PrepareBatch() and calling BindParameter() for each parameter in the SQL statement, use ExecuteBatch() to execute the statement numRows times. The value of numRows must be no more than the batSz (batch size) value specified in the PrepareBatch() and BindParameter() calls, but can be less than batSz as required by application logic.

This method returns the number of rows that were updated, with possible values in the range 0 to batSz, inclusive. (For those familiar with ODBC, this is the third parameter, *pirow, of an ODBC SQLParamOptions call. Refer to ODBC API reference documentation for information about SQLParamOptions.)

Before calling ExecuteBatch(), the application should fill the arrays of parameters previously bound by BindParameter() with valid values.

A TTStatus object is thrown as an exception if an error occurs (often due to violation of a uniqueness constraint). In this event, the return value is not valid and the batch is incomplete and should generally be rolled back.

The following example shows how to use the ExecuteBatch() method. The bulktest TimesTen Classic Quick Start demo also shows usage of this method. (See About TimesTen Quick Start and Sample Applications.)

First, create a table with two columns:

CREATE TABLE batch_table (a TT_INTEGER, b VARCHAR2(100));

Following is the sample code. Populate the rows of the table in batches of 50.

#define BATCH_SIZE 50
#define VARCHAR_SIZE 100
 
int int_array[BATCH_SIZE];
char char_array[BATCH_SIZE][VARCHAR_SIZE];
 
// Prepare the statement
 
TTCmd insert;
TTConnection connection;
 
// (assume a connection has been established)
 
try {
 
  insert.PrepareBatch (&connection,
                       (const char*)"insert into batch_table values (?,?)",
                       BATCH_SIZE);
 
  // Commit the prepared statement
  connection.Commit();
 
  // Bind the arrays of parameters
  insert.BindParameter(1, BATCH_SIZE, int_array);
  insert.BindParameter(2, BATCH_SIZE, (char **)char_array, VARCHAR_SIZE);
 
  // Execute 5 batches, inserting a total of 5 * BATCH_SIZE rows into
  // the database
  for (int iter = 0; iter < 5; iter++)
  {
    // Populate the value arrays with values.
    // (A more meaningful way of putting data into
    // the database is to read values from a file, for example,
    // rather than generating them arbitrarily.)
 
    for (int i = 0; i < BATCH_SIZE; i++)
    {
      int_array[i] = i * iter + i;
      sprintf(char_array[i], "varchar value # %d", i*iter+ i);
    }
 
    // Execute the batch insert statement,
    // which inserts the entire contents of the
    // integer and char arrays in one operation.
    SQLULEN num_ins = insert.ExecuteBatch(BATCH_SIZE);
 
    cerr << "Inserted " << num_ins << " rows." << endl;
 
    connection.Commit();
 
  } // for iter
 
} catch (TTError er1) {
  cerr << er1 << endl;
}

The number of rows updated (num_ins in the example) can be less than BATCH_SIZE if, for example, there is a violation of a uniqueness constraint on a column. You can use code similar to that in the next example check for this situation and roll back the transaction as necessary.

for (int iter = 0; iter < 5; iter++)
{

  // Populate the value arrays with values.
  // (A better way of putting meaningful data into
  // the database is to read values from a file,
  // rather than generating them arbitrarily.)

  for (int i = 0; i < BATCH_SIZE; i++)
  {
    int_array[i] = i * iter + i;
    sprintf(char_array[i], "varchar value # %d", i*iter+i);
  }

  // now we execute the batch insert statement,
  // which does the work of inserting the entire
  // contents of the integer and char arrays in
  // one operation

  SQLULEN num_ins = insert.ExecuteBatch(BATCH_SIZE);

  cerr << "Inserted " << num_ins << " rows (expected "
       << BATCH_SIZE << " rows)." << endl;

  if (num_ins == BATCH_SIZE) {
    cerr << "Committing batch" << endl;
    connection.Commit();
  }
  else {
    cerr << "Some rows were not inserted as expected, rolling back "
         << "transaction." << endl;
    connection.Rollback();
    break; // jump out of batch insert loop
  }

} // for loop

PrepareBatch()

void PrepareBatch(TTConnection* cP, const char* sqlp, unsigned short batSz)

PrepareBatch() is comparable to the Prepare() method but for batch INSERT, UPDATE, or DELETE statements. The cP and sqlp parameters are used as with Prepare(). See Prepare().

The batSz (batch size) parameter specifies the maximum number of insert, update, or delete operations that are performed using subsequent calls to ExecuteBatch().

A TTStatus object is thrown as an exception if an error occurs.

Note:

To avoid unwanted round trips between client and server in client/server connections, the PrepareBatch() method performs what is referred to as a "deferred prepare", where the request is not sent to the server until required. See TimesTen Deferred Prepare in Oracle TimesTen In-Memory Database C Developer's Guide.

setParamLength()

(Version for batch operations)

void setParamLength(int pno, unsigned short rowno, int byteLen)

This method sets the length of a bound parameter value before a call to ExecuteBatch(). The pno argument specifies the parameter number in the SQL statement (where the first parameter is number 1). The rowno argument specifies the row number in the array of parameters being bound (where the first row is row number 1). The byteLen parameter specifies the desired length, in bytes, not counting the NULL terminator. Alternatively, byteLen can be set to SQL_NTS for a null-terminated string. (It can also be set to SQL_NULL_DATA, which is equivalent to using the setParamNull() batch method, described next.)

Note:

For binary and NCHAR types, as shown in Table 3-4, it may be easier to use the BindParameter() userByteLenP array to set parameter lengths. Be aware that row numbering in the array of parameters being bound starts with 0 in the userByteLenP array but with 1 when you use setParamLength().
There is also a non-batch version of this method. See setParamLength(). (It is important to use only the two-parameter version for non-batch operations, and only the three-parameter version that specifies rowno for batch operations.)

setParamNull()

(Version for batch operations)

void setParamNull(int pno, unsigned short rowno)

This method sets a bound parameter value to NULL before a call to ExecuteBatch(). The pno argument specifies the parameter number in the SQL statement (where the first parameter is number 1). The rowno argument specifies the row number in the array of parameters being bound (where the first row is row number 1).

Note:

For binary and NCHAR types, as shown in Table 3-4, there is a BindParameter() userByteLenP array. For these types, you can have a null value by specifying SQL_NULL_DATA in this array, which is equivalent to using setParamNull(). Be aware that row numbering in the bound array of parameters userByteLenP starts with 0, but numbering starts with 1 when you use setParamNull().
There is also a non-batch version of this method. See setParamNull(). (It is important to use only the one-parameter version for non-batch operations, and only the two-parameter version that specifies rowno for batch operations.)