Converting Oracle Pro*C Programs to DB2 Universal Database

Updates in products often mean changes to your processes. This article explains the steps necessary during Oracle Pro*C program conversion to C with embedded DB2 SQL calls.

Share:

Marina Greenstein, Certified Consulting I/T Specialist - Data Management, IBM White Plains

Marina Greenstein is a Certified Technical Consultant with the DB2 Migration Team. She joined IBM at 1995 and is currently responsible for helping customers migrate from competitive DBMS to DB2 UDB. She presented migration methodology and various database migration topics at numerous DB2 Technical conferences and at SHARE.



11 September 2003

Introduction

While many aspects of IBM® DB2® Universal DatabaseTM application development have undergone changes in recent years (stored procedures from C/COBOL/JavaTM to SQL Procedure Language, support for SQL PL in User-Defined Functions, triggers and in-line SQL, enriched set of built-in functions, etc.), support for embedding SQL into other host languages (C/C++) has remained virtually unchanged. This has resulted in many difficulties during conversions of Oracle Pro*C programs to DB2 UDB and during migration of applications from Oracle to DB2 UDB.

This article explains the steps necessary during Pro*C program conversion to C with embedded DB2 SQL calls.

Note: Examples included in this article are excerpts from actual programs and cannot be compiled by themselves.


Connecting to the database

There are differences in how the C program can connect to the database. In Oracle each instance (service name) can manage only one database. DB2 instances can be used to manage multiple databases; thus, the database name should be explicitly provided by the connection statement. In order to connect to the Oracle database you need to specify the Oracle user and password for that user:

EXEC SQL CONNECT :user_name IDENTIFIED BY :password;

In DB2, you need to specify the database name, user ID and password for that user ID. So the above statement will be converted as:

EXEC SQL CONNECT TO :dbname USERID :userid PASSWORD : password;

Please note that dbname, userid and password need to be declared as host variables.


Host variable declaration

Host variables are C or C++ language variables that are referenced within SQL statements. They allow an application to pass input data to and receive output data from the database manager. After the application is precompiled, host variables are used by the compiler as any other C/C++ variable is.

Host variables should not only be compatible with DB2 data types (accepted by DB2 pre-compiler) but also acceptable for the programming language compiler.

As C program manipulate with values from the tables using host variable, the first step is to convert Oracle table definition to DB2 data types (see Table 1, below). Please note that this mapping is one to many as it depends on the actual usage of data. For example, Oracle DATE data can be converted to DB2 DATE, if it only stores the actual date, but it needs to be converted to DB2 TIMESTAMP if it stores the DATE and TIME.

The next step is to match DB2 data types with C data types. Table 1 shows mapping between data types:

Table 1. Oracle table definitions to DB2 data types

Oracle data typeDB2 data typeC data type declaration
CHAR(n)CHAR(n)char char_var[n+1];
VARCHAR2(n)VARCHAR(n)struct{short len;
char data[n]} varchar_var;
or
char varchar_var[n+1];
LONGCLOB(2GB)SQL TYPE IS CLOB(n) v-name;
NUMBER(p,s)NUMERIC(p,s)double num_var;
DECIMAL(p,s)DECIMAL(p,s)double dec_var;
!note that passing parameters using
the SQLDA allows an exact representation
of DECIMAL/NUMERIC using packed BCD encoding.
INTEGERINTEGERsqlint32 int_var;
SMALLINTSMALLINTsqlint16 int_var;
BIGINTsqlint64 bigint_var;
RAW(n)CHAR(n) FOR BIT DATAchar ... [n]
LONG RAW BLOB(2GB)SQL TYPE IS BLOB(n) v_name;
DATETIMESTAMPchar tms_var[27];
DATE (only the date)DATE (MM/DD/YYYY)char dt_var[11];
DATE(only the time)TIME (HH24:MI:SS)char tm_var[16];

All host variables in a C program need to be declared in a special declaration section so that the DB2 pre-compiler can identify the host variables and their data types.

      EXEC SQL BEGIN DECLARE SECTION; 
 
             char emp_name[31] = {'\0'}; 
             sqlint32  ret_code = 0;   
 
     EXEC SQL END DECLARE SECTION;

Within this declaration section, there are rules for host variable data types that are different from Oracle pre-compiler rules. Oracle pre-compiler permits host variables to be declared as VARCHAR. VARCHAR[n] is a pseudo-type recognized by the Pro*C pre-compiler. It is used to represent blank-padded, variable-length strings. The Pro*C pre-compiler will convert it into a structure with a 2-byte length field followed by an n-byte character array. DB2 requires usage of standard C constructs. So, the declaration for the variable emp_name VARCHAR[25] needs to be converted as follows:

 struct emp_name 	{ 
       short  var_len; 
       char   var_data[25] 	};

Or, as mentioned above, the use of a "char emp_name[n]" is also permitted for VARCHAR data. Variables of user-defined types (using typedef) in PRO*C need to be converted to the source data type. For example, type theUser_t has been declared to host values from Oracle object type:

    typedef struct  user_s 
      {short int userNum; 
        char userName[25]; 
        char userAddress[40]; 
      } theUser_t;

In Pro*C program, you can have host variables declared as theUser_t:

      EXEC SQL BEGIN DECLARE; 
          theUser_t *myUser;          
     EXEC SQL END DECLARE SECTION;

To use this host variable for DB2, you would need to take this out of the EXEC SQL DECLARE SECTION and define host variable MyUser as a structure.

DB2 allows for the host variable to be declared as a pointer with the following restrictions:

  • If a host variable is declared as a pointer, no other host variable may be declared with that same name within the same source file.
  • The host variable declaration char *ptr is accepted, but it does not mean null-terminated character string of undetermined length. Instead, it means a pointer to a fixed-length, single-character host variable. This may not be what was intended for the Oracle host variable declaration.

We recommend that sqlint32 and sqlint64 be used for INTEGER and BIGINT host variables, respectively. By default, the use of long host variables results in the pre-compiler error SQL0402 on platforms where long is a 64-bit quantity, such as 64-bit UNIX®. Use the PREP option LONGERROR NO to force DB2 to accept long variables as acceptable host variable types and treat them as BIGINT variables.


Oracle host tables

In Pro*C programs, you can declare host variables using arrays, then declare a cursor you want to get results from. Then you can issue a fetch statement that will get all rows from the cursor into that host array.

Here is a fragment of PRO*C that demonstrates this method:

EXEC SQL BEGIN DECLARE SECTION; 
 
long int     dept_numb[10]; 
char         dept_name[10][14]; 
char         v_location[12]; 
 
EXEC SQL END DECLARE SECTION; 
 
/* ...... */  
 
 
EXEC SQL DECLARE CUR1 CURSOR FOR 
     SELECT DEPTNUMB, DEPTNAME 
      FROM org_table 
     WHERE LOCATION = :v_location; 
 
/*....... */ 
 
EXEC SQL FETCH CUR1 INTO :dept_num, :dept_name;

The last statement will get all 10 rows from the cursor into arrays.

As DB2 does not support arrays for the host variable declaration, the above code needs to be converted as follows:

EXEC SQL BEGIN DECLARE SECTION; 
 
sqlint32     h_dept_numb = 0; 
char         h_dept_name[14] = {'\0'}; 
char         v_location[12] = {'\0'}; 
 
EXEC SQL END DECLARE SECTION; 
 
/* move array out of DECLARE section - just C variables */ 
long  int     dept_numb[10]; 
char          dept_name[10][14]; 
short int     i = 0; 
 
 
/* ...... */  
 
 
EXEC SQL DECLARE CUR1 CURSOR FOR 
     SELECT DEPTNUMB, DEPTNAME 
      FROM org_table 
     WHERE LOCATION = :v_location; 
 
/*we need Fetch one row at the time and move to corresponding  
      member of array */ 
 
for (i=0;i<11;i++){ 
    EXEC SQL FETCH CUR1 INTO :h_dept_num, :h_dept_name; 
    if (SQLCODE == 100){ 
        break; 
      } 
    dept_numb[i] = h_dept_numb; 
    strcpy(dept_name[i], h_dept_name); 
}

Exception handling

DB2 mechanisms for trapping errors are quite similar between Oracle and DB2, using the same concept of separating error routines from the mainline logic. There are three different WHENEVER statements that could be used to define program behavior in case of an error in DB2:

EXEC SQL WHENEVER SQLERROR GOTO error_routine; 
EXEC SQL WHENEVER SQLWARNING CONTINUE; 
EXEC SQL WHENEVER NOT FOUND not_found_routine;

Although the WHENEVER statement is prefixed by EXEC SQL like other SQL statements, it is not an executable statement. Instead, a WHENEVER statement causes the precompiler to generate code in a program to check the SQLCODE attribute from the SQLCA after each SQL statement and perform the action specified in the WHENEVER statement. SQLERROR means that an SQL statement returns a non-positive SQLCODE, indicating an error condition. SQLWARNING indicates a non-negative SQLCODE (except +100), while NOT FOUND specifies SQLCODE = +100, indicating that no data rows were found to satisfy a request.

A compilation unit can contain as many WHENEVER statements as necessary, and they can be placed anywhere in the program. The scope of one WHENEVER statement reaches from the placement of the statement in the file onward in the character stream of the file until the next suitable WHENEVER statement is found or end-of-file is reached. No functions or programming blocks are considered in that analysis. For example, you may have two different SELECT statements; one must return at least one row, and the other may not return any. You will need two different WHENEVER statements:

     EXEC SQL WHENEVER NOT FOUND GOTO no_row_error; 
     EXEC SQL SELECT  address  
                        INTO  :address 
                        FROM   test_table 
                       WHERE phone = :pnone_num; 
     ........ 
     EXEC SQL WHENEVER NOT FOUND CONTINUE; 
     EXEC SQL SELECT  commis_rate 
                       INTO :rate :rateind 
                    WHERE prod_id = :prodId; 
      if (rateind == -1 ) rate = 0.15;      
      ......

Oracle precompiler also supports DO and STOP statements as actions in a WHENEVER statement; those are not supported by DB2 precompiler and need to be converted to GOTO.

Another alternative would be to check explicitly the SQLCODE after each EXEC SQL statement because that would allow more context-sensitive error handling.

Error messages and warnings

The SQL Communication Area (SQLCA) data structure in DB2 is similar to that of Oracle. SQLCA provides information for diagnostic checking and event handling.

To get the full text of longer (or nested) error messages, you need the sqlglm() function:

sqlglm(message_buffer, &buffer_size, &message_length);

where message_buffer is the character buffer in which you want Oracle to store the error message; buffer_size specifies the size of message_buffer in bytes; Oracle stores the actual length of the error message in message_length. The maximum length of an Oracle error message is 512 bytes.

DB2 Universal Database provides its user with a special run-time API function to return an error message based on SQLCODE:

rc=sqlaintp(msg_buffer, 1024, 80, sqlca.sqlcode);

where "80" stands for the number of characters after which a line break will be inserted in the message. DB2 will search for work-boundaries to place such a line break. "1024" specifies the length of the message buffer; e.g., char msg_buffer[1024].

As a result of invoking this function, the allocated buffer will contain the descriptive error message, e.g.:

SQL0433N Value "TEST VALUES" is too long. SQLSTATE=22001.

If you need more information about a particular error, DB2 Universal Database provides an API function that returns an extended message associated with the specific SQLSTATE:

rc=sqlogstt(msg_sqlstate_buffer, 1024, 80, sqlca.sqlcode);

As a result of invoking this function, char msg_sqlstate_buffer[1024] will contain the following message:

SQLSTATE 22001: Character data, right truncation occurred; 
for example, an update or insert 
value is a string that is too long for the column, 
or datetime value cannot be assigned to a 
host variable, because it is too small.

Error messages and warnings

The SQL Communication Area (SQLCA) data structure in DB2 is similar to that of Oracle. SQLCA provides information for diagnostic checking and event handling.

To get the full text of longer (or nested) error messages, you need the sqlglm() function:

sqlglm(message_buffer, &buffer_size, &message_length);

where message_buffer is the character buffer in which you want Oracle to store the error message; buffer_size specifies the size of message_buffer in bytes; Oracle stores the actual length of the error message in message_length. The maximum length of an Oracle error message is 512 bytes.

DB2 Universal Database provides its user with a special run-time API function to return an error message based on SQLCODE:

rc=sqlaintp(msg_buffer, 1024, 80, sqlca.sqlcode);

where "80" stands for the number of characters after which a line break will be inserted in the message. DB2 will search for work-boundaries to place such a line break. "1024" specifies the length of the message buffer; e.g., char msg_buffer[1024]. As a result of invoking this function, the allocated buffer will contain the descriptive error message, e.g.:

SQL0433N Value "TEST VALUES" is too long. SQLSTATE=22001.

If you need more information about a particular error, DB2 Universal Database provides an API function that returns an extended message associated with the specific SQLSTATE:

rc=sqlogstt(msg_sqlstate_buffer, 1024, 80, sqlca.sqlcode);

As a result of invoking this function, char msg_sqlstate_buffer[1024] will contain the following message:

SQLSTATE 22001: Character data, right truncation occurred; for example, an  
update or insert value is a string that is too long for the column, or datetime  
value cannot be assigned to a host variable, because it is too small.

Passing data to a stored procedure from a C program

In Oracle, in order to invoke a remote database procedure, the following statements are used:

EXEC SQL EXECUTE 
       BEGIN 
         Package_name.SP_name(:arg_in1,:arg_in2, :status_out); 
       END; 
END-EXEC;

The value transfer between the calling environment and the stored procedure may be achieved through arguments. You can choose one of three modes for each argument: IN, OUT or INOUT. For example, the above stored procedure may be declared as:

CREATE PACKAGE package_name IS 
     PROCEDURE  SP_name( 
             agr_in1 IN NUMBER , 
             arg_in2 IN CHAR(30), 
             status_out OUT NUMBER);

When this stored procedure is invoked, values passed from the calling program will be accepted by the stored procedure correspondingly.

DB2 client application invokes stored procedure by using the CALL statement. The CALL statement can pass parameters to the stored procedure and receive parameters returned from the stored procedure. It has the following syntax:

CALL procedure_name  (:parm1, ...:parmN);

As with all SQL statements you prepare CALL statement with parameters markers and then supply values for the markers using SQLDA:

CALL procedure_name USING DESCRIPTOR host_var;

The SQLDA is very helpful if you have an unknown number of host variables or many variables (100 or more). Managing single variables in those cases can be very troublesome.

In order to invoke a stored procedure from the C client, the following needs to be in place:

  • a stored procedure needs to be created and registered with the database
  • a host variable or parameter marker to each IN and INOUT parameter of the stored procedure should be declared and initialized

Consider an example. The program must give a raise to each employee whose current salary is less than a certain value. The program will pass that value to a stored procedure, perform an update and return back the status. Client code in C will look as follows:

#include <sqlenv.h> 
 
main() 
{ 
     EXEC SQL BEGIN DECLARE SECTION; 
               Sqlint32  salary_val=0; 
               Sqlint16 salind=1; 
               Sqlint16  status=0; 
               Sqlint16  statind=0; 
     EXEC SQL END DECLARE SECTION; 
 
     EXEC SQL INCLUDE SQLCA; 
     EXEC SQL CONNECT TO sample; 
     EXEC SQL WHENEVER SQLERROR GOTO err_routine; 
     
      salary_val = getSalaryForRaise();    
      statind = -1;   /*  set indicator variable to -1 for <i>status</i>  
      	as output-only variable */ 
      
      EXEC SQL CALL  raiseSal(:salary_val :salind, :status :statind); 
       if (status == 0){ 
              printf  (" The raises has been successfully given \n "); 
              EXEC SQL COMMIT; 
              } 
        else  
            if (status ==1) 
                printf  (" NO input  values has been provided.\n "); 
            else 
                if(status == 2) 
                        printf("Stored procedure failed.\n"); 
 
     err_routine: 
          printf  (" SQL Error, SQLCODE =  \n ", SQLCODE); 
          EXEC SQL ROLLBACK;    
} 
</sqlenv.h>

Note that all host variables that are used as parameters in the CALL statement are declared and initialized in EXEC SQL DECLARE SECTION.


Building a C/C++ DB2 application

DB2 provides build scripts for precompiling, compiling and linking C-embedded SQL programs. These are located in the sqllib/samples/c directory, along with sample programs that can be built with these files. This directory also contains the embprep script used within the build script to pre-compile a *.sqc file.

Build files are provided by DB2 for each language on supported platforms where the types of programs they build are available, in the same directory as the sample programs for each language. These build files, unless otherwise indicated, are for supported languages on all supported platforms. The build files have the .bat (batch) extension on Windows®, and have no extension on UNIX platforms. For example, bldmapp.bat is a script to build C/C++ application on Windows.

DB2 also provides utilemb.sqc and utilemb.h files, containing functions for error handling. In order to use utility functions, the utility file must first be compiled, and then its object file linked in during the creation of the target program's executable. Both the makefile and build files in the samples directories do this for the programs that require error-checking utilities.

For more information on building C applications, see IBM DB2 UDB Application Development Guide: Building and Running Applications (ISBN SC09-4825-00).


Summary

This paper discusses how the change a C program to access DB2 Universal Database instead of Oracle database. Even though the principles of embedding Oracle and DB2 UDB database access calls are similar for C/C++ development, specific database differences can present challenges. This article is intended to help developers understand the differences and overcome resulting challenges.

Acknowledgment

Thanks to Knut Stolze for his comments on a draft of this article.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13244
ArticleTitle=Converting Oracle Pro*C Programs to DB2 Universal Database
publish-date=09112003