Oracle SQL functions
In
SQL statements, you can use any function that Oracle supports (such
as aggregate or mathematical functions).
For example, you can use the Oracle function UPPER in a SELECT
statement:
1 |
SELECT UPPER(emp_name)<br> INTO :emp_name_var<br> FROM employee; |
Calling OCI functions
While PowerBuilder provides access to a large percentage of
the features within Oracle, in some cases you may want to call one
or more OCI functions directly. In PowerBuilder you can use external
function declarations to access any Windows DLL.
The OCI calls qualify for this type of access. Most OCI calls
require a pointer to an LDA_DEF structure as their first
parameter. If you want to call OCI functions without reconnecting
to the database to get an LDA_DEF 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 LDA_DEF pointer that PowerBuilder
uses internally to communicate with the database. You can use the
returned value in your DLLs and pass it as one of the parameters
in your function.
Example
This example shows how to use DBHandle. Assume a successful
connection has occurred using the default transaction object (SQLCA):
1 |
// Define a variable to hold our DB handle.<br>long OracleHandle |
1 |
// Get the handle.<br>OracleHandle = SQLCA.DBHandle( ) |
1 |
// Now that you have the LDA_DEF pointer,<br>// call the DLL function.<br>MyDLLFunction( OracleHandle, parm1, parm2, ... ) |
In your DLL, cast the incoming long value into a pointer to
an ORA_CSA:
1 |
VOID FAR PASCAL MyDLLFunction( long lOracleHandle,<br> parm1_type parm1,<br> parm2_type parm2, ... )<br>{ |
1 |
// pLda will provide addressability to the Oracle<br>// logon data area<br>Lda_Def FAR *pLda = (Lda_Def FAR *)lOracleHandle; |
1 |
// pCda will point to an Oracle cursor<br>Cda_Def FAR *pCda = &<br> GlobalAllocPtr(GMEM_MOVEABLE,sizeof(Cda_Def));<br>if(! pCda ) |
1 |
// handle error...<br>if(open(pCda, pLda,NULL, -1, -1, NULL, -1)) |
1 |
// handle error...<br>#ifdef Oracle7 |
1 |
// parse the DELETE statement<br>if(osql3(pCda, <br> "DELETE FROM EMPLOYEE WHERE Emp_ID = 100", -1); |
1 |
#else<br>if(oparse(pCda,<br> "DELETE FROM EMPLOYEE <br> WHERE Emp_ID = 100", -1, 0, 1) :<br>#endif |
1 |
// handle error...<br> if(oclose(pCda)) |
1 |
// handle error...<br> GlobalFreePtr(pCda);<br>} |