Progress
DataServer
for ORACLE Guide
Defining a Buffer
You can read results from a send-sql-statement into a buffer. Progress provides that proc-text-buffer that reads all data as CHARACTER data. Defining your own buffers in an ORACLE database allows you to retrieve database results in their original data types.
Follow these steps to define a buffer.
- Define a view in ORACLE:
- By following the naming convention BUFFER_buffername
- With the same number of columns and data types that the SQL statement returns
- With the columns in the order that the SQL statement returns them
For example, to return two types of values, an integer and a character string, use an ORACLE SQL utility to define the following buffer for your ORACLE database:
- Update your schema image using the Update/Add Table Definitions DataServer utility for ORACLE. The utility adds the view to the list of accessible objects. The DataServer defines the view as a buffer that Progress can use.
See the "Updating a Schema Image" section in "The DataServer Tutorial," for instructions on using this utility.
This buffer defines two returned values-an INTEGER and a CHARACTER value, in that order. The order of the fields is significant. If the data types do not match those returned by the SQL statement, the procedure returns the values in a different order than you specified, and you receive a run-time error. For this buffer, the SQL statement must return two data types, INTEGER and CHARACTER, in that order. If it returns only the CHARACTER data type or first the CHARACTER and then the INTEGER data type, you receive a run-time error. If it returns only the INTEGER data type, the CHARACTER field will have the unknown value.
The easiest way to create a buffer that accepts data from ORACLE stored procedures is to use the text of the SQL SELECT statement from the send-sql-statement option. This way, you ensure that you define your data types correctly, and in the correct order.
The following code example creates a view that never returns any results. Defining a view this way indicates that you will not use it as a view, but as a buffer. The BUFFER_ prefix allows the Data Dictionary to determine that this view defines a buffer. It is not necessary to define views that you will use as buffers this way, but it does allow you to distinguish quickly between views and buffers in the schema image:
Defining a separate buffer for each data type allows the return values to maintain their data types. You can then use the returned values in calculations without first converting them back to their original data types. Reading your results into an explicitly defined buffer also allows you to manipulate the data just as you would manipulate data from a Progress database, with Frame phrases and FORM statements, for example. You might also want to define separate buffers for columns. Separate buffers make your output more readable, because Progress builds a new default frame for each buffer.
This example runs the send-sql-option twice; procedure handles (through the PROC-HANDLE function) identify the different results from the ORACLE database:
If you use more than one send-sql-statement at a time to send SELECT statements, you must explicitly define procedure handles for each.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |