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.
Public Members
Member | Description |
---|---|
|
This is used to specify whether a parameter is input, output, or input/output when registering the parameter. Supported values are |
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 |
---|---|
Closes the result set when the application has finished fetching rows. |
|
Frees a prepared SQL statement and all resources associated with it. |
|
Invokes a SQL statement that has been prepared for execution. |
|
Invokes a SQL statement that has not been previously prepared. |
|
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. |
|
Retrieves the value in the specified column of the current row of the result set. |
|
Returns the length of the specified column, in bytes. |
|
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 |
|
Retrieves the underlying ODBC statement handle. |
|
Returns the current limit on the number of rows returned by a |
|
Retrieves the value in the next column of the current row of the result set. |
|
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 |
|
Each call gets the output value of a specified output or input/output parameter after executing a prepared SQL statement. |
|
Retrieves the query threshold value. |
|
Returns the number of rows that were affected by the recently executed SQL operation. |
|
Indicates whether the value in the specified column of the current row is |
|
Associates a SQL statement with the |
|
Prints the value in the specified column of the current row to an output stream. |
|
Registers a parameter for binding. This is required for output or input/output parameters. |
|
Sets a limit on the number of rows returned by a |
|
Each call sets the value of a specified parameter before executing a prepared SQL statement. |
|
Sets the length, in bytes, of the specified input parameter. |
|
Sets the value of a parameter to |
|
Sets a threshold time limit for execution of each SQL statement. If it is exceeded, a warning is written to the support log. |
|
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 |
---|---|
|
0<= |
|
0<= |
|
0<= |
|
0<= |
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: The |
|
Note: The |
|
Note: Optionally use the |
|
Note: The |
|
|
|
|
|
|
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
objectThe 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, seesetParamNull()
.
Table 3-3 setParam() Signatures for Supported TimesTen Table Column Types
Data Type | setParam() Variants Supported |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: The integer versions are appropriate only for columns declared with the scale parameter set to zero, such as |
|
|
|
|
|
|
|
|
|
|
|
|
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 |
---|---|
Returns the name of the specified column. |
|
Indicates whether data in the specified column can have the value |
|
Returns the precision of the specified column. |
|
Returns the scale of the specified column. |
|
Returns the ODBC data type of the specified column. |
|
Returns the number of output columns. |
|
Returns the number of input parameters. |
|
Indicates whether the value of the specified parameter can be |
|
Returns the precision of the specified parameter in a prepared statement. |
|
Returns the scale of the specified parameter in a prepared statement. |
|
Returns the ODBC data type of the specified parameter. |
|
Indicates whether the statement represented by the |
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 |
---|---|
Returns the number of statements in the batch. |
|
Binds an array of values for one parameter of a statement prepared using |
|
Invokes a SQL statement that has been prepared for execution by |
|
Prepares batch |
|
Sets the length, in bytes, of the value of the specified bound parameter before execution of the prepared statement. |
|
Sets the specified bound parameter to |
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 typesize_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 ofSQLLEN
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 leastbatSz
in length and filled with valid length values beforeExecuteBatch()
is called. (You can storeSQL_NULL_DATA
in the array of parameter lengths for a null value, which is equivalent to using thesetParamNull()
batch method.) -
maxByteLen
is as described above. This indicates the maximum length value that can be specified in any element of theuserByteLenP
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 theBindParameter()
userByteLenP
array to set parameter lengths. Be aware that row numbering in the array of parameters being bound starts with 0 in theuserByteLenP
array but with 1 when you usesetParamLength()
. -
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 aBindParameter()
userByteLenP
array. For these types, you can have a null value by specifyingSQL_NULL_DATA
in this array, which is equivalent to usingsetParamNull()
. Be aware that row numbering in the bound array of parametersuserByteLenP
starts with 0, but numbering starts with 1 when you usesetParamNull()
. -
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.)