Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
RDBMS Stored Procedure Details : Interfacing with RDBMS stored procedures : Loading result sets into temp-tables : Using a temp-table handle with an unprepared dynamic temp-table
 
Using a temp-table handle with an unprepared dynamic temp-table
When a temp-table handle points to an unprepared dynamic temp-table, the MS SQL Server DataServer defines the temp-table schema in the form of the result sets record structure which is passed back to the DataServer from the foreign data source. The data types defined for the temp-table schema are determined based on the default data type mapping that exists between the SQL data type and its equivalent OpenEdge default data type. Once the temp-table schema is dynamically established by the DataServer, the result set begins to populate it.
Recognize that there is the possibility of a small performance price to be paid when you build dynamic temp-tables. However, considering the database independence that this technique affords over building static temp-tables, you might consider the price of dynamically built temp-tables to be a small, reasonable one.
The following table identifies the temp-table options for which you can plan and the requirements you must fulfill for each option.
Table 23. Options to plan the temp-table layout for result sets
To return a result set to this type of temp-table . . .
Then the layout definition is . . .
Static
Dynamic-prepared state
Defined by you; you must base the layout on the expected fields to be returned and each of these fields' data types so that the first field defined in the temp-table corresponds to the first column of the result set. This column matching and data type matching must be repeated successfully for each temp-table and its corresponding result set.
Dynamic - unprepared state
Not defined by you; the schema of the temp-table is based on the result-set schema and a mapping of default OpenEdge data types for each SQL type. For more details, see Details about a dynamic temp-table in an unprepared state.
Note: Once the data is loaded into the temp-table, any updates made to the records in the temp-table are not propagated back to the foreign database. Result sets are available through temp-tables for the purpose of obtaining a snapshot of the data. For example, you can use this technique to populate a browser from a temp-table. You must re-read the record using the proper lock mechanism to actually update the record.