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

The example in the previous section assumes you migrated your database to MS SQL Server using the Create RECID Fieldoption, as discussed in ROWIDcharacteristics. The examples in this section show ROWID as being represented by the 8-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, you would need to map the column accordingly, just as the example maps PROGRESS_RECID.

The RUN STORED-PROC command has no native awareness that the MS SQL Server 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 OpenEdge ROWID value, the physical name of the target database table needs to be known. To achieve this bond, the temp-table that the stored procedure populates must be associated with an OpenEdge ProDataSet object.

ABL Query filling a ProDataSet temp-table

The following example shows an ABL query filling the temp tables of a ProDataSet. It will be used as the baseline code referenced throughout the remainder of this section.

DEFINE VARIABLE phDataSet AS HANDLE NO-UNDO.

DEFINE TEMP-TABLE ttCustomer LIKE Sports2000.Customer
  FIELD tRecid       AS INTEGER
FIELD tRECID_ident AS INTEGER.

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,?,?,?).
QUERY qCustomer:QUERY-PREPARE ("FOR EACH Customer").
DATASET dsCustomer:FILL().

FOR EACH ttCustomer:
  DISPLAY ttCustomer.name ttCustomer.tRecid.
END.

If the table uses computed-column PROGRESS_RECID option, then ttCustomer temp-table definition should be:

DEFINE TEMP-TABLE ttCustomer LIKE Sports.Customer
FIELD tRecid AS ROWID /* Must be changed to ROWID type */
FIELD tRECID_ident AS INT64
FIELD tRECID_alt as INT64.

Using the LOAD-RESULT-INTO technique to populate the underlying Temp-Table of a ProDataSet

The following example combines code from the examples:

ExpectedABL behavior—ROWID value of a temp-table buffer

ABLQuery filling a ProDataSet temp-table

The example applies the results of the RUN STORED-PROC [LOAD-RESULT-INTO] technique, rather than an ABL query, to fill the TEMP-TABLE associated with a ProDataSet.

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 tRecid       AS ROWID /* Must be changed to ROWID type */
  FIELD tRECID_ident AS INT64.

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

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

phDataSet=DATASET dsCust:HANDLE.

DEFINE DATA-SOURCE srcCustomer FOR QUERY qCust.
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 = ttCustomer.tRecid.

If rid-1 NE 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.tRecid) VIEW-AS ALERT-BOX.

Keep the following key points in mind regarding 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.

The result column location where PROGRESS_RECID is being returned has been named tRecid in the temp-table. The PROGRESS_RECID_IDENT has been renamed tRECID_ident. This renaming occurs because of the following line:

FIELD tRecid AS ROWID /* must be changed to ROWID type */
FIELD tRECID_ident AS INTEGER.

If the table uses the computed-column PROGRESS_RECID option, then the ttCustomer temp-table definition should be:

DEFINE TEMP-TABLE ttCustomer LIKE Sports.Customer
FIELD tRecid AS ROWID /* Must be changed to ROWID type */
FIELD tRECID_ident AS INT64
FIELD tRECID_alt as INT64.

The TEMP-TABLE must be defined to the ProDataSet. The following line excerpted from the complete example shows this definition:

DEFINE DATASET dsCustomer FOR ttCustomer.

The demonstrated technique 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:

FIND FIRST Customer WHERE Customer.CustNum = 1 NO-LOCK.
rid-1 = ROWID(Customer).

In contrast, the following code excerpt demonstrates an alternative use of the ROWID value with a temp-table:

FIND FIRST Customer WHERE Customer.CustNum = 1 NO-LOCK.
rid-2 = ttCustomer.tRecid.