Loading a result set into a temp-table

Enhancements implemented through changes to the RUN STORED-PROC statement allow you to retrieve a result set from a foreign data source and load the 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.

This example introduces 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 the RUN STORED-PROC statement with send-sql-statement optionand introduces the dynamic temp-table topic further discussed in this section.

DEFINE VARIABLE bh      AS HANDLE NO-UNDO.
DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery  AS HANDLE NO-UNDO.
DEFINE VARIABLE tt1     AS HANDLE NO-UNDO.

DEFINE FRAME BrowseFrame WITH SIZE 80 BY 10.

CREATE TEMP-TABLE tt1.

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

bh = tt1:DEFAULT-BUFFER-HANDLE.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS (bh).
DISPLAY tt1:name.
hQuery:QUERY-PREPARE("FOR EACH" + tt1: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(bh).
ENABLE ALL WITH FRAME BrowseFrame.
WAIT-FOR CLOSE OF CURRENT-WINDOW.