5 Advanced Topics

Precompiler Option CHAR_MAP

The CHAR_MAP precompiler command line option is available to specify the default mapping of char[n] and char host variables. Oracle maps them to CHARZ. CHARZ implements the ANSI Fixed Character format. Strings are fixed-length, blank-padded and null-terminated. VARCHAR2 values (including nulls) are always fixed-length and blank-padded. Table 5-1 shows the possible settings of CHAR_MAP:

The default mapping is CHAR_MAP=CHARZ, which was the case in previous versions of Pro*C/C++.

Use CHAR_MAP=VARCHAR2 instead of the old DBMS=V6_CHAR, which is obsolete.

Inline Usage of the CHAR_MAP Option

Unless you declared a char or char[n] variable otherwise, the inline CHAR_MAP option determines its mapping. The following code fragment illustrates the results of setting this option inline in Pro*C/C++:

char ch_array[5];

strncpy(ch_array, "12345", 5);
/* char_map=charz is the default in Oracle7 and Oracle8 */
EXEC ORACLE OPTION (char_map=charz);
/* Select retrieves a string "AB" from the database */
SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', ' ', ' ', '\0' } */

strncpy (ch_array, "12345", 5);
EXEC ORACLE OPTION (char_map=string) ;
/* Select retrieves a string "AB" from the database */
EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', '\0', '4', '5' } */

strncpy( ch_array, "12345", 5);
EXEC ORACLE OPTION (char_map=charf);
/* Select retrieves a string "AB" from the database */
EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', ' ', ' ', ' ' } */ 

Effect of the DBMS and CHAR_MAP Options

The DBMS and CHAR_MAP options determine how Pro*C/C++ treats data in character arrays and strings. These options allow your program to observe compatibility with ANSI fixed-length strings, or to maintain compatibility with previous releases of Oracle and Pro*C/C++ that use variable-length strings. See Chapter 10, " Precompiler Options" for a complete description of the DBMS and CHAR_MAP options.

The DBMS option affects character data both on input (from your host variables to the Oracle table) and on output (from an Oracle table to your host variables).

Character Array and the CHAR_MAP Option

The mapping of character arrays can also be set by the CHAR_MAP option independent of the DBMS option. DBMS=V7 or DBMS=V8 both use CHAR_MAP=CHARZ, which can be overridden by specifying either CHAR_MAP=VARCHAR2 or STRING or CHARF.

char emp_name[10]; 
... 
strcpy(emp_name, "MILLER");     /* WRONG! Note no blank-padding */ 
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES 
    (1234, :emp_name, 20); 

. . . WHERE ename = 'MILLER'; 

strncpy(emp_name, "MILLER    ", 10); /* 4 trailing blanks */ 
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES 
    (1234, :emp_name, 20); 

char emp_name[11];  /* Note: one greater than column size of 10 */ 
... 
strcpy(emp_name, "MILLER");        /* No blank-padding required */ 
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES 
    (1234, :emp_name, 20); 

TABLE strdbase ( ..., strval VARCHAR2(6));

""        -- string of length 0
"AB"      -- string of length 2
"KING"    -- string of length 4
"QUEEN"   -- string of length 5
"MILLER"  -- string of length 6

char  str[5] = {'X', 'X', 'X','X', 'X'} ;
short str_ind;
...
EXEC SQL SELECT strval INTO :str:str_ind WHERE ... ;

strval = ""         "AB"       "KING"     "QUEEN"    "MILLER"
---------------------------------------------------------------
VARCHAR2 "     " -1 "AB   " 0  "KING "  0 "QUEEN"  0 "MILLE"  6
CHARF    "XXXXX" -1 "AB   " 0  "KING "  0 "QUEEN"  0 "MILLE"  6
CHARZ    "    0" -1 "AB  0" 0  "KING0"  0 "QUEE0"  5 "MILL0"  6
STRING   "0XXXX" -1 "AB0XX" 0  "KING0"  0 "QUEE0"  5 "MILL0"  6

CREATE TABLE test_char (C_col CHAR(10), V_col VARCHAR2(10)); 
 
INSERT INTO test_char VALUES ('MILLER', 'KING'); 

... 
char name1[10]; 
char name2[10]; 
... 
EXEC SQL SELECT C_col, V_col INTO :name1, :name2 
    FROM test_char; 

"MILLER####" 

"KING######"      /* 6 trailing blanks */ 

"MILLER###\0" /* 3 trailing blanks, then a null-terminator */ 

"KING#####\0" 

... 
char *p_name1; 
char *p_name2; 
... 
p_name1 = (char *) malloc(11); 
p_name2 = (char *) malloc(11); 
strcpy(p_name1, "          "); 
strcpy(p_name2, "0123456789"); 
 
EXEC SQL SELECT C_col, V_col INTO :p_name1, :p_name2 
    FROM test_char; 

"MILLER####\0"     /* 4 trailing blanks and a null terminator */ 
 
"KING######\0"    /* 6 blanks and null */ 

VARCHAR Variables and Pointers

The following example shows how VARCHAR host variables are declared:

VARCHAR   emp_name1[10];   /* VARCHAR variable   */ 
VARCHAR  *emp_name2;       /* pointer to VARCHAR */

strcpy((char *)emp_name1.arr, "VAN HORN"); 
emp_name1.len = strlen((char *)emp_name1.arr); 

emp_name2 = malloc(sizeof(short) + 10)   /* len + arr */ 
strcpy((char *)emp_name2->arr, "MILLER"); 
emp_name2->len = strlen((char *)emp_name2->arr); 

emp_name2 = &emp_name1;
 

EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO) 
    VALUES (:emp_number, :emp_name2, :dept_number); 

emp_name1.arr[emp_name1.len] = '\0'; 
printf("%s", emp_name1.arr); 

printf("%.*s", emp_name1.len, emp_name1.arr); 

emp_name2->len = 10;  /* Set maximum length of buffer. */ 
EXEC SQL SELECT ENAME INTO :emp_name2 WHERE EMPNO = 7934; 
printf("%d characters returned to emp_name2", emp_name2->len);

Unicode Variables

Pro*C/C++ allows fixed-width Unicode data (character set Unicode Standard Version 3.0, known simply as UCS-16) in host char variables. UCS-16 uses 2 bytes for each character, so it is an unsigned 2-byte datatype. SQL statement text in UCS-16 is not supported yet.

In the following example code a host variable, employee, of the Unicode type utext is declared to be 20 Unicode characters long. A table emp is created containing the column ename, which is 60 bytes long, so that database character sets in Asian languages, where multibyte characters are up to three bytes long, will be supported.

utext employee[20] ;                               /* Unicode host variable   */
EXEC SQL CREATE TABLE emp (ename CHAR(60));
/* ename is in the current database character set  */                                        
EXEC SQL INSERT INTO emp (ename) VALUES ('test') ; 
/* 'test' in NLS_LANG encoding converted to database character set */
EXEC SQL SELECT * INTO :employee FROM emp ;       
/* Database character set converted to Unicode */

A public header file, sqlucs2.h, must be included in your application code. It does the following:

• Contains the statement:

    #include <oratypes.h>

• Defines a "Unicode varchar", uvarchar, as:

    struct uvarchar
    {
       ub2 len;
       utext arr[1] ;
    };
    typedef struct uvarchar uvarchar ;

• Defines a "Unicode long varchar", ulong_varchar, as:

    struct ulong_varchar
    {
       ub4 len ;
       utext arr[1] ;
    }
    typedef struct ulong_varchar ulong_varchar ;

The default datatype of utext is the same as the default for any character variables, CHARZ, which is blank-padded and null-terminated.

Use the CHAR_MAP precompiler option to change the default datatype, as follows:

#include <sqlca.h>
#include <sqlucs2.h>

main()
{
   utext employee1[20] ;

/* Change to STRING datatype:    */
   EXEC ORACLE OPTION (CHAR_MAP=STRING) ;
   utext employee2[20] ;

   EXEC SQL CREATE TABLE emp (ename CHAR(60)) ;
   ...
/***********************************************************  
  Initializing employee1 or employee2 is compiler-dependent.   
 **********************************************************/
   EXEC SQL INSERT INTO emp (ename) VALUES (:employee1) ;
   ...
   EXEC SQL SELECT ename INTO :employee2 FROM emp;
/* employee2 is now not blank-padded and is null-terminated  */
   ...

#include oratypes.h
utext sqlstmt[100] ;
...
/* If sqlstmt contains a SQL statement: */
EXEC SQL PREPARE s1 FROM :sqlstmt ;
EXEC SQL EXECUTE IMMEDIATE :sqlstmt ;
...

    typedef utext utext_5 ;
    EXEC SQL TYPE utext_5 IS STRING ;

Host Variable Equivalencing

By default, the Pro*C/C++ Precompiler assigns a specific external datatype to every host variable.

Table 5-2 lists the default assignments:

With the VAR statement, you can override the default assignments by equivalencing host variables to Oracle external datatypes. The syntax you use is

EXEC SQL VAR host_variable IS type_name [ (length) ]; 

where host_variable is an input or output host variable (or host array) declared earlier, type_name is the name of a valid external datatype, and length is an integer literal specifying a valid length in bytes.

Host variable equivalencing is useful in several ways. For example, suppose you want to SELECT employee names from the EMP table, then pass them to a routine that expects null-terminated strings. You need not explicitly null-terminate the names. Simply equivalence a host variable to the STRING external datatype, as follows:

... 
char  emp_name[11]; 
EXEC SQL VAR emp_name IS STRING(11); 

The length of the ENAME column in the EMP table is 10 characters, so you allot the new emp_name 11 characters to accommodate the null terminator. When you SELECT a value from the ENAME column into emp_name, the program interface null-terminates the value for you.

You can use any external datatypes except NUMBER (for example, VARNUM).

User-Defined Type Equivalencing

You can also map (or make equivalent) user-defined datatypes to Oracle external datatypes. First, define a new datatype structured like the external datatype that suits your needs. Then, map your new datatype to the external datatype using the TYPE statement.

With the TYPE statement, you can assign an Oracle external datatype to a whole class of host variables. The syntax you use is:

EXEC SQL TYPE user_type IS type_name [ (length) ] [REFERENCE]; 

Suppose you need a variable-length string datatype to hold graphics characters. First, declare a struct with a short length component followed by a 65533-byte data component. Second, use typedef to define a new datatype based on the struct. Then, equivalence your new user-defined datatype to the VARRAW external datatype, as shown in the following example:

struct  screen 
{ 
    short  len; 
    char   buff[4000]; 
}; 
typedef struct screen graphics; 

EXEC SQL TYPE graphics IS VARRAW(4000); 
graphics  crt;  — host variable of type graphics 
    ... 

You specify a length of 4000 bytes for the new graphics type because that is the maximum length of the data component in your struct. The precompiler allows for the len component (and any padding) when it sends the length to the Oracle server.

typedef unsigned char *my_raw; 
 
EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE; 
my_raw    graphics_buffer; 
... 
graphics_buffer = (my_raw) malloc(4004); 

CHARF External Datatype

CHARF is a fixed-length character string. You can use this datatype in VAR and TYPE statements to equivalence C datatypes to the fixed-length SQL standard datatype CHAR, regardless of the setting of the DBMS or CHAR_MAP option.

When DBMS=V7 or V8, specifying the external datatype CHARACTER in a VAR or TYPE statement equivalences the C datatype to the fixed-length datatype CHAR (datatype code 96). However, when CHAR_MAP=VARCHAR2, the C datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1).

Now, you can always equivalence C datatypes to the fixed-length SQL standard type CHARACTER by using the CHARF datatype in the VAR or TYPE statement. When you use CHARF, the equivalence is always made to the fixed-length character type, regardless of the setting of the DBMS or CHAR_MAP option.

The EXEC SQL VAR and TYPE Directives

You can code an EXEC SQL VAR ... or EXEC SQL TYPE ... statement anywhere in your program. These statements are treated as executable statements that change the datatype of any variable affected by them from the point that the TYPE or VAR statement was made to the end of the scope of the variable. If you precompile with MODE=ANSI, you must use Declare Sections. In this case, the TYPE or VAR statement must be in a Declare Section.

Example: Datatype Equivalencing (sample4.pc):

The demonstration program in this section shows you how you can use datatype equivalencing in your Pro*C/C++ programs. This program is available as sample4.pc in the demo directory.It demonstrates the use of type equivalencing using the LONG VARRAW external datatype. In order to provide a useful example that is portable across different systems, the program inserts binary files into and retrieves them from the database.

This program uses LOB embedded SQL statements. See also Chapter 16, "LOBs".

Please read the introductory comments for an explanation of the program's purpose.

/***************************************************************
sample4.pc
This program demonstrates the use of type equivalencing using the
LONG VARRAW external datatype. In order to provide a useful example
that is portable across different systems, the program inserts
binary files into and retrieves them from the database.  For
example, suppose you have a file called 'hello' in the current
directory.  You can create this file by compiling the following
source code:

#include <stdio.h>

int main()
{
  printf("Hello World!\n");
}

When this program is run, we get:

$hello
Hello World!

Here is some sample output from a run of sample4:

$sample4
Connected.
Do you want to create (or re-create) the EXECUTABLES table (y/n)? y
EXECUTABLES table successfully dropped.  Now creating new table...
EXECUTABLES table created.

Sample 4 Menu.  Would you like to:
(I)nsert a new executable into the database
(R)etrieve an executable from the database
(L)ist the executables stored in the database
(D)elete an executable from the database
(Q)uit the program

Enter i, r, l, or q: l

Executables           Length (bytes)
--------------------  --------------

Total Executables: 0

Sample 4 Menu.  Would you like to:
(I)nsert a new executable into the database
(R)etrieve an executable from the database
(L)ist the executables stored in the database
(D)elete an executable from the database
(Q)uit the program

Enter i, r, l, or q: i
Enter the key under which you will insert this executable: hello
Enter the filename to insert under key 'hello'.
If the file is not in the current directory, enter the full
path: hello
Inserting file 'hello' under key 'hello'...
Inserted.

Sample 4 Menu.  Would you like to:
(I)nsert a new executable into the database
(R)etrieve an executable from the database
(L)ist the executables stored in the database
(D)elete an executable from the database
(Q)uit the program

Enter i, r, l, or q: l

Executables           Length (bytes)
--------------------  --------------
hello                           5508

Total Executables: 1

Sample 4 Menu.  Would you like to:
(I)nsert a new executable into the database
(R)etrieve an executable from the database
(L)ist the executables stored in the database
(D)elete an executable from the database
(Q)uit the program

Enter i, r, l, or q: r
Enter the key for the executable you wish to retrieve: hello
Enter the file to write the executable stored under key hello into.  If you
don't want the file in the current directory, enter the
full path: h1
Retrieving executable stored under key 'hello' to file 'h1'...
Retrieved.

Sample 4 Menu.  Would you like to:
(I)nsert a new executable into the database
(R)etrieve an executable from the database
(L)ist the executables stored in the database
(D)elete an executable from the database
(Q)uit the program

Enter i, r, l, or q: q

We now have the binary file 'h1' created, and we can run it:

$h1
Hello World!
***************************************************************/

#include <oci.h>
#include <string.h>
#include <stdio.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlcpr.h>

/* Oracle error code for 'table or view does not exist'. */
#define NON_EXISTENT  -942
#define NOT_FOUND     1403

/* This is the definition of the long varraw structure.
 * Note that the first field, len, is a long instead
 * of a short.  This is becuase the first 4
 * bytes contain the length, not the first 2 bytes.
 */
typedef struct long_varraw {
  ub4  len;
  text buf[1];
} long_varraw;


/* Type Equivalence long_varraw to LONG VARRAW.
 * All variables of type long_varraw from this point
 * on in the file will have external type 95 (LONG VARRAW)
 * associated with them.
 */
EXEC SQL TYPE long_varraw IS LONG VARRAW REFERENCE;


/* This program's functions declared. */
#if defined(__STDC__)
  void do_connect(void);
  void create_table(void);
  void sql_error(char *);
  void list_executables(void);
  void print_menu(void);
  void do_insert(varchar *, char *);
  void do_retrieve(varchar *, char *);
  void do_delete(varchar *);
  ub4  read_file(char *, OCIBlobLocator *);
  void write_file(char *, OCIBlobLocator *);
#else
  void do_connect(/*_ void _*/);
  void create_table(/*_ void _*/);
  void sql_error(/*_ char * _*/);
  void list_executables(/*_ void _*/);
  void print_menu(/*_ void _*/);
  void do_insert(/*_ varchar *, char * _*/);
  void do_retrieve(/*_ varchar *, char * _*/);
  void do_delete(/*_ varchar * _*/);
  ub4  read_file(/*_ char *, OCIBlobLocator * _*/);
  void write_file(/*_ char *, OCIBlobLocator * _*/);
#endif

void main()
{
  char reply[20], filename[100];
  varchar key[20];
  short ok = 1;

  /* Connect to the database. */
  do_connect();

  printf("Do you want to create (or re-create) the EXECUTABLES table (y/n)? ");
  gets(reply);

  if ((reply[0] == 'y') || (reply[0] == 'Y'))
    create_table();

  /* Print the menu, and read in the user's selection. */
  print_menu();
  gets(reply);

  while (ok)
  {
    switch(reply[0]) {
    case 'I': case 'i':
      /* User selected insert - get the key and file name. */
      printf("Enter the key under which you will insert this executable: ");
      key.len = strlen(gets((char *)key.arr));
      printf("Enter the filename to insert under key '%.*s'.\n",
             key.len, key.arr);
      printf("If the file is not in the current directory, enter the full\n");
      printf("path: ");
      gets(filename);
      do_insert((varchar *)&key, filename);
      break;
    case 'R': case 'r':
      /* User selected retrieve - get the key and file name. */
      printf("Enter the key for the executable you wish to retrieve: ");
      key.len = strlen(gets((char *)key.arr));
      printf("Enter the file to write the executable stored under key ");
      printf("%.*s into.  If you\n", key.len, key.arr);
      printf("don't want the file in the current directory, enter the\n");
      printf("full path: ");
      gets(filename);
      do_retrieve((varchar *)&key, filename);
      break;
    case 'L': case 'l':
      /* User selected list - just call the list routine. */
      list_executables();
      break;
    case 'D': case 'd':
      /* User selected delete - get the key for the executable to delete. */
      printf("Enter the key for the executable you wish to delete: ");
      key.len = strlen(gets((char *)key.arr));
      do_delete((varchar *)&key);
      break;
    case 'Q': case 'q':
      /* User selected quit - just end the loop. */
      ok = 0;
      break;
    default:
      /* Invalid selection. */
      printf("Invalid selection.\n");
      break;
    }

    if (ok)
    {
      /* Print the menu again. */
      print_menu();
      gets(reply);
    }
  }

  EXEC SQL COMMIT WORK RELEASE;
}


/* Connect to the database. */
void do_connect()
{
  /* Note this declaration: uid is a char * pointer, so Oracle
     will do a strlen() on it at runtime to determine the length.
   */
  char *uid = "scott/tiger";

  EXEC SQL WHENEVER SQLERROR DO sql_error("do_connect():CONNECT");
  EXEC SQL CONNECT :uid;

  printf("Connected.\n");
}


/* Creates the executables table. */
void create_table()
{
  /* We are going to check for errors ourselves for this statement. */
  EXEC SQL WHENEVER SQLERROR CONTINUE;

  EXEC SQL DROP TABLE EXECUTABLES;
  if (sqlca.sqlcode == 0)
    {
      printf("EXECUTABLES table successfully dropped.  ");
      printf("Now creating new table...\n");
    }
  else if (sqlca.sqlcode == NON_EXISTENT)
    {
      printf("EXECUTABLES table does not exist.  ");
      printf("Now creating new table...\n");
    }
  else
    sql_error("create_table()"); 

  /* Reset error handler. */
  EXEC SQL WHENEVER SQLERROR DO sql_error("create_table():CREATE TABLE");

  EXEC SQL CREATE TABLE EXECUTABLES
    ( name VARCHAR2(30), length NUMBER(10), binary BLOB ) ;

  printf("EXECUTABLES table created.\n");
}

/* Opens the binary file identified by 'filename' for reading, and writes
   it into into a Binary LOB.  Returns the actual length of the file read.
 */
ub4 read_file(filename, blob)
  char *filename;
  OCIBlobLocator *blob;
{
  long_varraw *lvr;
  ub4      bufsize;
  ub4      amt;
  ub4      filelen, remainder, nbytes;
  ub4      offset = 1;
  boolean  last = FALSE;
  FILE    *in_fd;

  /* Open the file for reading. */
  in_fd = fopen(filename, "r");
  if (in_fd == (FILE *)0)
    return (ub4)0;

  /* Determine Total File Length - Total Amount to Write to BLOB */
  (void) fseek(in_fd, 0L, SEEK_END);
  amt = filelen = (ub4)ftell(in_fd);

  /* Determine the Buffer Size and Allocate the LONG VARRAW Object */
  bufsize = 2048;
  lvr = (long_varraw *)malloc(sizeof(ub4) + bufsize);

  nbytes = (filelen > bufsize) ? bufsize : filelen;
      
  /* Reset the File Pointer and Perform the Initial Read */
  (void) fseek(in_fd, 0L, SEEK_SET);
  lvr->len = fread((void *)lvr->buf, (size_t)1, (size_t)nbytes, in_fd);
  remainder = filelen - nbytes;

  EXEC SQL WHENEVER SQLERROR DO sql_error("read_file():WRITE");

  if (remainder == 0)
    {
      /* Write the BLOB in a Single Piece */
      EXEC SQL LOB WRITE ONE :amt
         FROM :lvr WITH LENGTH :nbytes INTO :blob AT :offset;
    }
  else
    {
      /* Write the BLOB in Multiple Pieces using Standard Polling */
      EXEC SQL LOB WRITE FIRST :amt
         FROM :lvr WITH LENGTH :nbytes INTO :blob AT :offset;

      do {

        if (remainder > bufsize)
          nbytes = bufsize;
        else
          {
            nbytes = remainder;
            last = TRUE;
          }

        if ((lvr->len = fread(
              (void *)lvr->buf, (size_t)1, (size_t)nbytes, in_fd)) != nbytes)
          last = TRUE;

        if (last)
          {
            /* Write the Final Piece */
            EXEC SQL LOB WRITE LAST :amt
               FROM :lvr WITH LENGTH :nbytes INTO :blob;
          }
        else
          {
            /* Write an Interim Piece - Still More to Write */
            EXEC SQL LOB WRITE NEXT :amt
               FROM :lvr WITH LENGTH :nbytes INTO :blob;  
          }

        remainder -= nbytes;

      } while (!last && !feof(in_fd));
    }

  /* Close the file, and return the total file size. */
  fclose(in_fd);
  free(lvr);
  return filelen;
}


/* Generic error handler.  The 'routine' parameter should contain the name
   of the routine executing when the error occured.  This would be specified
   in the 'EXEC SQL WHENEVER SQLERROR DO sql_error()' statement.
 */
void sql_error(routine)
  char *routine;
{
  char message_buffer[512];
  size_t buffer_size;
  size_t message_length;

  /* Turn off the call to sql_error() to avoid a possible infinite loop */
  EXEC SQL WHENEVER SQLERROR CONTINUE;

  printf("\nOracle error while executing %s!\n", routine);

  /* Use sqlglm() to get the full text of the error message. */
  buffer_size = sizeof(message_buffer);
  sqlglm(message_buffer, &buffer_size, &message_length);
  printf("%.*s\n", message_length, message_buffer);

  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}


/* Opens the binary file identified by 'filename' for writing, and copies
   the contents of the Binary LOB into it.
 */
void write_file(filename, blob)
  char *filename;
  OCIBlobLocator *blob;
{
  FILE        *out_fd;       /* File descriptor for the output file */
  ub4          amt;
  ub4          bufsize;
  long_varraw *lvr;

  /* Determine the Buffer Size and Allocate the LONG VARRAW Object */
  bufsize = 2048;
  lvr = (long_varraw *)malloc(sizeof(ub4) + bufsize);

  /* Open the output file for Writing */
  out_fd = fopen(filename, "w");
  if (out_fd == (FILE *)0)
    return;

  amt = 0;             /* Initialize for Standard Polling (Possibly) */
  lvr->len = bufsize;                       /* Set the Buffer Length */

  EXEC SQL WHENEVER SQLERROR DO sql_error("write_file():READ");

  /* READ the BLOB using a Standard Polling Loop */
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      EXEC SQL LOB READ :amt FROM :blob INTO :lvr WITH LENGTH :bufsize;
      (void) fwrite((void *)lvr->buf, (size_t)1, (size_t)lvr->len, out_fd);
    }
  
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  /* Write the Final Piece (or First and Only Piece if not Polling) */
  (void) fwrite((void *)lvr->buf, (size_t)lvr->len, (size_t)1, out_fd);

  /* Close the Output File and Return */
  fclose(out_fd);
  free(lvr);
  return;
}



/* Inserts the binary file identified by file into the
 * executables table identified by key.
 */
void do_insert(key, file)
  varchar *key;
  char *file;
{
  OCIBlobLocator *blob;
  ub4 loblen, fillen;

  EXEC SQL ALLOCATE :blob;

  EXEC SQL WHENEVER SQLERROR DO sql_error("do_insert():INSERT/SELECT");

  EXEC SQL SAVEPOINT PREINSERT;
  EXEC SQL INSERT
    INTO executables (name, length, binary) VALUES (:key, 0, empty_blob());

  EXEC SQL SELECT binary INTO :blob 
             FROM executables WHERE name = :key FOR UPDATE;

  printf(
    "Inserting file '%s' under key '%.*s'...\n", file, key->len, key->arr); 

  fillen = read_file(file, blob);
  EXEC SQL LOB DESCRIBE :blob GET LENGTH INTO :loblen;

  if ((fillen == 0) || (fillen != loblen))
    {
      printf("Problem reading file '%s'\n", file);
      EXEC SQL ROLLBACK TO SAVEPOINT PREINSERT;
      EXEC SQL FREE :blob;
      return;
    }

  EXEC SQL WHENEVER SQLERROR DO sql_error("do_insert():UPDATE");
  EXEC SQL UPDATE executables
    SET length = :loblen, binary = :blob WHERE name = :key;

  EXEC SQL COMMIT WORK;

  EXEC SQL FREE :blob;
  EXEC SQL COMMIT;
  printf("Inserted.\n");
}


/* Retrieves the executable identified by key into file */
void do_retrieve(key, file)
  varchar *key;
  char *file;
{
  OCIBlobLocator *blob;

  printf("Retrieving executable stored under key '%.*s' to file '%s'...\n",
         key->len, key->arr, file);

  EXEC SQL ALLOCATE :blob;

  EXEC SQL WHENEVER NOT FOUND continue;
  EXEC SQL SELECT binary INTO :blob FROM executables WHERE name = :key;

  if (sqlca.sqlcode == NOT_FOUND)
    printf("Key '%.*s' not found!\n", key->len, key->arr);
  else 
    {
      write_file(file, blob);
      printf("Retrieved.\n");
    }
 
  EXEC SQL FREE :blob;
}


/* Delete an executable from the database */
void do_delete(key)
  varchar *key;
{
  EXEC SQL WHENEVER SQLERROR DO sql_error("do_delete():DELETE");
  EXEC SQL DELETE FROM executables WHERE name = :key;

  if (sqlca.sqlcode == NOT_FOUND)
    printf("Key '%.*s' not found!\n", key->len, key->arr);
  else
    printf("Deleted.\n");
}


/* List all executables currently stored in the database */
void list_executables()
{
  char key[21];
  ub4  length;

  EXEC SQL WHENEVER SQLERROR DO sql_error("list_executables");

  EXEC SQL DECLARE key_cursor CURSOR FOR
    SELECT name, length FROM executables;

  EXEC SQL OPEN key_cursor;

  printf("\nExecutables           Length (bytes)\n");
  printf("--------------------  --------------\n");

  EXEC SQL WHENEVER NOT FOUND DO break;
  while (1)
  {
    EXEC SQL FETCH key_cursor INTO :key, :length;
    printf("%s      %10d\n", key, length);
  }

  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL CLOSE key_cursor;

  printf("\nTotal Executables: %d\n", sqlca.sqlerrd[2]);  
}


/* Prints the menu selections. */
void print_menu()
{
  printf("\nSample 4 Menu.  Would you like to:\n");
  printf("(I)nsert a new executable into the database\n");
  printf("(R)etrieve an executable from the database\n");
  printf("(L)ist the executables stored in the database\n");
  printf("(D)elete an executable from the database\n");
  printf("(Q)uit the program\n\n");
  printf("Enter i, r, l, or q: ");
}

How the Pro*C/C++ Preprocessor Works

The Pro*C/C++ preprocessor recognizes most C preprocessor commands, and effectively performs the required macro substitutions, file inclusions, and conditional source text inclusions or exclusions. The Pro*C/C++ preprocessor uses the values obtained from preprocessing, and alters the source output text (the generated .c output file).

An example should clarify this point. Consider the following program fragment:

#include "my_header.h" 
... 
VARCHAR name[VC_LEN];              /* a Pro*C-supplied datatype */ 
char    another_name[VC_LEN];              /* a pure C datatype */
... 

Suppose the file my_header.h in the current directory contains, among other things, the line

#define VC_LEN   20 

The precompiler reads the file my_header.h, and uses the defined value of VC_LEN (20), declares the structure of name as VARCHAR[20].

char is a native type. The precompiler does not substitute 20 in the declaration of another_name[VC_LEN].

This does not matter, since the precompiler does not need to process declarations of C datatypes, even when they are used as host variables. It is left up to the C compiler's preprocessor to actually include the file my_header.h, and perform the substitution of 20 for VC_LEN in the declaration of another_name.

Preprocessor Directives

The preprocessor directives that Pro*C/C++ supports are:

• #define, to create macros for use by the precompiler and the C or C++ compiler

• #include, to read other source files for use by the precompiler

• #if, to precompile and compile source text based on evaluation of a constant expression to 0

• #ifdef, to precompile and compile source text conditionally, depending on the existence of a defined constant

• #ifndef, to exclude source text conditionally

• #endif, to end an #if or #ifdef or #ifndef command

• #else, to select an alternative body of source text to be precompiled and compiled, in case an #if or #ifdef or #ifndef condition is not satisfied

• #elif, to select an alternative body of source text to be precompiled and compiled, depending on the value of a constant or a macro argument

ORA_PROC Macro

Pro*C/C++ predefines a C preprocessor macro called ORA_PROC that you can use to avoid having the precompiler process unnecessary or irrelevant sections of code. Some applications include large header files, which provide information that is unnecessary when precompiling. By conditionally excluding such header files based on the ORA_PROC macro, the precompiler never reads the file.

The following example uses the ORA_PROC macro to exclude the irrelevant.h file:

#ifndef  ORA_PROC
#include <irrelevant.h>
#endif

Because ORA_PROC is defined during precompilation, the irrelevant.h file is never included.

The ORA_PROC macro is available only for C preprocessor directives, such as #ifdef or #ifndef. The EXEC ORACLE conditional statements do not share the same namespaces as the C preprocessor macros. Therefore, the condition in the following example does not use the predefined ORA_PROC macro:

EXEC ORACLE IFNDEF ORA_PROC;
   <section of code to be ignored>
EXEC ORACLE ENDIF;

ORA_PROC, in this case, must be set using either the DEFINE option or an EXEC ORACLE DEFINE statement for this conditional code fragment to work properly.

Location of Header File Specification

The Pro*C/C++ Precompiler for each system assumes a standard location for header files to be read by the preprocessor, such as sqlca.h, oraca.h, and sqlda.h. For example, on most UNIX systems, the standard location is $ORACLE_HOME/precomp/public. For the default location on your system, see your system-specific Oracle documentation. If header files that you need to include are not in the default location, you must use the INCLUDE= option, on the command line or as an EXEC ORACLE option.

To specify the location of system header files, such as stdio.h or iostream.h, where the location might be different from that hard-coded into Pro*C/C++ use the SYS_INCLUDE precompiler option.

Some Preprocessor Examples

You can use the #define command to create named constants, and use them in place of "magic numbers" in your source code. You can use #defined constants for declarations that the precompiler requires, such as VARCHAR[const]. For example, instead of code with bugs, such as:

... 
VARCHAR  emp_name[10]; 
VARCHAR  dept_loc[14]; 
... 
... 
/* much later in the code ... */ 
f42() 
{ 
    /* did you remember the correct size? */
    VARCHAR new_dept_loc[10]; 
   ... 
} 

you can code:

#define ENAME_LEN     10 
#define LOCATION_LEN  14 
VARCHAR  new_emp_name[ENAME_LEN]; 
   ... 
/* much later in the code ... */ 
f42() 
{ 
    VARCHAR new_dept_loc[LOCATION_LEN]; 
   ... 
} 

You can use preprocessor macros with arguments for objects that the precompiler must process, just as you can for C objects. For example:

#define ENAME_LEN    10 
#define LOCATION_LEN 14 
#define MAX(A,B)  ((A) > (B) ? (A) : (B)) 
 
   ... 
f43() 
{ 
    /* need to declare a temporary variable to hold either an 
       employee name or a department location */ 
    VARCHAR  name_loc_temp[MAX(ENAME_LEN, LOCATION_LEN)]; 
   ... 
} 

You can use the #include, #ifdef and #endif preprocessor directives to conditionally include a file that the precompiler requires. For example:

#ifdef ORACLE_MODE 
#include <sqlca.h> 
#else 
    long SQLCODE; 
#endif 

#define RESEARCH_DEPT   40 
... 
EXEC SQL SELECT empno, sal 
    INTO :emp_number, :salary /* host arrays */
    FROM emp 
    WHERE deptno = RESEARCH_DEPT;  /* INVALID! */

#define STR_LEN      40
...
typedef char asciiz[STR_LEN];
...
EXEC SQL TYPE asciiz IS STRING(STR_LEN) REFERENCE;
...
EXEC SQL VAR password IS STRING(STR_LEN);

#define MAKE_COL_NAME(A)    col ## A 
... 
EXEC SQL SELECT MAKE_COL_NAME(1), MAKE_COL_NAME(2) 
    INTO :x, :y 
    FROM table1; 

SQL Statements Not Allowed in #include

Because of the way the Pro*C/C++ preprocessor handles the #include directive, as described in the previous section, you cannot use the #include directive to include files that contain embedded SQL statements. You use #include to include files that contain purely declarative statements and directives; for example, #defines, and declarations of variables and structures required by the precompiler, such as in sqlca.h.

Include the SQLCA, ORACA, and SQLDA

You can include the sqlca.h, oraca.h, and sqlda.h declaration header files in your Pro*C/C++ program using either the C/C++ preprocessor #include command, or the precompiler EXEC SQL INCLUDE command. For example, you use the following statement to include the SQL Communications Area structure (SQLCA) in your program with the EXEC SQL option:

EXEC SQL INCLUDE sqlca; 

To include the SQLCA using the C/C++ preprocessor directive, add the following code:

#include <sqlca.h> 

When you use the preprocessor #include directive, you must specify the file extension (such as .h).

      #ifndef SQLCA
      #define SQLCA 1

When you precompile a file that contains a #include directive or an EXEC SQL INCLUDE statement, you have to tell the precompiler the location of all files to be included. You can use the INCLUDE= option, either in the command line, or in the system configuration file, or in the user configuration file.

The default location for standard preprocessor header files, such as sqlca.h, oraca.h, and sqlda.h, is preset in the precompiler. The location varies from system to system. See your system-specific Oracle documentation for the default location on your system.

When you compile the .c output file that Pro*C/C++ generates, you must use the option provided by your compiler and operating system to identify the location of included files.

For example, on most UNIX systems, you can compile the generated C source file using the command

cc -o progname -I$ORACLE_HOME/sqllib/public ... filename.c ... 

On VAX/OPENVMS systems, you pre-pend the include directory path to the value in the logical VAXC$INCLUDE.

EXEC SQL INCLUDE and #include Summary

When you use an EXEC SQL INCLUDE statement in your program, the precompiler includes the source text in the output (.c) file. Therefore, you can have declarative and executable embedded SQL statements in a file that is included using EXEC SQL INCLUDE.

When you include a file using #include, the precompiler merely reads the file, and keeps track of #defined macros.

Defined Macros

If you define macros on the C compiler's command line, you might also have to define these macros on the precompiler command line, depending on the requirements of your application. For example, if you compile with a UNIX command line such as

cc -DDEBUG ...

you should precompile using the DEFINE= option, namely

proc DEFINE=DEBUG ...

Include Files

The location of all included files that need to be precompiled must be specified on the command line, or in a configuration file.

For example, if you are developing under UNIX, and your application includes files in the directory /home/project42/include, you must specify this directory both on the Pro*C/C++ command line and on the cc command line. You use commands like these:

proc iname=my_app.pc include=/home/project42/include ...
cc -I/home/project42/include ... my_app.c

or you include the appropriate macros in a makefile. For complete information about compiling and linking your Pro*C/C++ application, see your system-specific Oracle documentation.

Precompiled Header File Creation

Assume that you have a header file called top.h.Then you can precompile it, specifying that HEADER=hdr:

proc HEADER=hdr INAME=top.h

Pro*C/C++ precompiles the given input file, top.h, and generates a new precompiled header file, top.hdr, in the same directory. The output file, top.hdr, can be moved to a directory that the #include statement will cause to be searched.

Use of the Precompiled Header Files

Use the same value of the HEADER option with an application file that is to be precompiled. If simple.pc contains:

#include <top.h>
...

and top.h contains:

#include <a.h>
#include <b.h>
#include <c.h>
...

then precompile this way:

proc HEADER=hdr INAME=simple.pc

When Pro*C/C++ reads the #include top.h statement, it will search for a corresponding 'top.hdr' file and instantiate the data from that file instead of precompiling 'top.h' again.

Examples

This section includes examples demonstrating several different cases.

#include <top.h>
#include <top.h>
main(){}

#include <b.h>

#include <a.h>
#include <b.h>
main(){}

#include <a.h>
#include <c.h>

#include <b.h>
#include <c.h>

#include <topA.h>
#include <topB.h>
main(){}

List of Header Files

The ORACLE_BASE\ORACLE_HOME\precomp\public directory contains the Pro*C/C++ header files. Table 5-3 lists and describes the header files.

Effects of Options

The following precompiler options are used with the precompilation of the application.

A> proc HEADER=hdrA DEFINE=<A macros> INCLUDE=<A dirs> common.h

B> proc HEADER=hdrB DEFINE=<B macros> INCLUDE=<B dirs> common.h

proc HEADER=hdr PARSE=PARTIAL file.h

proc HEADER=hdr PARSE=FULL program.pc

#define LENGTH 10
typedef int myint;

#include <file.h>
main()
{
     VARCHAR ename[LENGTH];
     myint empno = ...;
     EXEC SQL SELECT ename INTO :ename WHERE JOB = :empno;
}

Usage Notes

The file format of the generated output file of a precompiled header is not guaranteed to remain fixed from one release to the next. Pro*C/C++ has no way of determining which version of the precompiler was used to generate the precompiled header file output.

Because of this, it is strongly recommended that, in order to avoid the possibility of errors or other strange behavior during a precompilation that uses precompiled header files, those files be regenerated by re-precompiling the corresponding header files when upgrading to newer releases of Pro*C/C++.

The generated output from the precompilation of a header file is completely non-portable. This means that you cannot transfer the output file from the precompilation of a header file from one platform to another and use that file during the subsequent precompilation of another header file or Pro*C/C++ program.

Symbol Definition

You can define a symbol in two ways. Either include the statement:

EXEC ORACLE DEFINE symbol;

in your host program or define the symbol on the command line using the syntax

... INAME=filename ... DEFINE=symbol 

where symbol is not case-sensitive.

Some port-specific symbols are predefined for you when the Pro*C/C++ precompiler is installed on your system.

An Oracle Preprocessor Example

In the following example, the SELECT statement is precompiled only when the symbol site2 is defined:

EXEC ORACLE IFDEF site2; 
    EXEC SQL SELECT DNAME 
        INTO :dept_name 
        FROM DEPT 
        WHERE DEPTNO = :dept_number; 
EXEC ORACLE ENDIF; 

Blocks of conditions can be nested as shown in the following example:

EXEC ORACLE IFDEF outer; 
    EXEC ORACLE IFDEF inner; 
    ... 
    EXEC ORACLE ENDIF; 
EXEC ORACLE ENDIF;

You can "Comment out" C or embedded SQL code by placing it between IFDEF and ENDIF and not defining the symbol.

Numeric Constants in Pro*C/C++

In Pro*C/C++, normal C scoping rules are used to find and locate the declaration of a numeric constant declaration.

     const int g = 30;     /* Global declaration to both function_1()
                                                  and function_2() */
     void function_1()
     {
       const int a = 10;  /* Local declaration only to function_1() */
       char x[a];
       exec sql select ename into :x from emp where job = 'PRESIDENT';
     }

     void function_2()
     {
       const int a = 20;  /* Local declaration only to function_2() */
       VARCHAR v[a];
       exec sql select ename into :v from emp where job = 'PRESIDENT';
     }

     void main()
     {
       char m[g];                                   /* The global g */
       exec sql select ename into :m from emp where job = 'PRESIDENT';
     }

Numeric Constant Rules and Examples

Variables which are of specific static types need to be defined with static and initialized. The following rules must be kept in mind when declaring numeric constants in Pro*C/C++:

• The const qualifier must be used when declaring the constant

• An initializer must be used to initialize the value of the constant. This initializer must be precompile-time evaluable.

Any attempt to use an identifier that does not resolve to a constant declaration with a valid initializer is considered an error.

The following shows examples of what is not permitted and why:

int a;
int b = 10;
volatile c;
volatile d = 10;
const e;
const f = b;

VARCHAR v1[a]; /* No const qualifier, missing initializer */
VARCHAR v2[b];                      /* No const qualifier */
VARCHAR v3[c];     /* Not a constant, missing initializer */
VARCHAR v4[d];                          /* Not a constant */
VARCHAR v5[e];                     /* Missing initializer */
VARCHAR v6[f];   /* Bad initializer.. b is not a constant */

Runtime Context in the OCI Release 8 Environment

An EXEC SQL CONTEXT USE statement specifies a runtime context to be used in a Pro*C/C++ program. This context applies to all executable SQL statements that positionally follow it in a given Pro*C/C++ file until another EXEC SQL CONTEXT USE statement occurs. If no EXEC SQL CONTEXT USE appears in a source file, the default "global" context is assumed. Thus, the current runtime context, and therefore the current OCI environment handle, is known at any point in the program.

The runtime context and its associated OCI environment handle are initialized when a database logon is performed using EXEC SQL CONNECT in Pro*C/C++.

When a Pro*C/C++ runtime context is freed using the EXEC SQL CONTEXT FREE statement, the associated OCI environment handle is terminated and all of its resources, such as space allocated for the various OCI handles and LOB locators, are de-allocated. This command releases all other memory associated with the Pro*C/C++ runtime context. An OCI environment handle that is established for the default "global" runtime remains allocated until the Pro*C/C++ program terminates.

Parameters in the OCI Release 8 Environment Handle

An OCI environment established through Pro*C/C++ will use the following parameters:

• The callback functions used by the environment for allocating memory, freeing memory, writing to a text file, and flushing the output buffer will be trivial functions that call malloc(), free(), fprintf(stderr, ...), and fflush(stderr) respectively.

• The language will be obtained from the Globalization Support variable NLS_LANG.

• The error message buffer will be allocated in thread-specific storage.

SQLEnvGet()

The SQLLIB library function SQLEnvGet() (SQLLIB OCI Environment Get) returns the pointer to the OCI environment handle associated with a given SQLLIB runtime context. The prototype for this function is:

sword SQLEnvGet(dvoid *rctx, OCIEnv **oeh);

where:

SQLSvcCtxGet()

The SQLLIB library function SQLSvcCtxGet() (SQLLIB OCI Service Context Get) returns the OCI service context for the Pro*C/C++ database connection. The OCI service context can then be used in direct calls to OCI functions. The prototype for this function is:

sword SQLSvcCtxGet(dvoid *rctx, text *dbname,
       sb4 dbnamelen, OCISvcCtx **svc);

where:

Embedded OCI Release 8 Calls

To embed OCI release 8 calls in your Pro*C/C++ program:

1. Include the public header sql2oci.h

2. Declare an environment handle (type OCIEnv *) in your Pro*C/C++ program:

OCIEnv *oeh;

3. Optionally, declare a service context handle (type OCISvcCtx *) in your Pro*C/C++ program if the OCI function you wish to call requires the Service Context handle.

OCISvcCtx *svc;

4. Declare an error handle (type OCIError *) in your Pro*C/C++ program:

OCIError *err;

5. Connect to Oracle using the embedded SQL statement CONNECT. Do not connect using OCI.

EXEC SQL CONNECT ...

6. Obtain the OCI Environment handle that is associated with the desired runtime context using the SQLEnvGet function.

For single threaded applications:

retcode = SQLEnvGet(SQL_SINGLE_RCTX, &oeh);

or for shared server applications:

sql_context ctx1;
...
EXEC SQL CONTEXT ALLOCATE :ctx1;
EXEC SQL CONTEXT USE :ctx1;
...
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
...
retcode = SQLEnvGet(ctx1, &oeh);

7. Allocate an OCI error handle using the retrieved environment handle:

retcode = OCIHandleAlloc((dvoid *)oeh, (dvoid **)&err, 
                    (ub4)OCI_HTYPE_ERROR, (ub4)0, (dvoid **)0);

8. Optionally, if needed by the OCI call you use, obtain the OCIServiceContext handle using the SQLSvcCtxGet call:

For single threaded applications:

retcode = SQLSvcCtxGet(SQL_SINGLE_RCTX, (text *)dbname, (ub4)dbnlen, &svc);

or, for shared server environment applications:

sql_context ctx1;
...
EXEC SQL ALLOCATE :ctx1;
EXEC SQL CONTEXT USE :ctx1;
...
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd AT :dbname
     USING :hst;
...
retcode = SQLSvcCtxGet(ctx1, (text *)dbname, (ub4)strlen(dbname), &svc);

Set Up the LDA

You set up the LDA by issuing the OCI call

sqllda(&lda); 

where lda identifies the LDA data structure.

If the setup fails, the lda_rc field in the lda is set to 1012 to indicate the error.

Remote and Multiple Connections

A call to sqllda() sets up an LDA for the connection used by the most recently executed SQL statement. To set up the different LDAs needed for additional connections, you must call sqllda() with a different LDA immediately after each CONNECT. In the following example, you connect to two nondefault databases concurrently:

#include <ocidfn.h>
Lda_Def lda1;
Lda_Def lda2;

char username[10], password[10], db_string1[20], dbstring2[20];
...
strcpy(username, "scott");
strcpy(password, "tiger");
strcpy(db_string1, "NYNON");
strcpy(db_string2, "CHINON");
/* give each database connection a unique name */
EXEC SQL DECLARE DB_NAME1 DATABASE;
EXEC SQL DECLARE DB_NAME2 DATABASE;
/* connect to first nondefault database */
EXEC SQL CONNECT :username IDENTIFIED BY :password;
    AT DB_NAME1 USING :db_string1;
/* set up first LDA */
sqllda(&lda1);
/* connect to second nondefault database */
EXEC SQL CONNECT :username IDENTIFIED BY :password;
    AT DB_NAME2 USING :db_string2;
/* set up second LDA */
sqllda(&lda2);

DB_NAME1 and DB_NAME2 are not C variables; they are SQL identifiers. You use them only to name the default databases at the two nondefault nodes, so that later SQL statements can refer to the databases by name.

Oracle-Specific Issues

You can use the precompiler to develop applications that comply with the X/Open standards. However, you must meet the following requirements.