If you do not use the buffer proc–text–buffer defined by the Progress ABL, you must define your own. Defining a view in the data source that can serve as a buffer allows you to retrieve database results in their original data types.
While a stored procedure can include multiple SQL statements, a buffer that you define holds the results of only a single SQL statement.
To define a buffer:
1. Define a view in the ODBC data source with :
The naming convention _BUFFER_buffername for all data sources except DB2 UDB. For DB2 UDB, use the naming convention P_BUFFER_buffername because DB2 UDB does not allow object names to start with an underscore character.
The same number of columns and data types that the stored procedure returns in the results set.
The columns in the order that the stored procedure returns them.
For example, to return two columns with two types of values, an integer and a character string, use an SQL utility to define the following view in the data source:
CREATE VIEW _BUFFER_custlist AS SELECT customer.custnum,
customer.name FROM customer WHERE 1 = 0
Notice that these views are defined to ensure that they never return any results. This indicates that you will not use the views as views, but as buffers. 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.
2. Update your schema image using the Update/Add Table Definitions DataServer utility. The utility adds the view to the list of accessible objects in the schema holder. The DataServer defines the view as a buffer that Progress can use.
This buffer defines two returned values for a stored procedure—an INTEGER and a CHARACTER value—in that order. If the data types do not match those returned by the stored procedure, the procedure returns more than two types of values, or returns the values in a different order than you specified, you receive a run-time error.
The easiest way to create a buffer that accepts data from stored procedures is to use the text of the SQL SELECT statement from the stored procedure. This ensures that you define your data types correctly and in the correct order. Use a native process such as sp_helptext to view the stored procedure from Sybase, or view procedures in the system tables appropriate for your data source.
The next example does not use the supplied buffer. Instead, it defines buffers by creating views in the data source, using the following syntax:
Syntax
CREATE VIEW _BUFFER_buffer-name
These are examples of the views, created in your ODBC data source, that you can use as buffers to store the results from the stored procedure pcust:
CREATE VIEW _BUFFER_pcust_orders AS SELECT customer.custnum,
customer.name, order.ordernum FROM customer, order WHERE 1 = 0
CREATE VIEW _BUFFER_pcust_states AS SELECT custnum, state.st
FROM customer, state WHERE 1 = 0
The following ABL procedure shows the results of the stored procedure pcust being written into the new buffers pcust_orders and pcust_states:
/* Typed buffers */
RUN STORED-PROC pcust (20, OUTPUT 0, OUTPUT 0).
FOR EACH pcust_orders:
DISPLAY pcust_orders.
END.
FOR EACH pcust_states:
DISPLAY pcust_states.
END.
CLOSE STORED-PROC pcust.
DISPLAY pcust.orders pcust.states.
Because two different buffers have been defined, the returned values maintain their data types instead of being converted to character strings and stored in the ABL-defined buffer proc–text–buffer. You can then use the returned values in calculations without first converting them back to their original data types. In addition, the two separate buffers make your output look cleaner, allowing OpenEdge to build a new default frame for the two different types of output. Reading your results into an explicitly defined buffer also allows you to manipulate the data just as you would manipulate data from an OpenEdge database; for example, with Frame phrases and FORM statements.
The next example accesses the stored procedure pcust twice; procedure handles (through the PROC–HANDLE function) identify the different results from your data source:
/* Procedure handles */
DEFINE VARIABLE handel1 AS INTEGER NO-UNDO.
DEFINE VARIABLE handle2 AS INTEGER NO-UNDO.
RUN STORED-PROCEDURE pcust handle1 =
PROC-HANDLE (20, OUTPUT 0, OUTPUT 0).
RUN STORED-PROCEDURE pcust handle2 =
PROC-HANDLE (20, OUTPUT 0, OUTPUT 0).
FOR EACH pcust_orders WHERE PROC-HANDLE = handle1:
DISPLAY pcust_orders.
END.
FOR EACH pcust_states WHERE PROC-HANDLE = handle1:
DISPLAY pcust_states.
END.
FOR EACH pcust_orders WHERE PROC-HANDLE = handle2:
DISPLAY pcust_orders.
END.
FOR EACH pcust_states WHERE PROC-HANDLE = handle2:
DISPLAY pcust_states.
END.
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle1.
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle2.
In this example, the results look the same as in the previous example. However, because you are running a stored procedure twice, ABL uses the procedure handles to identify the different instances. If you run more than one stored procedure in your application, you must explicitly define procedure handles for each one.
The next example shows how to use standard ABL syntax to join the results of the stored procedures with other tables in the database:
/* Join with procedure results */
RUN STORED-PROC pcust (20, OUTPUT 0, OUTPUT 0).
FOR EACH pcust_orders, EACH orderline
WHERE pcust_orders.ordernum = orderline.ordernum:
DISPLAY orderline.ordernum orderline.itemnum.
END.
FOR EACH pcust_states:
DISPLAY pcust_states.
END.
CLOSE STORED-PROC pcust.
This example joins the order information returned from the stored procedure with the order–line information in the same database.
The naming convention _BUFFER_buffername for all data sources except DB2 UDB. For DB2 UDB, use the naming convention P_BUFFER_buffername because DB2 UDB does not allow object names to start with an underscore character.