4 TimesTen Support for Oracle Pro*C/C++ Precompiler

Oracle TimesTen In-Memory Database and Oracle IMDB Cache support the Oracle Pro*C/C++ Precompiler for C and C++ applications. You can use the precompiler with embedded SQL and PL/SQL applications that access the TimesTen database.

This chapter includes the following topics:

It provides only an overview and TimesTen-specific information regarding Pro*C/C++. For complete general information, you can refer to Pro*C/C++ Programmer's Guide in the Oracle Database library.

Overview of the Oracle Pro*C/C++ Precompiler

The Oracle Pro*C/C++ Precompiler enables you to embed SQL statements or PL/SQL blocks directly into C or C++ code. Further, you can use your C or C++ program host variables in your embedded SQL or PL/SQL.

You use a precompilation step to convert the Pro*C/C++ source file into a C or C++ source file. The precompiler accepts the Pro*C/C++ file as input, translates embedded SQL statements into standard Oracle runtime library calls, and generates a modified source code file that you can then compile and link. Pro*C/C++ code is linked against the Oracle precompiler SQLLIB library, which is shipped with TimesTen as part of the Oracle Instant Client.

Overview of TimesTen support for Pro*C/C++

TimesTen support for the Oracle Pro*C/C++ Precompiler depends on TimesTen OCI. TimesTen OCI depends on the Oracle client library and the TimesTen ODBC libraries. See Figure 3-1 to see where OCI and Pro*C/C++ fit in the TimesTen architecture.

This chapter contains information specific to using the Oracle Pro*C/C++ Precompiler with TimesTen. The syntax and usage of the Oracle Pro*C/C++ Precompiler with TimesTen is essentially the same as with Oracle Database.

The rest of this section includes the following topics.

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 Oracle Call Interface." Much of the information there may apply to Pro*C/C++ applications as well.

Embedded SQL support and restrictions

TimesTen supports SQL92 standards. Oracle supports SQL99 standards.

The TimesTen Pro*C/C++ Precompiler does not support embedded SQL for functionality that TimesTen and IMDB 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 include 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 will be 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 include 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 include 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:

  • ALTER AUTHORIZATION

  • CACHE FREE ALL

  • CALL: 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 TABLE: Supports only Oracle data types.

  • DECLARE TYPE

  • EXPLAIN PLAN

  • IN SYSDBA MODE

  • IN SYSOPER MODE

  • Any "LOB..." command

  • LOCK TABLE

  • Any "OBJECT..." command

  • PARTITION

  • REGISTER CONNECT

  • RETURN

  • RETURNING

  • SAVEPOINT

  • SET DESCRIPTOR: 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 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.

Getting started with TimesTen Pro*C/C++

This section covers the following topics for getting started with a Pro*C/C++ application for TimesTen:

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:

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

    (Unless you installed Quick Start in a different location.)

  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 will be 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/include
    
  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. Also see "Connecting to a TimesTen database from OCI" for information about using the tnsnames naming method or easy connect naming method to connect to the database.

The following topics are covered here:

Note:

A TimesTen connection cannot be inherited from a parent process. If a process opens a database connection before creating a child process, the child must not use the connection. In Pro*C/C++, to avoid having a child process inadvertently inherit a connection from its parent, use EXEC SQL COMMIT RELEASE in the parent before creating the child.

Connection syntax and parameters

TimesTen supports the following connection syntax:

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

The parameters are described in Table 4-1.

Table 4-1 Connection parameters

Parameter Description

user

This is the user name.

pwd

This is the user password.

user_string

As an alternative to separate user and pwd entries, user_string is a user name and password separated by a slash, such as user1/pwd1. After an "@" sign, you can also include a database identifier, instead of using dbname, or a TNS name or easy connect string, instead of using connect_string. See examples in the next section, "Using tnsnames or easy connect".

dbname

This is a database identifier declared in a previous DECLARE DATABASE statement.

host_variable

This is a variable whose value is a database identifier.

connect_string

This is a valid TNS name or easy connect string for a TimesTen database.


Using tnsnames or easy connect

Your EXEC SQL CONNECT syntax can be simplified if you use the Oracle tnsnames or easy connect method.

From Pro*C/C++, you can use a host variable to include the user name, password, and a TNS name. For example:

EXEC SQL CONNECT :dbstring

Where dbstring is set to "user1/pwd1@my_tnsname".

Alternatively, the host variable could include the user name, password, and an easy connect string. For example, dbstring could be set to "user1/pwd1@localhost/ttclient:timesten_client".

Or, if the TWO_TASK or LOCAL environment variable, as applicable for your operating system, is set to "my_tnsname" or "localhost/ttclient:timesten_client", you could connect as in the following example:

EXEC SQL CONNECT :user1 IDENTIFIED BY :pwd1

Specifying the Oracle password in Pro*C/C++ for IMDB Cache

To use IMDB Cache, there must be a cache user in the TimesTen database with the same name as an Oracle Database user who can select from and update the cached Oracle tables. This Oracle user, for example, can be the cache administration user or a schema user. The password of the TimesTen cache user can be different from the password of the Oracle user with the same name. See "Setting Up a Caching Infrastructure" in Oracle In-Memory Database Cache User's Guide for details.

For use of Pro*C/C++ with IMDB Cache, TimesTen allows you to pass the Oracle user's password through Pro*C/C++ by appending it to the password field in an EXEC SQL CONNECT call when you log in to TimesTen. Use the attribute OraclePWD in the connect string, such as in the following example:

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

You must always specify OraclePWD, even if the Oracle user's password is the same as the TimesTen user's password. Furthermore, in the circumstance of specifying an Oracle password for IMDB Cache, you must use a form of EXEC SQL CONNECT that specifies the password as a separate host variable. In this example, cacheuser1 is the name of the TimesTen cache user as well as the name of the Oracle user who can access the cached Oracle tables, ttpwd is the password of the TimesTen cache user, orclpwd is the password of the Oracle user, and tt_tnsname is the TNS name of the TimesTen database being connected to. The Oracle database is specified through the TimesTen OracleNetServiceName general connection attribute in the odbc.ini or sys.odbc.ini file.

Alternatively, instead of using the AT clause with a TNS name, you could use the TWO_TASK or LOCAL environment variable, as discussed in "Connecting to a TimesTen database from OCI".

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

You can connect through Pro*C/C++ as an externally identified user (external user) by specifying the user name in brackets, such as "[myadmin]", and the password as an empty string, "".

In particular, this is useful in connecting as the instance administrator, which in TimesTen is always an external user.

Consider the following example.

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

This functionality uses OCI proxy syntax. You can refer to the discussion of client access through a proxy in Oracle Call Interface Programmer's Guide.

Error reporting and handling

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

  • Errors under TimesTen Pro*C/C++ applications return Oracle error codes. TimesTen attempts to report the same Oracle error code as Oracle would under similar conditions. The error messages may come from either the TimesTen catalog or the Oracle catalog. Some error messages may include 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.

TimesTen Pro*C/C++ Precompiler options

This section discusses Pro*C/C++ Precompiler option support by TimesTen.

Precompiler option support

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

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

Option Notes

AUTO_CONNECT

Supported value: NO (default).

CHAR_MAP

Supported.

CINCR

Setting has no effect because TimesTen supports only CPOOL=NO.

CLOSE_ON_COMMIT

Supported value: YES.

The Oracle default value of NO is overridden by TimesTen.

CMAX

Setting has no effect because TimesTen supports only CPOOL=NO.

CMIN

Setting has no effect because TimesTen supports only CPOOL=NO.

CNOWAIT

Setting has no effect because TimesTen supports only CPOOL=NO.

CODE

Supported.

COMP_CHARSET

Supported.

CONFIG

Supported.

CPOOL

Supported value: NO (default).

CPP_SUFFIX

Supported.

CTIMEOUT

Setting has no effect because TimesTen supports only CPOOL=NO.

DB2_ARRAY

Supported.

DBMS

Supported value: NATIVE (default).

DEF_SQLCODE

Supported.

DEFINE

Supported.

DURATION

Setting has no effect because TimesTen does not support objects.

DYNAMIC

Supported.

ERRORS

Supported.

ERRTYPE

Not supported.

EVENTS

Both values allowed, but TimesTen OCI does not support Advanced Queuing.

FIPS

Supported.

HEADER

Supported.

HOLD_CURSOR

Supported.

IMPLICIT_SVPT

Supported value: NO (default).

INAME

Supported.

INCLUDE

Supported.

INTYPE

Supported.

LINES

Supported.

LNAME

Supported.

LTYPE

Supported.

MAX_ROW_INSERT

Supported.

MAXLITERAL

Supported.

MAXOPENCURSORS

Supported.

MODE

Supported.

NATIVE_TYPES

Supported.

NLS_CHAR

Supported.

NLS_LOCAL

Supported value: NO (default).

OBJECTS

Setting has no effect because TimesTen does not support objects.

ONAME

Supported.

ORACA

Supported.

OUTLINE

All values are allowed, but TimesTen does not support Oracle optimization.

OUTLNPREFIX

Both values are allowed, but TimesTen does not support Oracle optimization.

PAGELEN

Supported.

PARSE

Supported.

PREFETCH

Supported.

RELEASE_CURSOR

Supported.

RUNOUTLINE

Not supported. Both values (yes|no) are allowed but ignored.

SELECT_ERROR

Supported.

SQLCHECK

Any of the SQLCHECK settings is allowed, but TimesTen does not support semantic checking during precompilation.

Whenever a Pro*C/C++ application uses PL/SQL, Pro*C/C++ acts as though the SQLCHECK setting is SEMANTICS.

Important: A setting of SEMANTICS (or FULL, which is synonymous) always results in a connection to the database, even though precompilation semantic checking is not performed.

See "Semantic checking restrictions".

STMT_CACHE

Supported.

SYS_INCLUDE

Supported.

THREADS

Supported.

TYPE_CODE

Supported.

UNSAFE_NULL

Supported.

USERID

Supported.

UTF16_CHARSET

Only the NCHAR_CHARSET setting is supported.

VARCHAR

Supported.

VERSION

Setting has no effect because TimesTen does not support objects.


Note:

TimesTen does not support the default value for CLOSE_ON_COMMIT. TimesTen supports only CLOSE_ON_COMMIT=YES.

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;