Using TimesTen Features in your Application
This section covers the following topics for working with data.
Binding Parameters
This section discusses parameter binding for SQL statements.
Note:
The term "bind parameter" as used in TimesTen developer guides (in keeping with ODBC terminology) is equivalent to the term "bind variable" as used in TimesTen PL/SQL documents (in keeping with Oracle Database PL/SQL terminology).
About Parameter Binding
The TTCmd
class supplies the methods setParam()
and BindParameter()
(for batch operations) to bind parameters. It also supplies the method registerParam()
to support output and input/output parameters or to override default bind types.
The TimesTen binding mechanism (early binding) differs from that of Oracle Database (late binding). TimesTen requires the data types before preparing queries. As a result, there will be an error if the data type of each bind parameter is not specified or cannot be inferred from the SQL statement. This would apply, for example, to the following statement:
SELECT 'x' FROM DUAL WHERE ? = ?;
You could address the issue as follows, for example:
SELECT 'x' from DUAL WHERE CAST(? as VARCHAR2(10)) =
CAST(? as VARCHAR2(10));
Binding Input Parameters
For non-batch operations, use the TTCmd::
setParam()
method to bind input parameters for SQL statements, specifying the parameter position and the value to be bound. For batch operations, use the TTCmd::BindParameter()
method.
See setParam() and ExecuteBatch() for an example of batch operations.
For non-batch operations, the example below shows snippets from a class SampleConnection
, where parameters are bound to insert a row into a table. (This example is from the basics.cpp
sample application provided with the TimesTen Classic Quick Start. See About TimesTen Quick Start and Sample Applications.)
Assume a table basics
, defined as follows:
create table basics (name char(10) not null primary key, i tt_integer);
Implementation of the Connect()
method is omitted here, but see the example of a Connect()
implementation in Using TTCmd, TTConnection, and TTConnectionPool.
class SampleConnection : public TTConnection
{
using TTConnection::Connect;
private:
TTCmd insertData;
...
protected:
public:
SampleConnection();
~SampleConnection();
virtual void Connect(const char* connStr,
DRIVER_COMPLETION_ENUM driverCompletion);
void insert(char* nameP);
...
...
// Assume a Connect() method implemented with the following:
// insertData.Prepare(this, "insert into basics values(:name, :value)");
...
}
//----------------------------------------------------------------------
void
SampleConnection::insert(char* nameP)
{
static long i = 0;
insertData.setParam(1, nameP);
insertData.setParam(2, i++);
insertData.Execute();
}
//----------------------------------------------------------------------
...
int
main(int argc, char** argv)
{
...
char name[10];
SampleConnection conn;
...
// Assume conn is an open connection.
sprintf(name, "Robert");
try {
conn.insert(name);
}
catch (TTStatus st) {
cerr << "Error inserting row " << name << ":" << st << endl;
conn.Rollback();
}
}
Registering Parameters
The TTCmd
class provides the registerParam()
method, which enables you to specify the SQL type, precision, and scale of a parameter (as applicable) and whether the parameter is input, output, or input/output.
A registerParam()
call is required for an output or input/output parameter, which could be a REF CURSOR (output only) or a parameter from a PL/SQL RETURNING INTO
clause (output only), procedure, or function.
A registerParam()
call can be either before or after the related setParam()
or BindParameter()
call and takes precedence regarding SQL type, precision, and scale (as applicable).
The method signature is as follows:
inline void
TTCmd::registerParam(int pno,
int inputOutputType,
short sqltype,
int precision = 0,
short scale = 0)
-
pno
is the parameter position in the statement. -
inputOutputType
can beTTCmd::PARAM_IN
,TTCmd::PARAM_OUT
, orTTCmd::PARAM_INOUT
. -
sqltype
is the SQL type of the data (for example,SQL_INTEGER
). -
precision
andscale
(both optional) are used the same way as in an ODBCSQLBindParameter
call. For primitive types (such asint
),precision
andscale
settings are ignored.
Note:
See Binding Output or Input/Output Parameters, for an example. Also see registerParam() for additional reference information.
Parameter C Type to SQL Type Mappings
For an input parameter, TTClasses by default derives the SQL type from the bound C type for the setParam()
or BindParameter()
call according to the mappings.
It is not typical to need a registerParam()
call for an input parameter, but you can call it if you must use a particular SQL type or precision or scale.
Table 2-1 TTClasses C Type to SQL Type Mappings
C Type | SQL Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note:
Not all C types shown in the preceding table are supported if you are using a driver manager. Refer to Considerations when Using an ODBC Driver Manager (Windows).
Binding Output or Input/Output Parameters
TTClasses supports output and input/output parameters such as REF CURSORs (output only), parameters from a PL/SQL procedure or function that has OUT
or IN OUT
parameters, or a parameter from a RETURNING INTO
clause (output only).
You must use the TTCmd::
registerParam()
method, described in the preceding section, to inform TTClasses if a parameter in a SQL statement is output or input/output. For the intputOutputType
setting in the method call, use TTCmd::PARAM_OUT
or TTCmd::PARAM_INOUT
as appropriate.
For non-batch operations, after the SQL statement has been executed, use the appropriate TTCmd::getParam()
method to retrieve the output value, specifying the parameter position and the variable into which the value is placed. There is a signature for each data type.
For batch operations, TTCmd::BindParameter()
is used for output or input/output parameters as well as for input parameters. It is called before the statement is executed. After statement execution, the data for an output value is in the buffer specified in the BindParameter()
call. BindParameter()
has a signature for each data type. For an input/output parameter in batch operations, BindParameter()
is called only once, before statement execution. Before execution the specified buffer contains the input, and after statement execution it contains the output.
The following examples provide code fragments showing the use of output and input/output parameters.
The first example uses input and output parameters. The setParam()
call binds the value of the input parameter :a
. The getParam()
call retrieves the value of the output parameter :b
. The output parameter is also registered as required.
...
// t1 has a single TT_INTEGER column
cmd.Prepare(&conn, "insert into t1 values (:a) returning c1 into :b");
cmd.setParam(1, 99);
cmd.registerParam(2, TTCmd::PARAM_OUT, SQL_INTEGER);
cmd.Execute();
SQLINTEGER outval;
if (cmd.getParam(2, &outval))
cerr << "The output value is null." << endl;
else
cerr << "The output value is " << outval << endl;
...
The next example uses input and output parameters in a batch operation. The first BindParameter()
call provides the input data for the first parameter :a
. The second BindParameter()
call provides a buffer for output data for the second parameter :b
.
...
#define BATCH_SIZE 5
int input_int_array[BATCH_SIZE] = { 91, 92, 93, 94, 95 };
int output_int_array[BATCH_SIZE] = { -1, -1, -1, -1, -1 };
SQLULEN numrows;
cmd.PrepareBatch(&conn, "insert into t1 values (:a) returning c1 into :b",
BATCH_SIZE);
cmd.BindParameter(1, BATCH_SIZE, input_int_array);
cmd.BindParameter(2, BATCH_SIZE, output_int_array);
cmd.registerParam(2, TTCmd::PARAM_OUT, SQL_INTEGER);
numrows = cmd.ExecuteBatch(BATCH_SIZE);
...
The following example uses an input/output parameter. It is registered as required. The setParam()
call binds its input value and the getParam()
call retrieves its output value.
...
cmd.Prepare(&conn, "begin :x := :x + 1; end;");
cmd.registerParam(1, TTCmd::PARAM_INOUT, SQL_INTEGER);
cmd.setParam(1, 99);
cmd.Execute();
SQLINTEGER outval;
if (cmd.getParam(1, &outval))
cerr << "The output value is null." << endl;
else
cerr << "The output value is " << outval << endl;
...
This final example uses output and input/output parameters. Assume a PL/SQL procedure as follows:
create or replace procedure my_proc (
a in number,
b in number,
c out number,
d in out number ) as
begin
c := a + b;
d := a + b - d;
end my_proc;
The input parameters for the procedure are taken as constants in this example rather than as bound parameters, so only the OUT
parameter and IN OUT
parameter are bound. Both are registered as required. The setParam()
call provides the input value for the IN OUT
parameter :var1
. The first getParam()
call retrieves the value for the OUT
parameter :sum
. The second getParam()
call retrieves the output value for the IN OUT
parameter :var1
.
...
cmd.Prepare(&conn, "begin my_proc (10, 5, :sum, :var1); end;");
cmd.registerParam (1, TTCmd::PARAM_OUT, SQL_DECIMAL, 38);
cmd.registerParam (2, TTCmd::PARAM_INOUT, SQL_DECIMAL, 38);
cmd.setParam(2, "99");
cmd.Execute();
SQLINTEGER outval1, outval2;
if (cmd.getParam(1, &outval1))
cerr << "The first output value is null." << endl;
else
cerr << "The first output value is " << outval << endl;
if (cmd.getParam(2, &outval2))
cerr << "The second output value is null." << endl;
else
cerr << "The second output value is " << outval << endl;
...
Binding Duplicate Parameters
In TimesTen, multiple occurrences of the same parameter name in a SQL statement are considered to be distinct parameters. (This is consistent with Oracle Database support for binding duplicate parameters.)
Note:
-
"TimesTen mode" for binding duplicate parameters, and the
DuplicateBindMode
connection attribute, are deprecated. -
Refer to Binding of Duplicate Parameters in SQL Statements in Oracle TimesTen In-Memory Database C Developer's Guide.
Consider this query:
SELECT * FROM employees
WHERE employee_id < :a AND manager_id > :a AND salary < :b;
When parameter position numbers are assigned, a number is given to each parameter occurrence without regard to name duplication. The application must, at a minimum, bind a value for the first occurrence of each parameter name. For any subsequent occurrence of a given parameter name, the application can bind a different value for the occurrence or it can leave the parameter occurrence unbound. In the latter case, the subsequent occurrence takes the same value as the first occurrence. In either case, each occurrence still has a distinct parameter position number.
This example uses a different value for the second occurrence of a
in the SQL statement above:
mycmd.setParam(1, ...); // first occurrence of :a
mycmd.setParam(2, ...); // second occurrence of :a
mycmd.setParam(3, ...); // occurrence of :b
To use the same value for both occurrences of a
:
mycmd.setParam(1, ...); // both occurrences of :a
mycmd.setParam(3, ...); // occurrence of :b
Parameter b
is considered to be in position 3 regardless, and the number of parameters is considered to be three.
Working with REF CURSORs
REF CURSOR is a PL/SQL concept, a handle to a cursor over a SQL result set that can be passed between PL/SQL and an application.
In TimesTen, the cursor can be opened in PL/SQL, then the REF CURSOR can be passed to the application for processing. This usage is an OUT
REF CURSOR, an OUT
parameter with respect to PL/SQL. As with any output parameter, it must be registered using the TTCmd::registerParam()
method.
See Registering Parameters and Binding Output or Input/Output Parameters.
In the TimesTen implementation, the REF CURSOR is attached to a separate statement handle. The application prepares a SQL statement that has a REF CURSOR parameter on one statement handle, then, before executing the statement, binds a second statement handle as the value of the REF CURSOR. After the statement is executed, the application can describe, bind, and fetch the results using the same APIs as for any result set.
In TTClasses, because a TTCmd
object encapsulates a single SQL statement, two TTCmd
objects are used to support this REF CURSOR model.
See PL/SQL REF CURSORs in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for additional information about REF CURSORs.
Note:
For passing REF CURSORs between PL/SQL and an application, TimesTen supports only OUT REF CURSORs, from PL/SQL to the application.
The example below demonstrates the following steps for using a REF CURSOR in TTClasses.
-
Declare a
TTCmd
object for the PL/SQL statement that returns a REF CURSOR (cmdPLSQL
in the example). -
Declare a
TTCmd*
pointer to point to a secondTTCmd
object for the REF CURSOR (cmdRefCursor
in the example). -
Use the first
TTCmd
object (cmdPLSQL
) to prepare the PL/SQL statement. -
Use the
TTCmd::registerParam()
method of the firstTTCmd
object to register the REF CURSOR as an output parameter. -
Use the first
TTCmd
object to execute the statement. -
Use the
TTCmd::getParam()
method of the firstTTCmd
object to retrieve the REF CURSOR into the secondTTCmd
object (using&cmdRefCursor
). There is agetParam(int
paramNo
, TTCmd**
rcCmd
)
signature for REF CURSORs. -
Fetch the results from the
TTCmd
object for the REF CURSOR and process as desired. -
Drop the first
TTCmd
object. -
Drop the pointer to the
TTCmd
object for the REF CURSOR. -
Issue a
delete
statement to delete theTTCmd
object for the REF CURSOR.
This example retrieves and processes a REF CURSOR from a PL/SQL anonymous block. See the preceding steps for an explanation.
...
TTCmd cmdPLSQL;
TTCmd* cmdRefCur;
TTConnection conn;
...
// c1 is a TT_INTEGER column.
cmdPLSQL.Prepare(&conn, "begin open :rc for select c1 from t; end;")
cmdPLSQL.registerParam(1, TTCmd::PARAM_OUT, SQL_REFCURSOR);
cmdPLSQL.Execute();
if (cmdPLSQL.getParam(1, &cmdRefCur) == false)
{
SQLINTEGER fetchval;
while (!cmdRefCursor->FetchNext()) {
cmdRefCur->getColumn(1, &fetchval);
}
cmdRefCursor->Drop();
delete cmdRefCursor;
}
cmdPLSQL.Drop();
Be aware of the following usage notes when using REF CURSORs in TTClasses:
-
For passing REF CURSORs between PL/SQL and an application, TimesTen supports only
OUT
REF CURSORs, from PL/SQL to the application, and supports a statement returning only a single REF CURSOR. -
Unlike
TTCmd::getParam()
calls for other data types, agetParam()
call with aTTCmd**
parameter for a REF CURSOR can only be called once. Subsequent calls returnNULL
. If you must retrieve a REF CURSOR a second time, you must reexecute the statement. -
If the statement is executed multiple times, the REF CURSOR parameter must be reregistered each time. For example, if you are executing the statement, getting the REF CURSOR parameter, and fetching from the REF CURSOR within a loop, then the parameter registration must also be in the loop, such as follows:
cmdPLSQL.Prepare(...); loop cmdPLSQL.registerParam(...); cmdPLSQL.Execute(); cmdPLSQL.getParam(...); fetch loop end loop
This is shown the example below.
-
Any
TTCmd
object, including one for a REF CURSOR, has an ODBC statement handle allocated for it. The REF CURSOR statement handle is dropped at the time of theDrop()
statement and the resource is freed after thedelete
statement.
The following example uses a REF CURSOR in a loop. Assume the following declarations and a TTConnection
instance conn
.
...
TTCmd query;
TTCmd* ref_cur;
...
Here is the loop:
...
cerr << "Selecting values using cursor" << endl;
query.Prepare(&conn, "begin open :rc for select c1 from t1; end;");
for (int round = 0; round < ROUNDS; round++) {
cerr << "executing ref cursor round# " << (round+1) << endl;
query.registerParam(1, TTCmd::PARAM_OUT, SQL_REFCURSOR);
query.Execute();
query.getParam(1, &ref_cur);
while(true) {
fetch_next = ref_cur -> FetchNext();
if (fetch_next == 1)
break;
ref_cur -> getColumn(1, &val);
cerr << "val = " << val << endl;
}
ref_cur->Drop();
delete ref_cur;
}
conn.Commit();
query.Drop();
...
Working with ROWIDs
Each row in a table has a unique identifier known as its rowid. An application can retrieve the rowid of a row from the ROWID
pseudocolumn. Rowids can be represented in either binary or character format.
An application can specify literal rowid values in SQL statements, such as in WHERE
clauses, as CHAR
constants enclosed in single quotes.
The ODBC SQL type SQL_ROWID
corresponds to the SQL type ROWID
.
For parameters and result set columns, rowids are convertible to and from the C types SQL_C_BINARY
, SQL_C_WCHAR
, and SQL_C_CHAR
. SQL_C_CHAR
is the default C type for rowids. The size of a rowid is 12 bytes as SQL_C_BINARY
, 18 bytes as SQL_C_CHAR
, and 36 bytes as SQL_C_WCHAR
.
Note that TTClasses has always supported rowids as character strings; however, a TTClasses application can now pass a rowid to a PL/SQL anonymous block as a ROWID
type instead of as a string. This involves using the TTCmd::registerParam()
method to register the rowid input parameter as SQL_ROWID
type, as shown this example:
...
TTConnection conn;
TTCmd cmd;
...
cmd.Prepare(&conn, "begin delete from t1 where rowid = :x; end;");
cmd.registerParam(1, TTCmd::PARAM_IN, SQL_ROWID);
cmd.setParam(1, rowid_string);
cmd.Execute();
...
Refer to ROWID Data Type and ROWID pseudocolumn in Oracle TimesTen In-Memory Database SQL
Reference for additional information about rowids and the ROWID
data type, including usage and life.
Note:
TimesTen does not support the PL/SQL type UROWID
.
Working with LOBs
This section discusses the use of LOBs (large objects) in TTClasses. This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).
The following topics are discussed:
You can also refer to the following.
-
Large objects (LOBs) in Oracle TimesTen In-Memory Database C Developer's Guide for an overview of LOBs and LOB programming interfaces for C and C++. Only the LOB simple data interface is applicable to TTClasses.
-
LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen
-
Oracle Database SecureFiles and Large Objects Developer's Guide for general information about programming with LOBs (but not specific to TimesTen functionality)
Differences Between TimesTen LOBs and Oracle Database LOBs
This section notes differences in LOB support between TimesTen and Oracle Database.
-
A key difference between the TimesTen LOB implementation and the Oracle Database implementation is that in TimesTen, a LOB used in an application does not remain valid past the end of the transaction. All such LOBs are invalidated after a commit or rollback, whether explicit or implicit. This includes after any DDL statement.
-
TimesTen does not support BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs.
-
TimesTen does not support binding arrays of LOBs.
-
TimesTen does not support batch processing of LOBs.
-
Relevant to BLOBs, there are differences in the usage of hexadecimal literals in TimesTen. see the description of
HexadecimalLiteral
in Constants in Oracle TimesTen In-Memory Database SQL Reference.
Using the LOB Simple Data Interface in TTClasses
The simple data interface enables applications to access LOB data by binding and defining, just as with other scalar types.
For the simple data interface in TTClasses, use getParam()
and setParam()
to bind parameters and use getColumn()
or getColumnNullable()
to define result columns. The application can bind or define using a SQL type that is compatible with the corresponding variable type, as follows:
-
For BLOB data, use SQL type
SQL_LONGVARBINARY
and C typeSQL_C_BINARY
. -
For CLOB data, use SQL type
SQL_LONGVARCHAR
and C typeSQL_C_CHAR
. -
For NCLOB data, use SQL type
SQL_WLONGVARCHAR
and C typeSQL_C_WCHAR
.
Note:
-
TTClasses does not support batch mode for LOBs.
-
Binding a CLOB or NCLOB with a C type of
SQL_C_BINARY
is prohibited.
The following example shows use of the LOB simple data interface in TTClasses. Assume a table with NCLOB
, BLOB
, and CLOB
columns has been created and populated. The methods executed on these LOB types are the same as for NCHAR
, BINARY
, and CHAR
, respectively.
#ifdef _WIN32
#include <ttcommon.h>
#endif
#include "TTInclude.h"
#define LOB_COL_SIZE 4194304
int main(int argc, char** argv) {
TTConnection conn;
TTCmd query;
char conn_str[100] = "... your connection string ...";
char tbl_name[20] = "... test table name ...";
int num_rows = 0;
char query_stmt[1000];
int fetch_next;
int value_is_null = 0;
int column_type;
SQLWCHAR * unicode_val;
u_char * binary_val;
char * alfanum_val;
SQLLEN b_len;
SQLLEN u_len;
cerr << "Connecting to TimesTen <" << conn_str << ">" << endl;
try {
conn.Connect(conn_str);
sprintf(query_stmt, "select * from %s", tbl_name);
query.Prepare(&conn, query_stmt);
query.Execute();
const int num_result_cols = query.getNColumns();
while (true) {
// loop until no rows found
// fetch a row; if no more rows, break out of loop
// FetchNext returns 0 for success, 1 for SQL_NO_DATA_FOUND
fetch_next = query.FetchNext();
if (fetch_next == 1)
break;
for (int col = 1; col <= num_result_cols; col++) {
value_is_null = 0;
column_type = query.getColumnType(col);
switch (column_type) {
case SQL_WLONGVARCHAR:
value_is_null = query.getColumnNullable(col,
(SQLWCHAR**) & unicode_val, &u_len);
if (value_is_null) {
cerr << "NCLOB value is NULL";
} else {
cerr << "NCLOB value length = " << u_len << endl;
// do something with NCLOB value
}
break;
case SQL_LONGVARBINARY:
value_is_null = query.getColumnNullable(col,
(void**) & binary_val, &b_len);
if (value_is_null) {
cerr << "BLOB value is NULL";
} else {
cerr << "BLOB value length = " << b_len << endl;
// do something with BLOB value
}
break;
case SQL_LONGVARCHAR:
alfanum_val = (char*) malloc(LOB_COL_SIZE + 1);
value_is_null = query.getColumnNullable(col, alfanum_val);
if (value_is_null) {
cerr << "CLOB value is NULL";
} else {
cerr << "CLOB value length = " << strlen(alfanum_val) << endl;
// do something with BLOB value
}
free(alfanum_val);
break;
default:
break;
}
}
num_rows++;
cerr << "row " << num_rows << " fetched" << endl;
}
cerr << num_rows << " rows returned" << endl;
} catch (TTError err) {
cerr << "\nError" << err << endl;
}
query.Drop();
conn.Disconnect();
return 0;
}
Passthrough LOBs in TTClasses
Passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen, are exposed as TimesTen LOBs and are supported by TimesTen in much the same way that any TimesTen LOB is supported.
However, note the following:
-
TimesTen LOB size limitations do not apply to storage of LOBs in the Oracle database through passthrough.
-
As with TimesTen local LOBs, a passthrough LOB used in an application does not remain valid past the end of the transaction.
Setting a Timeout or Threshold for Executing SQL Statements
TimesTen offers two ways for you to limit the time for SQL statements or procedure calls to execute, by setting either a timeout value or a threshold value.
When you set a timeout value, if the timeout duration is reached, the statement stops executing and an error is thrown. A value of 0 indicates no timeout. When you set a threshold value, if the threshold is reached, a warning is written to the support log but execution continues. A value of 0 means no warnings.
The query timeout limit has effect only when a SQL statement is actively executing. A timeout does not occur during commit or rollback.
Use the TTCmd
methods setQueryTimeout()
and setQueryThreshold()
to specify these settings for the TTCmd
object. Note that these methods override the settings of the TimesTen connection attributes SQLQueryTimeout
(or SQLQueryTimeoutMsec
) and QueryThreshold
, respectively. Each of these connection attributes has a default value of 0, for no timeout or no threshold.
There is also a getQueryThreshold()
method to read the current threshold setting.
In TTClasses, these features can be used only at the statement level, not the connection level.
See Timeouts and Thresholds for Executing SQL Statements in Oracle TimesTen In-Memory Database C Developer's Guide. For information about the relationship between timeout values, see Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide.
Note:
If both a lock timeout value and a SQL query timeout value are specified, the lesser of the two values causes a timeout first. Regarding lock timeouts, you can refer to ttLockWait (built-in procedure) or LockWait (general connection attribute) in Oracle TimesTen In-Memory Database Reference, or to Check for Deadlocks and Timeouts in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.
Using Automatic Client Failover in a TTClasses Application
TTClasses does not have its own functionality for automatic client failover, but a TTClasses application can configure TimesTen automatic client failover in the same way that an ODBC application can.
This is discussed in ODBC Support for Automatic Client Failover in Oracle TimesTen In-Memory Database C Developer's Guide. For TimesTen Scaleout, also see Client Connection Failover in Oracle TimesTen In-Memory Database Scaleout User's Guide. For TimesTen Classic, see Using Automatic Client Failover in Oracle TimesTen In-Memory Database Operations Guide.