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) INTO :emp_name_var 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 2 3 4 5 6 7 |
// Define a variable to hold our DB handle. long OracleHandle // Get the handle. OracleHandle = SQLCA.DBHandle( ) // Now that you have the LDA_DEF pointer, // call the DLL function. MyDLLFunction( OracleHandle, parm1, parm2, ... ) |
In your DLL, cast the incoming long value into a pointer to an
ORA_CSA:
|
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 |
VOID FAR PASCAL MyDLLFunction( long lOracleHandle, parm1_type parm1, parm2_type parm2, ... ) { // pLda will provide addressability to the Oracle // logon data area Lda_Def FAR *pLda = (Lda_Def FAR *)lOracleHandle; // pCda will point to an Oracle cursor Cda_Def FAR *pCda = & GlobalAllocPtr(GMEM_MOVEABLE,sizeof(Cda_Def)); if(! pCda ) // handle error... if(open(pCda, pLda,NULL, -1, -1, NULL, -1)) // handle error... #ifdef Oracle7 // parse the DELETE statement if(osql3(pCda, "DELETE FROM EMPLOYEE WHERE Emp_ID = 100", -1); #else if(oparse(pCda, "DELETE FROM EMPLOYEE WHERE Emp_ID = 100", -1, 0, 1) : #endif // handle error... if(oclose(pCda)) // handle error... GlobalFreePtr(pCda); } |