Defining native stored procedures to ABL

The first time you run a stored procedure, the data-source management system creates an execution plan for it and stores the plan in the database. The next time you run the stored procedure, it runs as a precompiled procedure. This makes access to the database much quicker and more efficient than when you access it with new queries each time.

The DataServer allows you to use the ABL to run stored procedures written for an ODBC-compliant data source. The procedures must be defined according to the rules of the particular data source. All stored procedures are executed on the server machine by the native data source. See the documentation for your data source to determine if it supports stored procedures.

When you create or update your schema image, the stored procedures, functions, and packages appear in the list of accessible objects along with tables, view, and sequences. Progress allows you to run the stored procedures that you create in data sources from within Progress procedures. If your data source supports stored procedures, see your data-source documentation for complete information about creating and using stored procedures.

Note: In DB2 UDB, the same stored procedure can be defined with different signatures, that is, the same procedure name can be used for two different procedure definitions, called signatures, with different parameter construction. If you pull a stored procedure definition into your schema holder, which has overloaded functions, that is, multiple signatures, all parameters defined to all signatures of that procedure will be pulled into the same table definition in your schema. You must use the Data Dictionary to manually remove parameters that are pulled from signatures you did not intend to use in ABL. The schema holder will only allow one of the procedure signatures to be defined per procedure name.

Table 9. Argument data types for stored procedures
Progress	ODBC data source
DECIMAL FLOAT INTEGER	The DataServer represents all three data types as the OpenEdge INTEGER data type in the schema image. To preserve the scale and precision of these data types, you must manually update the information in the schema image for these parameters. Use the Data Dictionary to update the data type and format information in the field property sheet for the parameter.
CHAR	The data source represents this type as a VARCHAR parameter. Its size cannot exceed the VARCHAR size limit for the associated data source. If they exceed this limit, they cause an ODBC data-source error.
DATE	If you pass a DATE data type as an input parameter and use it in an equality test, the test might fail. In this case, use the trunc function in the stored procedure. For example:procedure x_date (indate in date, outdate out date) as begin select date_terminate into outdate from datetbl where trunc(hire_date) = trunc(indate); end;

If you are running several stored procedures, run them serially and process all the results from one stored procedure before you run a second one. The DataServer allows only one active request for running a stored procedure. However, you can process results from several stored procedures concurrently if you specify the DataServer startup parameter (-Dsrv PRGRS_PROC_TRAN) when you start your client session. When you run stored procedures concurrently, the DataServer uses one connection to the data source per procedure. If the stored procedures attempt to update the same record from a single client's requests, the connections could block each other or possibly create a deadlock.

Table 10. Returning values from stored procedures
ABL	Description
CLOSE STORED–PROCEDURE statement	Retrieves the values from the output parameters you defined for the stored procedure and tells ABL that the stored procedure has ended
PROC–HANDLE function	Allows you to specify a handle to identify a stored procedure
PROC–STATUS function	Reads the return value
RUN STORED–PROCEDURE statement	Executes a stored procedure

Note that you can substitute the abbreviations CLOSE STORED–PROC and RUN STORED–PROC for the full names CLOSE STORED–PROCEDURE and RUN STORED–PROCEDURE, respectively. The remainder of this guide generally uses the abbreviated form.

After you read the values into the buffer, you can operate on them in a variety of ways. You can use the database data just as you would use information from an OpenEdge database—format it and use it for calculations.

CREATE PROCEDURE pcust (@num INT, @orders INT OUT, @states INT OUT) AS
SELECT customer.custnum, customer.name, order.ordernum FROM customer, order
WHERE customer.custnum = order.custnum AND customer.custnum > @num
SELECT @orders = @@rowcount
SELECT customer.custnum, state.st FROM customer, state
WHERE customer.st = state.st AND customer.custnum > @num
SELECT @states = @@rowcount
RETURN 0
GO

This Transact-SQL code creates the stored procedure pcust and defines three parameters: num, which is an input parameter, and orders and states, which are output parameters. The procedure returns values for the output parameters to the caller after processing the results of the pcust SELECT statements. You can think of output parameters as temporary fields; that is, you can access the data in these columns using the standard notation of tablename.fieldname. (Note that although pcust is a stored procedure, its syntax is that of a table and it is stored in a table definition.) For example, you can access the data in the orders and states fields by specifying pcust.orders and pcust.states. All the parameters in the example have an integer data type.

Note: DB2 UDB uses external modules compiled in 3GL languages (for example, C) to implement stored procedures. Because these languages are often case sensitive, you must specify in your CREATE PROCEDURE statement a case-sensitive procedure name that matches the module name exported from the 3GL stored procedure. In the previous example, the procedure name pcust, which is stored in your dictionary as a file name that represents the stored procedure to the schema holder, is case sensitive and must match exactly the case-sensitive module name exported from your DLL.

If you perform a schema pull to retrieve a stored procedure from an existing DB2 UDB database, the procedure name will be imported into your schema holder as all uppercase characters because the DB2 UDB server is not case sensitive and rolls all object names to uppercase. After inputting the stored procedure, you should go into the Data Dictionary and modify the procedure name to the appropriate case that matches your 3GL module name. Note that the DataServer for ODBC caches the schema, which means you will need to disconnect from the schema holder and reconnect for your procedure name change to take effect.