Connecting to Your Database
Using Embedded SQL with OLE DB

In SQL statements, you can use any function that your backend DBMS supports (such as aggregate or mathematical functions). For example, if your DBMS supports the function Sum, you can use the function Sum in a SELECT statement:

SELECT Sum(salary) 
   INTO :salary_sum_var 
   FROM employee;

Calling OLE DB functions

While PowerBuilder provides access to a large percentage of the features within OLE DB, in some cases you might decide that you need to call one or more OLE DB functions directly for a particular application. PowerBuilder provides access to most Windows DLLs by using external function declarations.

PowerBuilder OLE DB can export OLE DB data source objects or session objects to users using the PowerScript function DBHandle. Users can create their own session objects using the exported data source object, so they can get a new independent connection that has connection properties similar to those used by PowerBuilder OLE DB. With the exported session object, users can also create their own command object that is under PowerBuilder OLE DB's transaction scope. The behavior is like using DBHandle() with the PowerBuilder ODBC interface.

DBHandle

DBHandle takes a transaction object as a parameter and returns a long variable, which is an interface pointer to a data source object or a session object. By default PowerBuilder OLE DB exports a data source object. If the DBParm "ReturnCommandHandle=1" is set, PowerBuilder OLE DB exports a session object.

Example 1

This example illustrates how to use DBHandle to get an OLE DB data source object. As with other examples, assume a successful connection has occurred using the default transaction object (SQLCA).

// Define a variable to hold the DB connection handle.
Long OleDbCnnInterface

// Get OLE DB Data Source Object 
OleDbCnnInterface = SQLCA.DBHandle()

// Now that you have the OLE DB data source object, 
// call the DLL function. 
MyDLLFunction(OleDbCnnInterface, parm1, parm2)

// In your DLL, cast the incoming handle to the 
// IUnknown* interface

MyDLLFunction(long OleDbCnnInterface, 
               parm1_type parm1, 
               parm2_type Parm2, ...)
{ 
   IUnknown* pUnkDataSource = & 
      (IUnknown*)OleDbCnnInterface; 
   IDBCreateSession*  pIDBCreateSession = NULL;

   pUnkDataSource->QueryInterface(IID_IDBCreateSession, 
   (void**)&pIDBCreateSession));

// now you have the OLE DB IDBCreateSession interface,
// you can create your own independent session object
// from the PowerBuilder OLE DB driver 
   IUnknown **  ppUnkSession;

   pIDBCreateSession->CreateSession(NULL, //pUnkOuter
                  IID_IDBCreateCommand, //riid
                  ppUnkSession          //ppSession
                  );

}

Example 2

This example illustrates how to use DBHandle to get an OLE DB session object.

// Before connection, set DBParm ReturnCommandHandle=1
...
SQLCA.DBParm = "ReturnCommandHandle = 1"
CONNECT;

// After successful connection 
// Define a variable to hold the DB connection handle.
long    OleDbCnnInterface

// Get OLE DB session object 
OleDbCnnInterface = SQLCA.DBHandle()

// Now you have the OLE DB session object, 
// call the DLL function. 
MyDLLFunction(OleDbCnnInterface, parm1, parm2)

// In your DLL, cast the incoming handle to 
// IUnknown* interface

MyDLLFunction(long OleDbCnnInterface, 
   parm1_type parm1, 
   parm2_type Parm2, ...)

{
      IUnknown* pUnkSession = &
         (IUnknown*)OleDbCnnInterface;
         IDBCreateCommand *  pIDBCreateCommand = NULL;

         pUnkSession->QueryInterface & 
               (IID_IDBCreateCommand,
            (void**)&pIDBCreateCommand));

With the IDBCreateCommand interface used by the PowerBuilder OLE DB interface, you can create your own command object. Your command object and the PowerBuilder command object will be in the same transaction scope.

See also

OLE DB Using ODBC escape Sequences