Connecting to Your Database
Using Embedded SQL

Since PowerBuilder does not inspect all SQL statement syntax, you can qualify Oracle catalog entities as necessary.

For example, all of the following qualifications are acceptable:

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:

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

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

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);
}

You can use the following transaction management statements with one or more transaction objects to manage connections and transactions for an Oracle database:

See also

Oracle Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

The following table lists each transaction management statement and describes how it works when you use any Oracle interface to connect to a database:

Note Oracle does not support the AutoCommit property of the transaction object.

See also

Oracle Performance and locking

An important consideration when designing a database application is deciding when CONNECT and COMMIT statements should occur to maximize performance and limit locking and resource use. A CONNECT takes a certain amount of time and can tie up resources during the life of the connection. If this time is significant, then limiting the number of CONNECTs is desirable.

After a connection is established, SQL statements can cause locks to be placed on database entities. The more locks there are in place at a given moment in time, the more likely it is that the locks will hold up another transaction.

Rules

No set of rules for designing a database application is totally comprehensive. However, when you design a PowerBuilder application, you should do the following:

Example 1

This script uses embedded SQL to connect to a database and insert a row in the ORDER_HEADER table and a row in the ORDER_ITEM table. Depending on the success of the statements in the script, the script executes a COMMIT or ROLLBACK.

// Set the SQLCA connection properties.
SQLCA.DBMS = "O73"
SQLCA.servername = "@TNS:SHOPFLR"
SQLCA.logid = "JPL"
SQLCA.logpass = "STUMP"

// Connect to the database.
CONNECT USING SQLCA;

// Insert a row into the ORDER_HEADER table.
// A ROLLBACK is required only if the first row
// was inserted successfully.
INSERT INTO ORDER_HEADER (ORDER_ID, CUSTOMER_ID)
   VALUES ( 7891, 129 );

// Test return code for ORDER_HEADER insertion.
If SQLCA.sqlcode = 0 then

// Since the ORDER_HEADER is inserted,
// try to insert ORDER_ITEM.
   INSERT INTO ORDER_ITEM &
         (ORDER_ID,ITEM_NBR,PART_NBR,QTY)
      VALUES ( 7891, 1, '991PLS', 456 );

// Test return code for ORDER_ITEM insertion.
   If SQLCA.sqlcode = -1 then
// The insert failed.
// Roll back insertion of ORDER_HEADER.
      ROLLBACK USING SQLCA;
   End If
End If
COMMIT USING SQLCA;

// Disconnect from the database.
DISCONNECT USING SQLCA;

Example 2

This example uses the scripts for the Open and Close events in a window and the Clicked event in a CommandButton to illustrate how you can manage transactions in a DataWindow control. Assume the window contains a DataWindow control dw_1 and the user enters data in dw_1 and then clicks the Cb_Update button to send the data to the database.

Since this script uses SetTransObject to connect to the database, the programmer is responsible for managing the transaction.

The window Open event script:

// Set the transaction object properties
// and connect to the database.
// Set the SQLCA connection properties.
SQLCA.DBMS = "O73"
SQLCA.servername = "@TNS:SHOPFLR"
SQLCA.logid = "JPL"
SQLCA.logpass = "STUMP"

// Connect to the database.
CONNECT USING SQLCA;

// Tell the DataWindow which transaction object to use.
dw_1.SetTransObject( SQLCA )

The CommandButton Clicked event script:

// Declare ReturnValue an integer.
integer    ReturnValue

// Update dw_1.
ReturnValue = dw_1.Update( )

// Test to see whether the updates were successful.
If ReturnValue = -1 then

// The updates were not successful.
// Roll back any changes made to the database.
   ROLLBACK USING SQLCA;
Else

// The updates were successful.
// Commit any changes made to the database.
   COMMIT USING SQLCA;
End If

The window Close event script:

// Since we used SetTransObject,
// disconnect from the database.
DISCONNECT USING SQLCA;

The statements that do not involve cursors are:

Internally, PowerBuilder processes DELETE, INSERT, and UPDATE statements the same way. PowerBuilder inspects them for any PowerScript variable references and replaces all references with a constant that conforms to Oracle rules for the data type.

Example

Assume you enter the following statement:

DELETE FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var is a PowerScript variable with the data type of integer that has been defined within the scope of the script that contains the DELETE statement. Before the DELETE statement is executed, emp_id_var is assigned a value (say 691) so that when the DELETE statement executes, the database receives the following statement:

DELETE FROM employee WHERE emp_id = 691;

When is this substitution technique used?

This variable substitution technique is used for all PowerScript variable types. When you use embedded SQL, precede all PowerScript variables with a colon ( : ).

See also

Oracle SELECT

The SELECT statement contains input variables and output variables.

Example 1

Assume you enter the following statement:

SELECT emp_name, emp_salary 
   INTO :emp_name_var, :emp_salary_var
   FROM employee WHERE emp_id = :emp_id_var;

In this example, emp_id_var, emp_salary_var, and emp_name_var are variables defined within the scope of the script that contains the SELECT statement, and emp_id_var is processed as described in the DELETE example above.

Both emp_name_var and emp_salary_var are output variables that will be used to return values from the database. The data types of emp_name_var and emp_salary_var should be the PowerScript data types that best match the Oracle data type. When the data types do not match perfectly, PowerBuilder converts them.

Assume the value for emp_id_var is 691 as in the previous example. When the SELECT statement executes, the database receives the following statement:

SELECT emp_name,emp_salary FROM employee WHERE emp_id=691;

If the statement executes with no errors, data locations for the result fields are bound internally. The data returned into these locations is then converted as necessary, and the appropriate PowerScript variables are set to those values.

Example 2

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. It also assumes the data type of the emp_id column in the employee table is CHARACTER[10].

The user enters an employee ID into the line edit sle_Emp and clicks the button Cb_Delete to delete the employee.

The script for the Clicked event in the CommandButton Cb_Delete is:

// Make sure we have a value.
if sle_Emp.text <> "" then
// Since we have a value, let's try to delete it.
   DELETE FROM employee 
      WHERE emp_id = :sle_Emp.text;
// Test to see if the DELETE worked.
   if SQLCA.sqlcode = 0 then
// It seems to have worked, let user know.
      MessageBox( "Delete",&
         "The delete processed successfully!")
   else
// It didn't work.
      MessageBox("Error", & 
         "The delete failed. Invalid Employee ID")
   end if
else
// No input value. Prompt user.
   MessageBox( "Error", & 
   "An employee ID is required for delete!") 
end if

Example 3

This example assumes the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has executed. The user wants to extract rows from the employee table and insert them into the table named extract_employees. The extraction occurs when the user clicks the button Cb_Extract. The boolean variable YoungWorkers is set to TRUE or FALSE elsewhere in the application.

The script for the Clicked event for the CommandButton Cb_Extract is:

integer    EmployeeAgeLowerLimit
integer    EmployeeAgeUpperLimit
// Do they have young workers?
if (YoungWorkers = TRUE ) then
// Yes - set the age limit in the YOUNG range.
// Assume no employee is under legal working age.
   EmployeeAgeLowerLimit = 16
// Pick an upper limit.
   EmployeeAgeUpperLimit = 42
else 
// No - set the age limit in the OLDER range.
   EmployeeAgeLowerLimit = 43
// Pick an upper limit that includes all
// employees.
   EmployeeAgeUpperLimit = 200
end if
INSERT INTO extract_employee (emp_id,emp_name)
   SELECT emp_id, emp_name FROM employee
      WHERE emp_age >= :EmployeeAgeLowerLimit AND
      emp_age <= :EmployeeAgeUpperLimit;

In embedded SQL, statements that retrieve data and statements that update data can both involve cursors.

Retrieval statements

The retrieval statements that involve cursors are:

Update statements

The update statements that involve cursors are:

PowerBuilder supports all Oracle cursor features.

See also

Oracle Cursor support summary

Oracle Retrieval

Oracle Update

Retrieval using cursors is conceptually similar to retrieval in the singleton SELECT. The main difference is that since there can be multiple rows in a result set, you control when the next row is fetched into the PowerScript variables.

If you expect only a single row to exist in the employee table with the specified emp_id, use the singleton SELECT. In a singleton SELECT, you specify the SELECT statement and destination variables in one concise SQL statement:

SELECT emp_name, emp_salary
   INTO :emp_name_var, :emp_salary_var
   FROM employee WHERE emp_id = :emp_id_var;

However, if the SELECT may return multiple rows, you must:

Declaring and opening a cursor

Declaring a cursor is tightly coupled with the OPEN statement. The DECLARE specifies the SELECT statement to be executed, and the OPEN actually executes it.

Declaring a cursor is similar to declaring a variable; a cursor is a nonexecutable statement just like a variable declaration. The first step in declaring a cursor is to define how the result set looks. To do this, you need a SELECT statement, and since you must refer to the result set in subsequent SQL statements, you must associate the result set with a logical name.

Note

For UPDATE ... WHERE CURRENT OF cursor_name and DELETE ... WHERE CURRENT OF cursor_name statements to execute successfully, the SELECT statement must contain the FOR UPDATE clause.

Example

Assume the SingleLineEdit sle_1 contains the state code for the retrieval:

// Declare cursor emp_curs for employee table
// retrieval.
DECLARE emp_curs CURSOR FOR
   SELECT emp_id, emp_name FROM EMPLOYEE
   WHERE emp_state = :sle_1.text;
// For UPDATE WHERE CURRENT OF cursor_name and
// DELETE WHERE CURRENT OF cursor_name to work
// correctly in Oracle 7, include the FOR UPDATE
// clause in the SELECT statement.
// Declare local variables for retrieval.
string    emp_id_var
string    emp_name_var
// Execute the SELECT statement with
// the current value of sle_1.text.
OPEN emp_curs;
// At this point, if there are no errors,
// the cursor is available for further processing.

Fetching Rows

In the singleton SELECT, you specify variables to hold the values for the columns within the selected row. The FETCH statement syntax is similar to the syntax of the singleton SELECT. Values are returned INTO a specified list of variables.

This example continues the previous example by retrieving some data:

// Get the first row from the result set.
FETCH emp_curs INTO :emp_id_var, :emp_name_var;

If at least one row can be retrieved, this FETCH places the values of the emp_id and emp_name columns from the first row in the result set into the PowerScript variables emp_id_var and emp_name_var. FETCH statements typically occur in a loop that processes several rows from a result set (one row at a time), but that is not the only way they are used.

Closing the cursor

The CLOSE statement terminates processing for the specified cursor. CLOSE releases resources associated with the cursor, and subsequent references to that cursor are allowed only if another OPEN is executed. Although you can have multiple cursors open at the same time, you should close the cursors as soon as possible for efficiency reasons.

After a FETCH statement completes successfully, you are positioned on a current row within the cursor. At this point, you can execute an UPDATE or DELETE statement using the WHERE CURRENT OF cursor_name syntax to update or delete the row. PowerBuilder enforces Oracle cursor update restrictions, and any violation results in an execution error.

Example 1

This cursor example illustrates how you can loop through a result set. Assume the default transaction object (SQLCA) has been assigned valid values and a successful CONNECT has been executed.

The statements retrieve rows from the employee table and then display a message box with the employee name in each row that is found.

// Declare the emp_curs cursor.
DECLARE emp_curs CURSOR FOR
   SELECT emp_name FROM EMPLOYEE 
      WHERE emp_state = :sle_1.text;
// For UPDATE WHERE CURRENT OF cursor_name and
// DELETE WHERE CURRENT OF cursor_name to work
// correctly in Oracle 7, include the FOR UPDATE
// clause in the SELECT statement.
// Declare a destination variable for employee 
// names.
string    emp_name_var
// Execute the SELECT statement with the
// current value of sle_1.text.
OPEN emp_curs;
// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;
// Loop through result set until exhausted.
DO WHILE SQLCA.sqlcode = 0
// Display a message box with the employee name.
   MessageBox("Found an employee!",emp_name_var)
// Fetch the next row from the result set.
   FETCH emp_curs INTO :emp_name_var;
LOOP
// All done, so close the cursor.
CLOSE emp_curs;

Example 2

This cursor example illustrates how to use a cursor to update or delete rows. The statements use emp_curs to retrieve rows from the employee table and then ask whether the user wants to delete the employee:

// Declare the emp_curs cursor.
DECLARE emp_curs CURSOR FOR
   SELECT  emp_name FROM employee
      WHERE emp_state = :sle_1.text;
// Declare a destination variable for employee
// names. 
string emp_name_var
// Declare a return variable for the MessageBox.
int return_var 
// Execute the SELECT statement with the current
// value of sle_1.text.
OPEN emp_curs;
// Fetch the first row from the result set.
FETCH emp_curs INTO :emp_name_var;
// Loop through result set until it is
// exhausted.
DO WHILE SQLCA.sqlcode = 0
// Ask the user to confirm the deletion.
   return_var = MessageBox( "Want to delete?",& 
   emp_var_name, Question!, YesNo!, 2 )
// Delete?
If ( return_var = 1 ) then
// Yes - delete the employee.
         DELETE FROM employee 
            WHERE CURRENT OF emp_curs;
      End If
// Fetch the next row from the result set.
   FETCH emp_curs INTO :emp_name_var;
LOOP
// All done, so close the cursor.
CLOSE emp_curs;

When you use cursors with any Oracle interface, keep the following points in mind:

Oracle stored procedures

If your database is Oracle Version 7.2 or higher, you can use an Oracle stored procedure that has a result set as an IN OUT (reference) parameter.

Procedures with a single result set

You can use stored procedures that return a single result set in DataWindow objects, reports, and embedded SQL, but not when using the RPCFUNC keyword to declare the stored procedure as an external function or subroutine.

Procedures with multiple result sets

You can use stored procedures that return multiple result sets only in embedded SQL. Multiple result sets are not supported in DataWindow objects, reports, or with the RPCFUNC keyword.

The O90 database interface supports SQL CREATE TYPE and CREATE TABLE statements for Oracle user-defined types (objects) in the ISQL view of the Database painter. It correctly handles SQL SELECT, INSERT, UPDATE, and DELETE statements for user-defined types in the Database and DataWindow painters. For more information, see Using Oracle

Methods for using Oracle stored procedures

There are three methods for using Oracle stored procedures in a PowerBuilder application:

See also

Supported features when using Oracle stored procedures

Using DECLARE, EXECUTE, FETCH, and CLOSE with Oracle stored procedures

Supported features with RPCFUNC keyword

The following are supported and unsupported Oracle PL/SQL features when you use the RPCFUNC keyword to declare the stored procedure:

Supported features with DECLARE Procedure statement

The following are supported and unsupported Oracle PL/SQL features when you use the DECLARE Procedure statement:

For an example that uses a REF CURSOR variable of type IN OUT, see Using Oracle

PowerBuilder provides SQL statements that are very similar to cursor operations to support retrieval using database stored procedures. In PowerBuilder embedded SQL, there are four commands that involve database stored procedures:

PowerBuilder requires a declarative statement to identify the database stored procedure that is being used and a logical name that can be referenced in subsequent SQL statements. The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
   Oracle_procedure_name(:InParam1,:InParam2, ...)
   {USING transaction_object};

where logical_procedure_name can be any valid PowerScript data identifier and Oracle_procedure_name is the name of the stored procedure in the database.

The parameter references can take the form of any valid parameter string that Oracle accepts. PowerBuilder does not inspect the parameter list format except for purposes of variable substitution. The USING clause is required only if you are using a transaction object other than the default transaction object.

You can use Oracle Named or Positional notation to specify the procedure arguments. Positional is simpler to specify, but you must use Named if any output parameters are defined to the left of any input parameters.

Example 1

If a stored procedure is defined as:

CREATE PROCEDURE spm1
   (dept varchar2, mgr_name OUT varchar2) 
   IS lutype varchar2(10);
   BEGIN 
   SELECT manager INTO mgr_name FROM mgr_table
   WHERE dept_name = dept;
   END;

To declare that procedure for processing within PowerBuilder, you code:

DECLARE dept_proc PROCEDURE FOR spm1(:dept);

Note that this declaration is a non-executable statement, just like a cursor declaration. Where cursors have an OPEN statement, procedures have an EXECUTE statement.

When the EXECUTE statement executes, the procedure is invoked. The EXECUTE refers to the logical procedure name.

EXECUTE dept_proc;

Example 2

The following example that declares a function in a service object that reads a pipe shows the use of named notation:

public function integer f_GetId (string as_PipeName)
double ldbl_Id
DECLARE f_GetId PROCEDURE FOR 
   f_GetId (pipe_name => :as_PipeName) USING SQLCA;
EXECUTE f_GetId;
FETCH f_GetId INTO :ldbl_Id;
CLOSE f_GetId;
RETURN ldbl_Id;

Example 3

Given this procedure:

CREATE OR REPLACE PROCEDURE spu_edt_object(
o_id_object OUT NUMBER,
o_message OUT VARCHAR2,
a_id_object NUMBER,
a_param VARCHAR2 := NULL,
a_value VARCHAR2 := NULL
) as
begin
o_id_object := 12345;
o_message := 'Hello World';
end;

The DECLARE statement must use named notation because output parameters are defined to the left of input parameters:

dec{0} o_id_object, id_obiect = 54321
string o_message, param = 'Test'

DECLARE proc_update PROCEDURE FOR spu_edt_object (
a_id_object => :id_object,
a_param => :param
)
USING SQLCA;

EXECUTE proc_update;
if SQLCA.SqlCode 0 then
SQLCA.f_out_error()
RETURN -1
end if

FETCH proc_update INTO :o_id_object, o_message;
if SQLCA.SqlCode 0 then
SQLCA.f_out_error()

RETURN -1
end if

To access rows returned by a procedure, you use the FETCH statement as you did for cursors. You can execute the FETCH statement after any EXECUTE statement that executes a procedure that has output parameters.

Example

FETCH dept_proc INTO :name_var;

The FETCH FROM procedure statements must exactly match the output parameters returned by the stored procedure when it executes.

If a database stored procedure has output parameters, it must be closed when processing is complete.

Closing a procedure looks the same as closing a cursor.

Example

CLOSE dept_proc;