17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

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.

To successfully call DriverManager.getConnection() to access the Teradata JDBC Driver, the CREATE PROCEDURE or REPLACE PROCEDURE statement must specify one of the following data access clauses:
  • CONTAINS SQL
  • READS SQL DATA
  • MODIFIES SQL DATA

For details on these data access clauses, see dsq1593709706977.html#fuh1472240849902__CIHCFJEG_xreftarget.

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.
An external stored procedure or stored procedure can only execute statements corresponding to the access clause of the most restrictive procedure that called it. For example, consider the following:
  • 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.