Database Procedure Management
The gateway has three SQL statements that allow the management and execution of gateway database procedures:
• REGISTER PROCEDURE
• REMOVE PROCEDURE
• EXECUTE PROCEDURE
The first two statements are used to populate the gateway standard catalogs with metadata about the procedures that the gateway user is allowed to invoke. The last statement is used by a client application to execute a gateway database procedure.
Note: Because database procedures are not meant to be dynamic in real world applications, for performance reasons, the gateway caches database procedure information during the first invocation of the procedure. Therefore, if you change registered information using the Register Procedure command after the procedure was called in the current gateway session, you must synchronize the new procedure definition or registered values by exiting and restarting the application that accesses the gateway (such as Ingres OpenROAD). Doing so will ensure that the changes available when the Execute Procedure call is performed.
Register Procedure
To register a gateway database procedure, the following criteria must be met:
• The stored procedure must already exist.
• The user that is registering the procedure must have the authority to invoke the procedure.
• The user that is registering the procedure must have INSERT privileges for IIGWPROCEDURE, IIGWPROCPARAMS, and IIGWPROCDEFAULTS.
The syntax of the register procedure statement is as follows:
REGISTER PROCEDURE [schema_name].proc_name
(param_name format qualifiers
[param_name format qualifiers, …])
AS IMPORT
FROM 'source'
schema_name
(Optional). Specifies the schema name of the gateway owner of the gateway database procedure. This parameter must be a legal OpenSQL identifier. If not specified, the current user is the database procedure owner. If the current user is not the database procedure owner, the gateway DBA is the database procedure owner. This parameter is optional.
proc_name
Specifies the OpenSQL name for the procedure being registered. This name is used in the execute procedure.
param_name
Specifies the parameter name. This must be a legal OpenSQL identifier. Its maximum length is limited by the host DBMS maximum length. For more information, see
Object Name Length.
format
Specifies the data type and length of param_name. This must be an OpenSQL data type supported by the gateway. The allowed data types are:
CHAR
DATE
DECIMAL
FLOAT
REAL
INTEGER
SMALLINT
VARCHAR
qualifiers
Specifies the optional parameter qualifiers. The allowed values are:
NOT NULL
NOT DEFAULT
WITH NULL
WITH DEFAULT default_value
The default_value can have one of following values:
• NULL
• SYSTEM
• CURRENT_DATE
• CURRENT_TIMESTAMP
• Literal value appropriate to the data type specified in the format parameter
• BYREF
• AS result_name
'source'
Specifies the name or identification of the host DBMS stored procedure. This value can be qualified by an explicit schema name, or it can be unqualified. If it is not qualified, the schema name of the user registering the procedure is the owner of the stored procedure, and the appropriate grants have been given for other users to invoke this procedure.
Example: register procedure statement
register procedure gwnewproc
(int_col integer not null,
float_col float not null,
date_col date not null
char_col char(10))
as import from 'dbnewproc';
In this example, the gateway uses the name gwnewproc to invoke the stored procedure called dbnewproc by the native DBMS.
Remove Procedure
To remove a gateway database procedure, the following criteria must be met:
• The procedure must have been successfully registered.
• The user that is registering the procedure must have DELETE privileges for IIGWPROCEDURE, IIGWPROCPARAMS, and IIGWPROCDEFAULTS.
The syntax of the remove procedure statement is as follows:
REMOVE PROCEDURE [schema_name].proc_name
When the procedure is removed, only references to the gateway database procedure in the gateway catalogs are removed.
Execute Procedure
To execute a gateway database procedure, the following criteria must be met:
• The procedure must have been successfully registered:
– In the current gateway session before the procedure is first called, or
– Prior to the current gateway session
• Any change to a procedure definition or registered information must also be made:
– In the current gateway session before the procedure is first called, or
– Prior to the current gateway session
• The user that is executing the procedure must have the authority to invoke the procedure.
• The user that is registering the procedure must have SELECT permissions for IIGWPROCEDURE, IIGWPROCPARAMS, and IIGWPROCDEFAULTS.
The syntax of the execute procedure statement is as follows:
EXECUTE PROCEDURE [schema_name].proc_name
(param_name = {param_value | BYREF(:param_value)}
[param_name = {param_value | BYREF(:param_value)}, …]);
schema_name
(Optional). Specifies the schema name of the gateway owner of the gateway database procedure. This parameter must be a legal Open SQL identifier. If not specified, the current user is the database procedure owner. If the current user is not the database procedure owner, the gateway DBA is the database procedure owner.
proc_name
Specifies the OpenSQL name for the procedure being executed. This name is used in the execute procedure statement.
param_value
Specifies a numeric, string literal, or host variable that is compatible with the type of parameter that was specified for this parameter at the time the procedure was registered.
If the client is OpenROAD, ESQL, ODBC, JDBC, or OpenAPI, BYREF support is allowed. BYREF support allows the gateway to return a value in the parameter specified to the client. If BYREF support is requested, this parameter will have the syntax BYREF(:host_var).
Example: execute procedure statement
execute procedure gwnewproc
(int_col = 16000,
float_col = 26.654,
date_col = '10-26-1998'
char_col = 'charvalue');
In this example, the gateway uses the name gwnewproc to invoke the stored procedure called dbnewproc by the native DBMS.