Loading result sets into temp-tables

Enhancements implemented through changes to the RUN STORED-PROC statement allow you to retrieve result sets from a foreign data source and load each result set, for which a temp-table handle is defined, into its own temp-table. The LOAD_RESULT-INTO function enables data retrieved to be loaded into temp-tables where the data can then be manipulated, employing all characteristics inherent to temp-tables. The capability to load result sets into temp-tables is not limited by the parsing requirements associated with the proc-text-buffer nor the database dependencies associated with views.

Temp-tables can provide data management capabilities associated with ABL directly to the result sets of a stored procedure, but completely independent of the foreign data source from which it was populated and/or derived. Temporary tables are effectively database tables in which OpenEdge stores data temporarily. Because temp-tables have the same support features that actual OpenEdge databases use, you can take advantage of almost all the OpenEdge database features that do not require data persistence and multi-user access. For example, you can define indexes for fields in the temp-table. For more information about temp-tables, see OpenEdge Getting Started: ABL Essentials.

RUN STORED-PROC statement with the LOAD-RESULT-INTO phrase

The following example shows how to use the RUN STORED-PROC statement with the LOAD-RESULT-INTO phrase with a single dynamic temp-table. It highlights the coding techniques discussed in Run Stored-Proc statement execution using the send-sql-statement option the and it introduces the dynamic temp-table topic further discussed in later sections.

DEFINE VARIABLE bhCustomer AS HANDLE NO-UNDO.
DEFINE VARIABLE hBrowse    AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery     AS HANDLE NO-UNDO.
DEFINE VARIABLE ttCustomer AS HANDLE NO-UNDO.

DEFINE FRAME BrowseFrame WITH SIZE 80 BY 10.

CREATE TEMP-TABLE ttCustomer.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttCustomer
  ("SELECT * from CUSTOMER").

bhCustomer = ttCustomer:DEFAULT-BUFFER-HANDLE.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS (bhCustomer).
DISPLAY ttCustomer:Name.
hQuery:QUERY-PREPARE("FOR EACH" + ttCustomer:Name).
hQuery:QUERY-OPEN.

CREATE BROWSE hBrowse ASSIGN
  ROW         = 1
  COL         = 1
  WIDTH       = 79
  DOWN        = 10
  FRAME       = FRAME BrowseFrame:HANDLE
  QUERY       = hQuery
  SENSITIVE   = TRUE
  SEPARATORS = TRUE
  ROW-MARKERS = FALSE
  VISIBLE     = TRUE.

hBrowse:ADD-COLUMNS-FROM(bhCustomer).
ENABLE ALL WITH FRAME BrowseFrame.
WAIT-FOR CLOSE OF CURRENT-WINDOW.