Blog do projektu Open Source JavaHotel

niedziela, 5 kwietnia 2015

Pro*C to DB2 Embedded SQL migration, connection

Introduction
Embedded SQL is traditional method of database programming but still very robust and effective. All major SQL vendors supports Embedded SQL technology. Unfortunately, although general structure for different Embedded SQL implementation contains a lot of shared elements migration from one vendor to another is not easy. Nevertheless,  "not easy" does not mean impossible, if one recognizes some common pattern then migration is pretty simple and allows keep the main solution logic almost intact.
Pro*C is Embedded SQL implementation for Oracle, just Embedded SQL for DB2. Below I will describe some common problems and solutions found during migrating Oracle C++ Pro*C code to DB2 starting from connecting to and disconnecting from database.
Oracle Compatibility Mode
Starting from version 9.7 DB2 contains Oracle Compatibility Mode (constantly improved and enhanced in subsequent version up to 10.5) making migration of SQL procedure code almost painless. There is also "compatibility mode" for Pro*C. It contains a lot of useful features but most of the problems should be resolved manually.
Sample program
I created a simple program which connects to and disconnects from database the show the first basic problems. The source code is available here.
Compiling, linking, declaration
Implementation for DB2 and Oracle is different. Below are  examples of make files.
Oracle: mk.m
DB2 : mk.m
Additional differences
DB2 requires all host variables (passing data between SQL and C++ code) being declared in a special section. Example:
  EXEC SQL BEGIN DECLARE SECTION;
    char dbAlias[15];
    char user[128 + 1];
    char pswd[15];
  EXEC SQL END DECLARE SECTION;
Also every procedure containing any Embedded SQL code should have a direct struct sql declaration or EXEC SQL INCLUDE SQLCA clause.
void ConnectTo() 
{    
  struct sqlca sqlca;
...
}

Connecting and disconnecting in Oracle
Oracle : Source file
#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

#include "../utillib.h"

static char * connection = "test/test@think:1521/testdb";

void sql_error(char *msg,int *failed) 
{ 
    char err_msg[512];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    *failed = 1;
} 


void ConnectTo() 
{    
    int failed = 0;
    printf("Connection string : %s\n",connection);
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
       printf("Not connected\n");
       exit(EXIT_FAILURE);
    }       

    printf("Connected to ORACLE\n"); 
}

void Disconnect() 
{
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL COMMIT WORK RELEASE;
   printf("\nDisconnected");
}
Migration to DB2
The main problem is hidden in :
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
    ...
    }
In case of any error sql_error routine is called and the routine prints error message and set error variable to 1 just allowing implement a logic for connection failure. This routine can be shared between all SQL invocations and provide common error handling.
Although DB2 Embedded SQL supports declaring custom routine for SQL error handling the routine does not accept any parameter just making direct migration of above code impossible. Passing data using global static variable is very bad programming practice and should be omitted.
The best solution is to use a reusable macro providing common method of error handling.
void SqlInfoPrint(char *appMsg, struct sqlca *pSqlca);

#define EVALRESULT(mess) if (sqlca.sqlcode < 0) { SqlInfoPrint(mess,&sqlca); error = 1; }
    
And DB2 version of Pro*C code above :
EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  EVALRESULT("Database -- connect with userid and password");
  if (error) {
    ...
  }
It requires manual changes but the execution flow is exactly the same. The migrated DB2 code (with implementation of SqlInfoPrint procedure) is here.
Next
Oracle EXEC SQL VAR

Brak komentarzy:

Publikowanie komentarza