3
Cross-Language Interoperability

3.1 Interoperability Between ODBC and OKAPI

An important feature of Oracle Lite is its ability to handle interoperability across different language interfaces. Notably, Oracle Lite enables seamless interoperability of data between SQL and object databases in a single program.

From a data modeling standpoint, this highly integrated interoperability is achieved through the simple concept of object reference. The Oracle Lite OKAPI interface defines the object reference as okRef. Oracle Lite SQL incorporates a new SQL type, POL_REF, which is compatible with the object extensions proposed for SQL-3.

The concept of object reference in Oracle Lite enables an application to freely exchange data between SQL and object databases. For example, an application can create and populate a SQL table, then access the contents of the rows directly as a C or C++ structure. Conversely, an application can issue a SQL query against persistent C or C++ objects in an Oracle Lite object database.

Oracle Lite provides a simple and practical mechanism for an application to operate on the same data through both relational and object interfaces. Oracle Lite does not impose a boundary between the rows in a SQL database and the persistent instances in an object database. This flexibility allows you to use the best interface for each task in an application.

3.2 Datatype Correspondence

The Oracle Lite object kernel handles datatype conversion between object model and relational model datatype references. The following table shows the correspondence between SQL, Java, standard C, and OKAPI datatypes.

3.3 Interoperability Sample Program

The example in this section illustrates the interoperability between ODBC and OKAPI. Specifically, it shows how to map a SQL row into a C structure to allow direct access and modification to a SQL row by a C program.

Besides the setup and cleanup for the ODBC statement, the program creates a SQL table and populates it through calls to ODBC. It retrieves object references by executing a SQL SELECT statement. The program then loops until all the results of the SELECT statement are fetched. During each iteration, the ODBC fetch statement returns a reference to an object in a host variable Ref, which is bound to the ODBC statement by an earlier ODBC call. Using the object reference to a row, the program accesses the row directly as a C structure and prints the ID column of the row.

Each ODBC connection is internally mapped to a kernel session. Oracle Lite extends the ODBC routine SQLGetInfo so that it returns the kernel session handle associated with an ODBC connection when SQL_OOT_SESSION is specified. The session handle returned by SQLGetInfo can then be used to interact with OKAPI.

#include <string.h>

#include <stdio.h>

#include <stdlib.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include "okapi.h"

#include "interop.h"


#define DROP_PERSON "DROP TABLE PERSON"

#define CREATE_PERSON \

   "CREATE TABLE PERSON(ID INTEGER, AGE INTEGER)"

#define INSERT_PERSON "INSERT INTO PERSON VALUES(1, 25)"

#define SELECT_PERSON_REF "SELECT POL_REF FROM PERSON"


typedef struct Person_s { /* must match the SQL table */

  ok4B ID;

  ok4B AGE;

} Person_s;


void GetRef()

{

  HENVH env = NULL; /* environment handle */

  HDBCH dbc = NULL; /* connection handle */

  HSTMTH stmt = NULL; /* statement handle */

  RETCODE rc = 0; /* return code from ODBC functions */

  okRef Ref = NULL; /* object reference */

  okEnv Session = NULL; /* session handle */

  Person_s *aPerson; /* pointer to a person row */

  okError e = OK_NOERROR; /* return code from OKAPI functions */

  FILE *OutFile;


  OutFile = fopen("example.out", "w");

  if (OutFile == NULL) { /* error handling */ }


  /* prepare for an ODBC statement */

  rc = SQLAllocEnv(&Henv); /* allocate an environment */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLAllocConnect(Henv, &Hdbc); /* allocate a connection */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLConnect(Hdbc, "POL", SQL_NTS, "SYSTEM", SQL_NTS, 

     "SYSTEM", SQL_NTS); /* connect to the POL data source */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLAllocStmt(Hdbc, &Hstmt); /* allocate a statement */

  if (rc != SQL_SUCCESS) { /* error handling */ }


  /* create PERSON table and populate it */

  rc = SQLExecDirect(Hstmt, DROP_PERSON, SQL_NTS);

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLExecDirect(Hstmt, CREATE_PERSON, SQL_NTS);

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLExecDirect(Hstmt, INSERT_PERSON, SQL_NTS);

  if (rc != SQL_SUCCESS) { /* error handling */ }


  /* select POL_REF (object reference) & bind result to Ref */

  rc = SQLExecDirect(Hstmt, SELECT_PERSON_REF, SQL_NTS);

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLBindCol(Hstmt, 1, SQL_C_DEFAULT, &Ref, 0, NULL);

  if (rc != SQL_SUCCESS) { /* error handling */ }


  /* get the session handle associated with the connection */

  rc = SQLGetInfo(Hdbc, SQL_OOT_SESSION, 

  &Session, sizeof(okEnv), NULL);

  if (rc != SQL_SUCCESS) { /* error handling */ }


  /* fetch the results until no data is found */

   for (; ;) {

      rc = SQLFetch(Hstmt);

      if (rc == SQL_NO_DATA_FOUND) break;

      if (rc != SQL_SUCCESS) { /* error handling */ }

      e = okFixObj(Session, Ref, OK_SLOCK, 

      (okObjData *)&aPerson); /* locate the object */

      if (OK_IS_ERROR(e)) { /* error handling */ }

      fprintf(OutFile, "PERSON ID: %d\n", aPerson->ID);

      e = okUnfixObj(Session, Ref, FALSE); /* release object */

      if (OK_IS_ERROR(e)) { /* error handling */ }

   }  


  /* clean up resources used by ODBC */

  rc = SQLFreeStmt(Hstmt, SQL_DROP); /* release statement */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLDisconnect(Hdbc); /* close connection */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLFreeConnect(Hdbc);/* release connection */

  if (rc != SQL_SUCCESS) { /* error handling */ }

  rc = SQLFreeEnv(Henv); /* release environment */

  if (rc != SQL_SUCCESS) { /* error handling */ }

} 


int PASCAL WinMain (HANDLE hInstance, HANDLE hPrevInstance,

LPSTR lpszCmdParam, int nCmdShow)

{

  GetRef();

  return 0;

}