TimesTen Features With Pro*C/C++

This section covers additional features you can use with Pro*C/C++ in TimesTen.

• Associative Array Bindings in TimesTen Pro*C/C++

• LOBs in TimesTen Pro*C/C++

Associative Array Bindings in TimesTen Pro*C/C++

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.

As discussed in Binding Associative Arrays 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.

Typically, 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.

See PL/SQL Tables, Host Arrays, and ARRAYLEN Statement in Embedded PL/SQL in Pro*C/C++ Programmer's Guide.

The following code excerpt shows the array salary[] being bound from Pro*C/C++ into the associative array num_tab in PL/SQL.

...
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 Classic supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

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

These topics are covered:

• Using the LOB Simple Data Interface in Pro*C/C++

• Using the LOB Locator Interface in Pro*C/C++

See the following for additional information:

• Large Objects (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.

• 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.

• LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen.

• LOBs in Pro*C/C++ Programmer's Guide for complete information about LOBs and how to use them in Pro*C/C++, 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.

Note:

• As indicated in the OCI chapter, in TimesTen a LOB used in an application does not remain valid past the end of the transaction.

• The LOB piecewise data interface is not applicable to OCI or Pro*C/C++ applications in TimesTen. (You can, however, manipulate LOB data in pieces through features of the LOB locator interface.)

Using the LOB Simple Data Interface in Pro*C/C++

The simple data interface enables applications to manipulate LOB data similarly to how they would manipulate other types of scalar data, such as by using EXEC SQL INSERT and EXEC SQL SELECT. The application can use a LOB type that is compatible with the corresponding variable type.

An application can use the EMPTY_BLOB() or EMPTY_CLOB() function, as appropriate, to initialize a persistent LOB. This is similar to using ALLOCATE in the LOB locator interface, discussed next. Consider the following tables:

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 );

The following selects LOB data from data_table into myblob and myclob, then inserts the LOB data into lob_table.

...
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);

To use an NCLOB, declare the variable as follows:

OCIClobLocator CHARACTER SET IS NCHAR_CS *mynclob;

Note:

The simple data interface, through OCI or Pro*C/C++, limits bind sizes to 64 KB.

Using the LOB Locator Interface in Pro*C/C++

You can use the Pro*C/C++ LOB locator interface to work with either LOBs from the database or temporary LOBs, either piece-by-piece or in whole chunks.

Refer to LOB Statements in Pro*C/C++ Programmer's Guide for detailed information about Pro*C/C++ statements for LOBs, noting that TimesTen does not support features specifically intended for BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs.

See lobdemo1.pc in Pro*C/C++ Programmer's Guide for an end-to-end example.

Also see About Using the LOB Locator Interface in OCI.

Tip:

If Pro*C/C++ syntax does not provide enough functionality to fully specify what you want to accomplish for any operation, you can use the corresponding OCI function as an alternative.

In Pro*C/C++, an application can create a temporary LOB by using the CREATE TEMPORARY embedded SQL feature, after first using the ALLOCATE feature to allocate the locator. Use FREE to free the allocation for the locator and FREE TEMPORARY to free the temporary LOB itself. This is shown immediately below.

Also see Creating a Temporary LOB in OCI.

Tip:

In TimesTen, creation of a temporary LOB results in creation of a database transaction if one is not already in progress. To avoid error conditions, execute a commit or rollback to close the transaction.

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;

Alternatively, to specify the LOB character set (here NCHAR), you can use the corresponding OCI function:

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

To access the locator of a persistent LOB in Pro*C/C++, an application typically accesses the LOB from the database by using a SQL statement to obtain the locator, then passing the locator to an appropriate API function.

The following excerpts are from the previously mentioned example in lobdemo1.pc in Pro*C/C++ Programmer's Guide. The example uses a CLOB license_txt and table license_table whose columns are social security number, name, and text summarizing driving offenses (a CLOB column). Also see Accessing the Locator of a Persistent LOB in OCI.

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

To read and write LOB data using the LOB locator interface, a Pro*C/C++ application can use LOB OPEN and LOB CLOSE to open and close a LOB, LOB READ to read LOB data, LOB WRITE or LOB WRITE APPEND to write or append LOB data, LOB DESCRIBE to obtain information about a LOB, and various other Pro*C/C++ features to perform a variety of other actions. All the Pro*C/C++ LOB locator interface features are covered in detail in LOBs in Pro*C/C++ Programmer's Guide. Also see Reading and Writing LOB Data Using the OCI LOB Locator Interface in this document.

To write data, use LOB WRITE ONE to write the data in a single chunk. TimesTen does not support LOB WRITE FIRST, LOB WRITE NEXT, or LOB WRITE LAST (features of the piecewise data interface).

Here is an example of an EXEC SQL LOB READ statement:

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

Refer to READ a BLOB, Write a File Example in Pro*C/C++ Programmer's Guide for additional information.

Here is an example of an EXEC SQL LOB WRITE statement (writing the LOB data in one chunk):

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

Refer to Read a File, WRITE a BLOB Example in Pro*C/C++ Programmer's Guide.

Here is an example of an EXEC SQL LOB WRITE APPEND statement:

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

Note:

Opening a LOB is similar conceptually, but not technically, to opening a file. Opening a LOB is more like a hint regarding resources to be required.

Be aware that a LOB being accessed by OCILobRead(), OCILobWrite(), or equivalent functionality is opened automatically as necessary.

The following excerpt is from the previously mentioned example in lobdemo1.pc in Pro*C/C++ Programmer's Guide.

...
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;
...

The next example, like the preceding one, uses LOB WRITE ONE. Then it also uses LOB WRITE APPEND to append additional data. It writes or appends to the BLOB in 1 K chunks up to MAX_CHUNKS.

...
 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 ;
   }
   ...
 }
...