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.

  1. 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:

      CREATE VIEW BUFFER_custorder AS SELECT customer.cust_num, 
      customer.name FROM customer 
      

  2. 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.
  3. 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:

CREATE VIEW BUFFER_pcust_orders AS SELECT customer.cust_num, 
    customer.name, order_num FROM customer, order_ 
     WHERE 1 = 2 

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:

/* Procedure handles */

DEFINE VAR handle1 AS integer.
DEFINE VAR handle2 AS integer.
DEFINE VAR x AS character.
RUN STORED-PROC send-sql-statement handle1 = 
 PROC-HANDLE ("SELECT cust_num, state FROM customer").

FOR EACH cust-buf WHERE PROC-HANDLE = handle1:
    x = "SELECT state, state-name, region FROM state 
      WHERE state = ’" + cust-buf.state + "’".

  RUN STORED-PROC send-sql-statement handle2 = PROC-HANDLE (x).
  FOR EACH state-buf WHERE PROC-HANDLE = handle2:
    DISPLAY state-buf.
  END.
  CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle2.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1. 

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