4 TimesTen Support for Pro*C/C++

TimesTen OCI support

Because TimesTen support of the Oracle Pro*C/C++ Precompiler depends on TimesTen OCI support, restrictions for TimesTen OCI apply to Pro*C/C++ applications.

In addition, TimesTen does not support OCI calls that are related to functionality that does not exist in TimesTen.

For more information about TimesTen OCI support, see Chapter 3, "TimesTen Support for OCI." Much of the information there may apply to Pro*C/C++ applications as well.

Embedded SQL support and restrictions

The TimesTen Pro*C/C++ Precompiler does not support embedded SQL for functionality that TimesTen and TimesTen Cache do not support. See "TimesTen restrictions and differences".

TimesTen provides the following support for SQLLIB functions:

• SQLErrorGetText (sqlglmt) is supported.

• SQLRowidGet() is supported following only SELECT FOR UPDATE statements.

In addition, TimesTen support for the Oracle Pro*C/C++ Precompiler has the following restrictions:

• REGISTER CONNECT is not supported.

• Stored Java subprograms are not supported.

Semantic checking restrictions

TimesTen support for the Oracle Pro*C/C++ Precompiler does not provide semantic checking during precompilation. A SQLCHECK precompiler option setting that specifies semantic checking is permissible but has no effect.

It is important to be aware, however, that a setting of SEMANTICS results in a database connection even though precompilation semantic checking is not performed. Therefore, a setting of SEMANTICS requires the following during precompilation:

• The database must be running.

• The USERID precompiler option must be set, either on the command line or in the pcscfg.cfg configuration file. You must provide the user name and password for an existing TimesTen user, and a TNS name that points to the database. In the following example, you are prompted for the password:

  USERID=user1@my_tnsname
  

  Alternatively, you can enter USERID=user1/mypassword@my_tnsname, but for security reasons it is not advisable to specify a password on a command line or in a configuration file.

See "Connecting to a TimesTen database from Pro*C/C++" for information about usage and syntax for TNS names.

See the next section, "Embedded PL/SQL restrictions", for related information about Pro*C/C++ programs that use PL/SQL.

Embedded PL/SQL restrictions

In TimesTen, if a Pro*C/C++ application contains PL/SQL blocks, then Pro*C/C++ acts as though the SQLCHECK setting is SEMANTICS. It is important to be aware that this results in a database connection even though precompilation semantic checking is not performed. Therefore, using PL/SQL in a Pro*C/C++ application requires the following during precompilation:

• The database must be running.

• The USERID precompiler option must be set, specifying an existing TimesTen user. See the preceding section, "Semantic checking restrictions", for details about setting this option.

Transaction restrictions

Regarding transactions, TimesTen support for the Oracle Pro*C/C++ Precompiler does not provide the following:

• SAVEPOINT SQL statement

• SET TRANSACTION SQL statement

  You can still have transactions with commit and rollback, just not the SET TRANSACTION SQL statement.

• Fetch across commits

• Distributed transactions

Connection restrictions

Regarding connections, TimesTen support for the Oracle Pro*C/C++ Precompiler does not provide the following:

• ALTER AUTHORIZATION clause

• Automatic connections to the database

• Making connections to the database with SYSDBA or SYSOPER privilege, given that these privileges do not exist in TimesTen

• Implicit connections (dblinks) to a TimesTen or Oracle Database

For information about supported connection syntax, see "Connecting to a TimesTen database from Pro*C/C++".

Summary of unsupported or restricted executable commands and clauses

Given restrictions including those noted in the preceding sections, this section summarizes the Pro*C/C++ EXEC SQL executable commands, categories of commands, and command clauses that TimesTen does not support or supports only partially:

• ALTER AUTHORIZATION

• CACHE FREE ALL

• CALL

  This is supported only for calling PL/SQL. To call TimesTen built-in procedures, use dynamic SQL statements.

• Any "COLLECTION..." command

• COMMIT FORCE 'some text'

• COMMIT WORK COMMENT 'some text' RELEASE

  The COMMENT clause is not supported.

• CONNECT BY

• CONTEXT OBJECT OPTION GET

• CONTEXT OBJECT OPTION SET

• DECLARE CURSOR

  The WITH HOLD clause is not supported.

• DECLARE TABLE

  Only Oracle Database data types are supported.

• DECLARE TYPE

• EXPLAIN PLAN

• IN SYSDBA MODE

• IN SYSOPER MODE

• LOCK TABLE

• Any "OBJECT..." command

• PARTITION

• REGISTER CONNECT

• RETURN

• RETURNING

• SAVEPOINT

• SET DESCRIPTOR

  You cannot set CHARACTER_SET_NAME.

• SET TRANSACTION

• START WITH

• TO SAVEPOINT

The ttSrcScan utility

If you have an existing Pro*C/C++ program and want to see whether it uses Pro*C/C++ features that TimesTen does not support, you can use the ttSrcScan command line utility to scan your program for unsupported embedded SQL functions and types. This is a standalone utility that can be run without TimesTen or Oracle Database being installed and runs on any platform supported by TimesTen. It reads source code files as input and creates HTML and text files as output. If the utility finds unsupported items, they are logged and alternatives are suggested. You can find the ttSrcScan executable in the quickstart/sample_util directory in your TimesTen installation.

Specify an input file or directory for the program to be scanned and an output directory for the ttSrcScan reports. Other options are available as well. See the README file in the sample_util directory for information.

Environment and configuration for TimesTen Pro*C/C++

The Pro*C/C++ system configuration file pcscfg.cfg contains the precompiler options for precompilation of your Pro*C/C++ source code. In TimesTen, you must use the version of this file that TimesTen provides. This typically happens automatically if you ensure appropriate configuration for TimesTen through the TimesTen ttenv script. See "Environment variables" in the Oracle TimesTen In-Memory Database Installation Guide for information about ttenv.

Building a Pro*C/C++ application

Before building a Pro*C/C++ application, you must set up your environment:

1. You can use the TimesTen OCI and Pro*C/C++ Makefiles provided with the Quick Start demos to implement appropriate environment settings. These are in the following locations (assuming the standard Quick Start location):

   install_dir/quickstart/sample_code/oci/
   install_dir/quickstart/sample_code/proc/
   

2. Confirm LD_LIBRARY_PATH or PATH is set so that the Oracle Instant Client directory precedes the Oracle Database libraries in the path. The path is set properly if you use the install_dir/bin/ttenv script or quickstart/ttquickstartenv script. See "Environment variables" in Oracle TimesTen In-Memory Database Installation Guide for information about environment variables and ttenv.

Then use steps such as the following to build a Pro*C/C++ application. The steps shown here present a basic example for a UNIX system and assume the program has no other includes (#include) or links to other libraries. The designation instant_client represents the directory where Oracle Instant Client is installed.

See the Quick Start Pro*C/C++ Makefile in the quickstart/sample_code/proc directory for complete, platform-specific examples.

1. Precompile the Pro*C/C++ source file by using the proc command from your system prompt. For example:

   % proc iname=sample.pc
   

   The proc utility takes a .pc source file as input and produces a .c file.

2. Compile the resulting C code file. On Linux platforms, enter a command similar to the following:

   % gcc -c sample.c -I(instant_client)/sdk/
   

3. Link the resulting object modules with modules in SQLLIB. For example:

   % gcc -o sample sample.o -L(instant_client)/lib -lclntsh
   

Connecting to a TimesTen database from Pro*C/C++

This section provides information on connecting to TimesTen from a Pro*C/C++ application. TimesTen Pro*C/C++ and OCI use the Oracle Instant Client to connect to the TimesTen database. Refer to "Connecting to a TimesTen database from OCI" for additional configuration steps to use the tnsnames naming method or easy connect naming method to connect to the database.

The following topics are covered here:

• Connection syntax and parameters

• Using tnsnames or easy connect

• Specifying the Oracle Database password in Pro*C/C++ for TimesTen Cache

• Connecting as an externally identified user in Pro*C/C++

EXEC SQL CONNECT{:user IDENTIFIED BY :pwd | :user_string}
  [[AT{dbname |:host_variable}]USING :connect_string];

EXEC SQL CONNECT :dbstring

EXEC SQL CONNECT :user1 IDENTIFIED BY :pwd1

text *cacheuser = (text *)"cacheuser1";
text *cachepwds = (text *)"ttpwd;OraclePWD=orclpwd";
text *dbname = (text *)"tt_tnsname";
....
EXEC SQL CONNECT :cacheuser IDENTIFIED BY :cachepwds AT :dbname

text *instanceadmin = (text *)"[myadmin]";
text *instanceadminpwd = (text *)"";
text *dbname = (text *)"tt_tnsname";
....
EXEC SQL CONNECT :instanceadmin IDENTIFIED BY :instanceadminpwd AT :dbname

Error reporting and handling

Be aware of the following regarding error conditions and error reporting:

• Errors under TimesTen Pro*C/C++ applications return Oracle Database error codes. TimesTen attempts to report the same error code as Oracle Database would under similar conditions. The error messages may come from either the TimesTen catalog or the Oracle Database catalog. Some error messages may indicate the accompanying TimesTen error code if appropriate. Pro*C/C++ applications that rely on parsing error codes should be checked.

• TimesTen supports the WHENEVER SQLERROR directive, to go to an error handler if an error occurs, and the WHENEVER NOT FOUND directive, to go to a handling section if a "no data found" condition occurs. TimesTen does not support the WHENEVER SQLWARNING directive.

  Examples:

  EXEC SQL WHENEVER NOT FOUND GOTO close_cursor;
  ...
  EXEC SQL WHENEVER SQLERROR GOTO error_handler;
  

Pro*C/C++ demo programs

TimesTen ships Pro*C/C++ demo programs. They are in the quickstart/sample_code/proc directory. The README file in the directory explains how to compile and run the demos.

Refer to the Quick Start welcome page at install_dir/quickstart.html for information.

Associative array bindings in TimesTen Pro*C/C++

As discussed in "Associative array bindings in TimesTen OCI", associative arrays, formerly known as index-by tables or PL/SQL tables, are supported as IN, OUT, or IN OUT bind parameters in TimesTen PL/SQL. See that section for additional information and limitations.

You can pass associative arrays between PL/SQL blocks and Pro*C/C++ applications as well as OCI applications. They can be indexed by a PL/SQL variable of type BINARY_INTEGER or PLS_INTEGER.

Normally, the entire host array is passed to PL/SQL, but you can use the Pro*C/C++ ARRAYLEN statement to specify a smaller array dimension.

For more information, refer to "PL/SQL Tables", "Host Arrays", and "ARRAYLEN Statement" under "Embedded PL/SQL" in Pro*C/C++ Programmer's Guide.

...
float salary[100];
/* populate the host array */
EXEC SQL EXECUTE
  DECLARE
    TYPE NumTabTyp IS TABLE OF REAL
                   INDEX BY BINARY_INTEGER;
    median_salary REAL;
    n BINARY_INTEGER;
...
  FUNCTION median (num_tab NumTabTyp, n INTEGER)
    RETURN REAL IS
  BEGIN
    -- compute median
  END;
  BEGIN
    n := 100;
    median_salary := median(:salary, n);
    ...
  END;
END-EXEC;
...

LOBs in TimesTen Pro*C/C++

TimesTen supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

See "Working with LOBs". That section is ODBC-oriented but also provides a general overview of LOBs, differences between TimesTen and Oracle Database LOBs, and LOB programming interfaces. Also see "LOBs in TimesTen OCI" for information about LOB locators, temporary LOBs, using the simple data interface or LOB locator interface in OCI, and additional OCI LOB features.

This section focuses on key Pro*C/C++ LOB features and TimesTen-specific support and restrictions.

See "LOB data types" in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen.

For complete information about LOBs and how to use them in Pro*C/C++, refer to "LOBs" in Pro*C/C++ Programmer's Guide, keeping in mind that TimesTen does not support BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs. In particular, see "How to Use LOBs in Your Program" within that chapter.

The following topics are covered for Pro*C/C++:

• Using the LOB simple data interface in Pro*C/C++

• Using the LOB locator interface in Pro*C/C++

EXEC SQL CREATE TABLE lob_table ( a_blob BLOB, a_clob CLOB );
...
EXEC SQL INSERT INTO lob_table (a_blob, a_clob) 
                VALUES (EMPTY_BLOB(), EMPTY_CLOB());
...
EXEC SQL CREATE TABLE data_table
         ( name VARCHAR2(30), length NUMBER(10), bincol BLOB, charcol CLOB );

...
OCIBlobLocator *myblob;
OCIClobLocator *myclob;
...
EXEC SQL SELECT bincol, charcol INTO :myblob, :myclob FROM data_table 
                WHERE name = :key;
...
// Put data into lob_table.
...
EXEC SQL INSERT INTO lob_table (a_blob, a_clob) VALUES (:myblob, :myclob);

OCIClobLocator CHARACTER SET IS NCHAR_CS *mynclob;

OCIClobLocator *tempclob;
EXEC SQL ALLOCATE :tempclob;
EXEC SQL LOB CREATE TEMPORARY :tempclob;
...
// (Manipulate LOB as desired.)
...
EXEC SQL FREE TEMPORARY :tempclob;
EXEC SQL FREE :tempclob;

status = OCILobCreateTemporary(svc, err, tempclob, OCI_DEFAULT, SQLCS_NCHAR,
         OCI_TEMP_CLOB, TRUE, OCI_DURATION_TRANSACTION); 

OCIClobLocator *license_txt;
...
EXEC SQL ALLOCATE :license_txt;
...
EXEC SQL SELECT name, txt_summary INTO :name, :license_txt FROM license_table
                WHERE sss = :sss;

EXEC SQL LOB READ :amt FROM :blob INTO :buffer;

EXEC SQL LOB WRITE ONE :amt FROM :buffer INTO :blob;

EXEC SQL LOB WRITE APPEND :amt FROM :writebuf INTO :blob;

...
OCIClobLocator *a_clob;
char *charbuf;
ub4 ClobLen, WriteAmt;
int CharLen = strlen(charbuf);
int NewCharbufLen = CharLen + DATELENGTH + 4;
varchar *NewCharbuf;
NewCharbuf = (varchar *)malloc(2 + NewCharbufLen);
NewCharbuf->arr[0] = '\n';
NewCharbuf->arr[1] = '\0';
strcat((char *)NewCharbuf->arr, charbuf);
NewCharbuf->arr[CharLen + 1] = '\0';
strcat((char *)NewCharbuf->arr, curdate);
NewCharbuf->len = NewCharbufLen;
EXEC SQL LOB DESCRIBE :a_clob GET LENGTH INTO :ClobLen;
WriteAmt = NewCharbufLen;
EXEC SQL LOB WRITE ONE :WriteAmt FROM :NewCharbuf WITH LENGTH :NewCharbufLen 
                       INTO :a_clob;
...

...
 EXEC SQL select b into :blob from t where pk = 1 for update;
 EXEC SQL LOB OPEN :blob READ WRITE;
 
 // Write/append to the BLOB
 for (i = 0; i < MAX_CHUNKS; i++) {
   if (i==0) { // FIRST CHUNK
     /*
     Write the first piece
     */
     EXEC SQL LOB WRITE ONE :amt FROM :writebuf INTO :blob;
 
   }
   else { // All Other Chunks
     /*
     At this point, APPEND all the next pieces
     */
     EXEC SQL LOB WRITE APPEND :amt FROM :writebuf INTO :blob ;
   }
   ...
 }
...

Precompiler option support

Table 4-2 describes TimesTen Pro*C/C++ Precompiler option support.

Setting precompiler options

You can set precompiler options in the following ways.

• At compile time, either in the configuration file pcscfg.cfg or on the Pro*C/C++ command line

  A setting on the command line takes precedence over a setting in the configuration file.

• At runtime through the EXEC ORACLE OPTION command

  A runtime setting takes precedence over a compile-time setting.

For example, the following shows portions of the configuration file that ships with TimesTen.

ltype=short
parse=full
close_on_commit=yes
...

The following command line would override the ltype=short setting from the configuration file:

% proc ltype=long ... iname=sample.pc

The following runtime command would override the ltype=long setting from the command line:

EXEC ORACLE OPTION LTYPE=NONE;