Connecting to Your Database
Using Embedded SQL with Microsoft SQL Server

You can use any function that SQL Server supports (such as aggregate or mathematical functions) in SQL statements.

This example shows how to use the SQL Server function UPPER in a SELECT statement:

SELECT UPPER(emp_name) INTO :emp_name_var FROM employee;

Calling DB-Library functions

While PowerBuilder provides access to a large percentage of the features within SQL Server, in some cases you may decide that you need to call one or more DB-Lib functions directly for a particular application. PowerBuilder provides access to any Windows DLL by using external function declarations.

The DB-Lib calls qualify for this type of access. Most DB-Lib calls require a pointer to a DBPROCESS structure as their first parameter. If you want to call DB-Lib without reconnecting to the database to get a DBPROCESS pointer, 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 DBPROCESS pointer that PowerBuilder uses internally to communicate with the database. You can use this returned long value in the SQL Server DLLs and pass it as one of the parameters in your function.

This example shows how to use DBHandle. Assume a successful connection has occurred using the default transaction object (SQLCA):

// Define a variable to hold our DB handle.
long    SQLServerHandle
// Go get the handle.
SQLServerHandle = SQLCA.DBHandle( )
// Now that you have the DBPROCESS pointer,
// call the DLL function.
MyDLLFunction( SQLServerHandle, parm1, parm2, ... )

In your DLL, cast the incoming long value into a pointer to a DBPROCESS structure:

MyDLLFunction( long 1SQLServerHandle,
      parm1_type parm1,
      parm2_type Parm2, ... )
{
DBPROCESS * pDatabase;
pDatabase = (DBPROCESS *)  1SQLServerHandle;
// DB-Lib functions can be called using pDatabase.
}