Try OpenEdge Now
skip to main content
DataServer for Oracle
RDBMS Stored Procedure Details : ROWID support : Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO
 

Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO

The example in this section assumes you migrated your database to Oracle using the CREATE RECID option, as discussed in the ROWIDcharacteristics . This example and all subsequent examples show ROWID as being represented by the 4-byte integer value of the PROGRESS_RECID column as opposed to some other unique single-component index designated in your database to be the Progress RECID.
Note: If you used a different single-component index to load the ROWID of a temp-table or native ROWID, you would need to map it accordingly, just as the example maps PROGRESS_RECID.
The RUN STORED-PROC command has no native awareness that the Oracle Database Table is being queried for the result set(s) it generates. Therefore, to allow DataServer technology to convert the stored PROGRESS_RECID value into a native Progress ROWID value, the physical name of the target database table must be known. To achieve this bond, the temp-table that the stored procedure populates must be associated with an OpenEdge ProDataSet object.
Simple ProDataSet code
This example shows an ABL query filling the temp tables of a ProDataSet. It is used as the baseline code which is referenced throughout the remainder of this section.
DEFINE VARIABLE phDataSet AS HANDLE NO-UNDO.

DEFINE TEMP-TABLE ttCustomer LIKE Sports.Customer
  FIELD iRecid AS INTEGER.

DEFINE DATASET dsCust FOR ttCustomer.
DEFINE QUERY qCustomer FOR Customer.

phDataSet = DATASET dsCustomer:HANDLE.
DEFINE DATA-SOURCE srcCustomer FOR QUERY qCustomer.
BUFFER ttCustomer:HANDLE:ATTACH-DATA-SOURCE
  (DATA-SOURCE srcCustomer:HANDLE,?,?,?).
QUERY qCustomer:QUERY-PREPARE("FOR EACH customer").
DATASET dsCustomer:FILL().

FOR EACH ttCustomer:
  DISPLAY ttCustomer.name ttCustomer.iRecid.
END.
Using the LOAD-RESULT-INTO technique to populate the underlying temp-table of a ProDataSet
This example combines code from the above codes applying the results of the RUN STORED-PROC [LOAD-RESULT-INTO] technique, rather than an ABL query, to fill the temp-table associated with a ProDataSets. Key points about this example are presented following the example.
DEFINE VARIABLE hSendSQL  AS HANDLE NO-UNDO EXTENT 1.
DEFINE VARIABLE phDataSet AS HANDLE NO-UNDO.
DEFINE VARIABLE rid-1     AS ROWID  NO-UNDO.
DEFINE VARIABLE rid-2     AS ROWID  NO-UNDO.

DEFINE TEMP-TABLE ttCustomer LIKE Sports.Customer
  FIELD rRecid AS ROWID. /* MUST BE CHANGED TO ROWID TYPE */

hSendSQL[1] = TEMP-TABLE ttCustomer:HANDLE.

DEFINE DATASET dsCustomer FOR ttCustomer.
DEFINE QUERY qCustomer FOR Customer.

phDataSet = DATASET dsCustomer:HANDLE.

DEFINE DATA-SOURCE srcCustomer FOR QUERY qCustomer.
BUFFER ttCustomer:HANDLE:ATTACH-DATA-SOURCE
  (DATA-SOURCE srcCustomer:HANDLE,?,?,?).

FIND FIRST customer WHERE customer.custnum = 1 NO-LOCK.
rid-1 = ROWID(customer).

/* Populate the ttCustomer temp-table */
RUN STORED-PROC send-sql-statement
  LOAD-RESULT-INTO hSendSQL ("select * from customer").

FIND FIRST ttCustomer WHERE ttCustomer.custnum = 1 NO-LOCK.
rid-2 = ttCust.rRecid.

If rid-1 <> rid-2 THEN
  MESSAGE "The same record but different ROWID's".
IF rid-1=rid-2 THEN
  MESSAGE "Congratulations - we have the same ROWID's".

MESSAGE STRING(ttCustomer.rRecid) VIEW-AS ALERT-BOX.
Keep the following key points in mind as you review this example:
*The TEMP-TABLE field that is mapped to the PROGRESS_RECID column should be changed from its standard definition of INTEGER to ROWID. In this example, the result column location where PROGRESS_RECID is being returned has been named rRecid in the temp-table. This renaming occurs in Example 3-26 because of the following line:
FIELD rRecid       AS ROWID /* must be changed to ROWID type */
FIELD iRECID_ident AS INTEGER.
*The TEMP-TABLE must be defined to the ProDataSet. The following line, another excerpted line of code from this example, shows this definition:
DEFINE DATASET dsCustomer FOR ttCustomer.
*The technique, demonstrated in this example, does not change the default behavior of the ROWID function, but provides a mechanism for the ROWID value to be stored along side its corresponding result rows; therefore, using the ROWID value to access database rows is unconventional with respect to the normal, or more typical, association between ROWID and a database table row. The following code demonstrates this difference.
Default use of ROWID function on a record buffer, as excerpted from this example:
FIND FIRST customer WHERE customer.custnum = 1 NO-LOCK.
rid-1 = ROWID(customer).
In contrast, the following excerpt from this example demonstrates an alternative use of the ROWID value with a temp-table:
FIND FIRST customer WHERE customer.custnum = 1 NO-LOCK.
rid-2 = ttCustomer.Recid.