| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect for AS/400 |
![[-]](/dwicons/i_colpse.gif)
| Chapter 14 Using AS/400 Stored Procedures |
Chapter 14
This chapter describes how to create and implement stored procedures within the AS/400 and covers the following topics:
An AS/400 stored procedure can be a SQL stored procedure (SSP) or an external stored procedure (ESP).
Unless specified otherwise in this chapter, both types of stored procedures are called "AS/400 stored procedures."
Advantages to using AS/400 stored procedures include the following:
Executing static rather than dynamic SQL, which improves performance
Providing a controlled method for a client application to perform sensitive operations, such as update
Minimizing duplicate efforts in SQL statement generation
Creating a stored procedure with a longer life than a prepared called statement. Creating an AS/400 stored procedure creates an AS/400 package, which exists until you drop the package.
Read the following sections for more information about each type of stored procedure.
A SQL stored procedure consists of a single SQL statement (such as insert, update, delete, grant) that is compiled and bound at the database. Once you create a SQL stored procedure, any client application can use it.
External stored procedures apply only to installations using OS/400 V3R1or higher.
An external stored procedure is a statically bound SQL call statement. It allows a user to make a call from within the AS/400 interface to a program that is outside of the database. The advantage of using an external stored procedure is that it is the only way to retrieve return argument values from a called AS/400 program.
An external stored procedure can return succeed or fail status to the client application by signalling its invoking program QSQCALL. For details, refer to the IBM AS/400 DB2/400 SQL Programming manual.
An external stored procedure differs from a SQL stored procedure as follows:
External stored procedure names have an "x_" prefix.
External stored procedures return argument values.
The SQL statement associated with the external stored procedure name must be a call operation.
For information about the call statement and external stored procedures, see "Using the call Statement" .
To create an AS/400 stored procedure, perform the following tasks:
Be sure the NULLID collection exists.
Build and execute the create procedure statement.
Grant authorization on the stored procedure package.
All tables that the AS/400 stored procedure refers to should be qualified with the collection name. If a table is not qualified with the collection name, AS/400 SQL uses the user profile name of the AS/400 job (connection).
The access service library requires a naming convention to distinguish between SQL stored procedures that return a result set, those that do not, and external stored procedures:
The first letter of a SQL stored procedure that returns a result set must be "s". For example:
create procedure sauthors as select * from authors
If the SQL stored procedure does not return a result set, the first letter of the stored procedure name must be something other than "s" or "x_".
External stored procedure names must have an "x_" prefix. For example:
create procedure x_excrule as call rulelib.rulepgm
An AS/400 stored procedure can contain variables, so that arguments can be passed to it when it runs. A argument can be used for any data value in the SQL statement but cannot be used for any table or column name.
The syntax for AS/400 stored procedures is as follows:
create procedureprocname arglist optionlist as sqlstatement
where:
procname is the name of the stored procedure
arglist is a set of arguments or argument specifications separated by commas
optionlist is a set of options that affect the bind-time processing of the stored procedure package at the database
sqlstatement is the AS/400 stored procedure SQL statement
Arguments in the arglist must be in the following format:
@parmname argtype, ...
or
&parmname argtype, ...
where:
parmname is an arbitrary name for the argument. It can contain a maximum of 31 characters.
argtype is any of the following datatypes: CHAR, VARCHAR, LONG VARCHAR, BINARY, VARBINARY, TEXT, INT, SMALLINT, FLOAT, REAL, DECIMAL, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC.
Formats for argtype are as follows:
CHAR(length) VARCHAR(length) LONG VARCHAR(length)DECIMAL(precision,scale)
A maximum of 50 arguments can be in arglist.
There must be a one-to-one correspondence between the number of arguments used in the SQL statement of the AS/400 stored procedure and the number of arguments in the arglist. The arguments must occur in the same order.
If an argument is required twice in the AS/400 stored procedure, it must appear twice in arglist.
An argument must be declared as a datatype consistent with the datatype of the relevant column as follows:
Any argument declared as numeric can be used with any numeric column.
Any argument declared as character can be used with any character column. (TEXT is handled by the access service as CHAR or VARCHAR.)
Any argument declared as a character type can be used with a DATE, TIME, or TIMESTAMP column.
It is best to describe the arguments exactly as they appear in the corresponding column. In some cases, this can allow the AS/400 DBMS to use the SQL statement more efficiently.
The optionlist allows a client to specify information about the stored procedure. The client can specify options only when SQL transformation is in Passthrough mode. Sybase mode does not allow options.
A client can specify the following options:
verify={req|opt} instructs the access service to verify the following:
verify=req instructs the access service to return an error message if a referenced object does not exist or the proper authorization is lacking. The create procedure statement fails when verify=req.
verify=opt instructs the access service to return a warning message if a referenced object is missing or if proper authorization is lacking. However, the create procedure statement succeeds if the objects exist and the proper authorizations are in place before the procedure executes. verify=opt is the default.
owner=userid instructs the access service to use the specified userid as the package owner. Including a user ID does not affect creation or execution of the AS/400 stored procedure package unless the security setting affects it. The current user's login is the default.
isolv={cs|ur|no} specifies the isolation level for binding the AS/400 stored procedure package (which is created when a SQL stored procedure is created).
isolv=cs specifies an isolation level of cursor stability. This means that all tables referenced by the SQL statement must be journaled. isolv=cs is the default.
isolv=ur specifies an isolation level of uncommitted read. ur specifies that the package is bound with the CHG isolation level. This means that any referenced tables in a select statement do not need to be journaled.
isolv=no specifies an isolation level of NONE, which means that journaling is not required for any objects in this SQL statement.
To include options in a create procedure statement, use a format as follows:
verify=req, isolv=ur, owner=userid
Consider the following guidelines for a create procedure statement:
Separate each option and its value with a comma.
Enclose the value for owner in single or double quotes:
If the value is enclosed in quotes, the access service passes the value exactly as it is written to the AS/400.
If the value is not enclosed in quotes, the access service converts the value to uppercase.
If the name you supply for procname already exists, the create procedure statement fails. Either drop the conflicting procedure or use a different name for the new procedure.
This section describes the interaction between AS/400 stored procedures and catalog stored procedures (CSPs). It also contains information about creating an AS/400 stored procedure and CSP tables.
When the create procedure statement executes, the following events occur:
A package for the AS/400 stored procedure is created in the NULLID collection of the AS/400. The package has the same name as the stored procedure.
AS/400 verifies that the datatype declared for each argument is consistent with the datatype of the relevant column.
One of the following happens, depending on whether or not the CSP tables NULLID.SYSPROCS and NULLID.SYSPROCCOL were created:
If the CSP tables were created, the access service updates the tables with information about the new stored procedure. One row is inserted into NULLID.SYSPROCS for the stored procedure, and one row is inserted into NULLID.SYSPROCCOL for each argument declared in the stored procedure. These tables are created by the CSP.SQL script that comes with the access service library.
If the tables were not created, the insert fails because rows cannot be inserted into tables that do not exist. However, because the create procedure request precedes the insert, the request is not rolled back when a failure occurs.
To query these tables about existing AS/400 stored procedures, use the following CSPs:
sp_stored_procedures
sp_sproc_columns procname
where procname is the name of the stored procedure.
For more information about sp_stored_procedures, see sp_stored_procedures .
For more information about sp_sproc_columns, see sp_sproc_columns .
To drop an AS/400 stored procedure, issue the drop procedure command as follows:
drop procedure procname
where
procname is the name of the procedure to drop.
The access service library removes all relevant rows from the NULLID.SYSPROCS and NULLID.SYSPROCCOL CSP tables.
After a create procedure statement executes successfully, the owner must grant execute authorization on the AS/400 stored procedure package to all users who will run that AS/400 stored procedure.
For example, if the AS/400 stored procedure executes an insert against the table XYZ, the owner must have insert authorization with grant option on table XYZ. However, the owner is not required to grant insert authorization on the table to each user of the AS/400 stored procedure; execute authorization is sufficient.
The following statement authorizes all users to execute an AS/400 stored procedure named ptest1:
grant execute on ptest1 to public
The syntax for executing an AS/400 stored procedure depends upon the SQL transformation mode in effect:
In Passthrough mode, a client application executes an AS/400 stored procedure by issuing the following statement:
use procedure procname arglist
In Sybase mode, a client application executes an AS/400 stored procedure by issuing one of the following statements:
exec procname arglist
execute procname arglist
In the above transformation level examples, the arglist is a list of argument values separated by commas. These argument values must be specified in the exact order they are specified in the SQL statement contained in the AS/400 stored procedure. However, this is not necessarily the same order that was used in the create procedure statement.
Be sure to use a comma between argument values. For example:
use procedure y
1,2,'CHAR'
Another example, in which the first two arguments are null:
use procedure y
,,'FRED'
The datatypes of all argument values must be consistent with the datatypes of the relevant columns as follows:
Any argument declared as numeric can be used with any numeric column.
Any argument declared as character can be used with any character column. (TEXT is handled by the access service as CHAR or VARCHAR.)
Any argument declared as character can be used with any DATE, TIME, or TIMESTAMP column, although the value must be in the proper format for the specific type.
Delimit characters in one of two ways:
Enclose the value in single or double quotes.
In Passthrough mode only, you can enclose the value in one of the following special delimiters: ! % () * / : <> ? \ | ' {} ~
To use special delimiters, follow these guidelines:
Be sure to use the same character before and after the value.
Do not enclose numeric arguments in quotes or any special delimiter.
Specify NULL values in one of two ways:
Use either NULL or null as the value.
Leave the argument out of the list. This works only when more than one argument exists for the procedure.
The following rules apply to AS/400 stored procedures:
Binary values can be used in character arguments. However, the 0 value must not occur because the access service sends all character arguments to the AS/400 as null-terminated strings. Therefore, 0 prematurely ends the string, resulting in an error.
The access service does not support escape sequences. For example, an access service sends the value ABCD\n as a 6-character string, not as ABCD followed by a linefeed.
Character values sent in the use procedure statement can be longer than declared in the create procedure statement. The access service truncates character values longer than those allowed by the target column but returns no error message. (The error message function is controlled by AS/400.) CHAR and VARCHAR columns can contain 32,766 bytes. However, the access service cannot handle requests larger than 32,000 bytes.
If a numeric value has a larger scale than its target column, the access service truncates the argument and does not record an error.
When a client issues a select as an AS/400 stored procedure, column names are not available. If the client asks for column names, the access service returns dummy column names, such as COL1 and COL2.
For external stored procedures, arguments are returned as a single row result set. Each argument is represented as a column, named parm1, parm2, parm3 and so on.
To access an AS/400 stored procedure with a SQL language event, use the call statement:
callprocname
where:
call is the keyword for accessing an AS/400 external stored procedure
procname is the name of the procedure defined on the AS/400
If the AS/400 program has any parameter definitions, the arguments needed to match the stored procedure interface must be declared directly in the call statement. In the following example, in library fred, program test1 accepts two arguments, a fixed character and an integer type:
call fred.test1 ('check', 123)To reuse the call statement and include argument markers, use a dynamically prepared statement, inserting the question mark (?) for argument placeholders:
call fred.test1 (?, ?)
When the dynamically prepared statement executes, the access service substitutes the arguments into the argument marker positions.
In a dynamically prepared statement, all arguments are input only. Also, when a commit or rollback occurs and causes the statement to drop, you must prepare the statement again.
An AS/400 external stored procedure uses the call statement from within the AS/400 interface to call a program outside of the database. To access an AS/400 stored procedure using an external stored procedure, the client application must use a create procedure language event to define the external stored procedure, as follows:
create procedure x_myproc @C char(8) @I int as call fred.test1 (@C,@I)
Note that the "x_" prefix identifies the name of the external stored procedure.
The following rules apply to arguments in call statements:
Character strings must be quoted, and numeric strings must not be quoted.
Because the external stored procedure declares the interface to the AS/400 program or stored procedure, the arguments in the arglist must have the same datatype as the argument definitions of the stored procedure.
The call language event can be used only against programs that have input argument definitions. Also, only call programs with input argument definitions allow parameter markers.
Use create procedure and use procedure x_procname statements to call programs that return data.
Be sure the program can handle the datatypes declared in the define procedure statement. For example, RPG programs cannot handle VARCHAR datatypes, but COBOL programs can.
|
|