OLE DB SQL
functions
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:
|
1 2 3 |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
// 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
// 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