Establishing a Connection
Before a Java external stored procedure can execute SQL, it must establish a default connection to the database by passing "jdbc:default:connection" to the java.sql.DriverManager.getConnection() method.
The default connection sends SQL to the session that the external stored procedure is running in.
- CONTAINS SQL
- READS SQL DATA
- MODIFIES SQL DATA
If you omit one of the preceding data access clauses, or if the CREATE PROCEDURE or REPLACE PROCEDURE statement specifies the NO SQL clause, then the Teradata JDBC Driver will not be available in the class path for the Java external stored procedure. An attempt to call the DriverManager getConnection method to access the Teradata JDBC Driver results in an exception indicating “No suitable driver”.
Limiting the Types of SQL Statements that a Procedure Executes
The types of SQL statements that an external stored procedure (and stored procedure) can execute is restricted by the data access clause specified in the CREATE PROCEDURE or REPLACE PROCEDURE statement.
| IF the CREATE PROCEDURE or REPLACE PROCEDURE specifies … | THEN the external stored procedure … |
|---|---|
| CONTAINS SQL | cannot read or modify SQL data in the database, but can execute SQL control statements such as CALL. |
| READS SQL DATA | cannot modify SQL data, but can execute statements such as SELECT that read SQL data. |
| MODIFIES SQL DATA | can execute SQL statements that read or modify SQL data. |
- A Java external stored procedure where the CREATE PROCEDURE statement specifies a data access clause of CONTAINS SQL.
- A stored procedure where the CREATE PROCEDURE statement specifies a data access clause of MODIFIES SQL DATA.
If the external stored procedure calls the stored procedure, the stored procedure can only execute control statements because the caller is already restricted by the CONTAINS SQL data access clause.
Statements that a Procedure can Execute if Fired From a Trigger
An external stored procedure that is called when a trigger is fired can only execute SQL statements that are allowed as triggered action statements.
Attempts to execute other statements result in the database returning an exception to the external stored procedure. An external stored procedure that receives such an exception has an opportunity to post the error and close any external files or disconnect any connections it established. The only remaining course of action is for it to return.
| IF the external stored procedure receives an exception and … | THEN the triggering request is terminated and … |
|---|---|
| returns with its own error by throwing an SQLException and setting the SQLState field to its own exception code | the original failure condition will not be known to the caller of the external stored procedure. |
| does not throw an SQLException | the system returns the original failure to the caller. |
| attempts to submit another request | the system generates a 7836 error: The XSP db.name submitted a request subsequent to receiving a trigger fail message. |
For more details and a list of SQL statements that are allowed as triggered action statements, see the information about CREATE TRIGGER in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.
Restrictions
Java external stored procedures that execute SQL cannot be multithreaded.