Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

> ## Defining native stored procedures to Progress

> 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 Progress 4GL 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, 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, i.e., 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 your Progress 4GL. The schema holder will only allow one of the procedure signatures to be defined per procedure name.

> Stored procedures called from within the Progress 4GL cannot return Boolean types. Table 2–8 lists issues that occur when you pass other data types as parameters.

> Table 2–8: Argument data types for stored procedures
> Progress | ODBC data source
> --- | ---
> `DECIMAL FLOAT INTEGER` | The DataServer represents all three data types as the Progress `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.

> Note: In your DB2 stored procedure, be sure to code parameters passed into your stored procedure as variable, not fixed, character strings (with a 2-byte length prefixing the character value).

> You run stored procedures from within Progress procedures by using the 4GL `RUN` `STORED–PROCEDURE` statement. A stored procedure that you run with this statement can return three types of values:

> - An integer return code, which could be a success code or a value returned by the stored procedure (defined by the data source).

> - Values of output parameters that you define when you create the procedure.

> - Results from the database.

> Progress has statements and functions that allow you to use the return codes and the values of the output parameters. Table 2–9 lists these statements and functions.

> Table 2–9: Returning values from stored procedures
> Progress 4GL | Description
> --- | ---
> `CLOSE STORED–PROCEDURE` statement | Retrieves the values from the output parameters you defined for the stored procedure and tells Progress 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.

> See Appendix B, "Stored Procedure Reference," for reference entries for the statements and functions described in Table 2–9.

> The Progress 4GL provides two techniques for accessing the results returned from the data source by the stored procedure. You can:

> - Use the `proc-text-buffer` that Progress supplies for the rows of results.

> - Define a buffer for the rows of results.

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

> The following sections describe how to do the following:

> - Run stored procedures.

> - Retrieve return codes.

> - Retrieve output parameter values.

> - Define and use buffers to retrieve database results.

> All of the following examples of how to run stored procedures in Progress use a stored procedure created in Transact-SQL for Sybase:

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

> Note: For other ODBC data sources, use your vendor-specific SQL syntax.

> 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 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 database, the procedure name will be imported into your schema holder as all upper case characters because the DB2 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.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095