The SQL Data Access clause indicates whether a procedure can issue SQL statements and, if so, what type. An external procedure that contains "SQL statements" contains CLIv2, ODBC, or JDBC calls for those statements, not the standard SQL statements themselves.
You can specify the SQL DATA ACCESS and LANGUAGE clauses in any order.
- You can write external procedures to perform SQL requests using CLIv2 or JDBC in the same way a client application can make CLIv2 calls to perform SQL requests.
- You can use the CREATE PROCEDURE (External Form) and REPLACE PROCEDURE (External Form) statements to specify a data access clause that defines the specific capabilities of SQL calls made from the external procedure.
When a CLIv2 external procedure specifies the MODIFIES SQL DATA option for its data access clause, for example, the system knows that the procedure must be linked with an API, but it does not know which API the procedure must be linked with. To handle this, the EXTERNAL NAME clause must explicitly specify that the procedure is to link with the CLIv2 libraries.
Explicitly specifying a package to link with in the EXTERNAL NAME clause is not valid for external procedures that use JDBC. For those procedures, the determination is made based on the combination of the LANGUAGE and SQL Data Access clauses you specify for the CREATE PROCEDURE or REPLACE PROCEDURE request.
After it has this information, the system takes care of finding the proper libraries, linking with them, and setting up the proper environmental variables during execution.
To avoid link and execution conflicts with existing UDF libraries, the database maintains a dynamic library that is separate from the standard UDF library for each database that contains CLIv2- or Java-based external procedures.
SQL DATA ACCESS Options Supported by CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
| Option | Description |
|---|---|
| CONTAINS SQL | The procedure can run SQL calls using CLIv2 or JDBC. The procedure neither reads nor modifies SQL data in the database. An example is a procedure whose body consists of only control statements local to the procedure. If such a procedure attempts to read or modify SQL data, or calls a procedure that attempts to read or modify SQL data, the system raises the following SQLSTATE exception code. '2F004' - reading SQL-data not permitted |
| MODIFIES SQL DATA | The procedure can run all SQL calls that can validly be called from an SQL procedure using CLIv2 or JDBC. This is the default option for SQL procedures that do not specify an SQL Data Access clause when the procedure is defined. An example of such a statement is an UPDATE, INSERT or DELETE. |
| NO SQL | The procedure cannot run SQL calls. This is the default option for external SQL procedures. |
| READS SQL DATA | The procedure cannot run SQL calls using CLIv2 or JDBC that modify SQL data, but can make SQL calls that read SQL data. If such a procedure attempts to modify SQL data, or calls a procedure that modifies database data, the system raises the following SQLSTATE exception code. '2F002' -modifying SQL-data not permitted. |
LANGUAGE Clause
You can specify the LANGUAGE and SQL DATA ACCESS clauses in any order.
If you only have the object code for your external procedure body, you must make sure that it is completely compatible with C, C++, or Java object code, even if it was written using a different programming language.
External Data Access Clause
This optional clause defines the relationship between a UDF or external procedure and data that is external to the database.
- Whether the external routine can read or modify external data
- Whether the database redrives the request involving the function or procedure after a database restart
If Redrive protection is enabled, the system preserves responses for completed SQL requests and resubmits uncompleted requests when there is a database restart. For details about Redrive functionality, see Redrive Protection for Queries.
If you do not specify an External Data Access clause, the default is NO EXTERNAL DATA.
The following table explains the options for the External Data Access clause and how the database uses them for external routines.
| Option | Description |
|---|---|
| MODIFIES EXTERNAL DATA | The routine modifies data that is external to the database. The word "modify" includes delete, insert, and update operations. Following a database failure, the database does not redrive requests involving a function or external stored procedure defined with this option.
|
| NO EXTERNAL DATA | The routine does not access data that is external to the database. This is the default. |
| READS EXTERNAL DATA | The routine reads, but does not modify data that is external to the database. |
CREATE PROCEDURE and REPLACE PROCEDURE (External Form) SQL SECURITY Privilege Options
The following table defines the meanings of the SQL SECURITY options.
| Privilege Option | Description |
|---|---|
| CREATOR | Assign the privileges of the creator of the procedure regardless of its containing database or user. |
| DEFINER | Assign the privileges of the definer of the procedure. This is the default. |
| INVOKER | Assign the privileges possessed by the user at the top of the current execution stack. |
| OWNER | Assign the privileges owned by the owner of the procedure, which are the privileges possessed by its containing database or user. |
PARAMETER STYLE Clause
This optional clause specifies the parameter passing style to be used by the external procedure.
The SQL parameter passing style allows the code body to indicate NULL data. This cannot be done with the TD_GENERAL parameter passing style. If you do not specify a parameter style clause, then the system defaults to PARAMETER STYLE SQL. You must specify a parameter passing style of Java for all external routines written in Java. If the Java procedure must accept null arguments, then the EXTERNAL NAME clause must include the list of parameters and specify data types that map to Java objects (see External Java Reference Strings: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)).
- SQL data access clause
- External procedure name
You cannot specify the parameter style both places in the same external procedure definition statement. Only 1 parameter style clause is permitted per external procedure definition.