Progress
DataServer
for ODBC Guide
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 Progress 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 Progress applications cannot return Boolean types. Table 2–9 lists issues that occur when you pass other data types as parameters.
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 Progress 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 a deadlock might even occur
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:
Progress has statements and functions that allow you to use the return codes and the values of the output parameters. Table 2–10 lists these statements and functions.
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 "Stored Procedure Reference," for reference entries for the statements and functions described in Table 2–10.
Progress provides two techniques for accessing the results returned from the data source by the stored procedure. You can:
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 a Progress database—format it and use it for calculations.
The following sections describe how to do the following:
All of the following examples of how to run stored procedures in Progress use a stored procedure created in Transact-SQL for Sybase:
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 © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |