ODBC 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:

SELECT Sum(salary)
   INTO :salary_sum_var
   FROM employee;

Calling ODBC functions

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

The ODBC calls qualify for this type of access. Most ODBC calls require a pointer to a connection handle (of the variable type HDBC) to a structure as their first parameter. If you want to call ODBC without reconnecting to the database to get a connection handle, use the PowerScript DBHandle function.

DBHandle

DBHandle takes a transaction object as a parameter and returns a long variable, which is the handle to the database for the transaction. This handle is actually the connection handle PowerBuilder uses internally to communicate with the database. You can use this returned long value in the ODBC DLLs and pass it as one of the parameters in your function.

After you obtain the connection handle, you can use the ODBC SQLGetInfo call to obtain the environment handle of the variable type HENV.

Example

This example illustrates how to use DBHandle. 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         ODBCConnectionHandle

// Go get the handle.
ODBCConnectionHandle = SQLCA.DBHandle( )

// Now that you have the ODBC connection pointer,
// call the DLL function.
MyDLLFunction(ODBCConnectionHandle, parm1, parm2)

In your DLL, cast the incoming connection handle of the type HDBC:

MyDLLFunction(long 1ODBCConnectionHandle,
   parm1_type parm1,
   parm2_type Parm2, ...)
{
HDBC * pDatabase;
pDatabase = (HDBC *)  1ODBCConnectionHandle;
// ODBC functions can be called using pDatabase.
}

See also

ODBC Using escape clauses