16.20 - Executing SQL in Java External Stored Procedures - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Java external stored procedures can use JDBC to establish a default connection to the database and directly execute SQL.

When an SQL statement within a Java external stored procedure accesses structured UDT, Period, or ARRAY type data, the values are returned based on the UDTTransformsOff, PeriodStructOn and ArrayTransformsOff flag set in the options parcel for the request.

This section provides highlights on using JDBC to execute SQL in Java external stored procedures, and identifies differences between accessing Teradata Database from a Java external stored procedure and accessing the database from a Java application. For details on using Teradata Driver for the JDBC Interface to access the database, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

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 Limiting the Types of SQL Statements that a Procedure Executes.

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”.

Example: Establishing a Default Connection to Teradata Database

public class region {

   public static void getRegion(String[] data) throws SQLException
   {
      String sql = "SELECT Region FROM Sales WHERE ID = ";
      try {
         /* Establish default connection. */
         Connection con = 
            DriverManager.getConnection( "jdbc:default:connection " ); 
         /* Execute the statement */
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery( sql + data[0] );
         rs.next();
         data[0] = rs.getString("Region");
         stmt.close();
      }
      catch (Exception e) {
         throw new SQLException(e.getMessage(),"38U01");
      }
   }

   ...

}

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.

Example: External Stored Procedure That Reads SQL Data

The following statement specifies the READS SQL DATA data access clause because the getRegion() method that implements the GetRegion external stored procedure (see Example: Establishing a Default Connection to Teradata Database) executes a SELECT statement.

CREATE PROCEDURE GetRegion(INOUT Str VARCHAR(120))
   LANGUAGE JAVA
   READS SQL DATA
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'JarXSP:region.getRegion';

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 “CREATE TRIGGER” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

Restrictions

Java external stored procedures that execute SQL cannot be multithreaded.

Related Topics

FOR information on … SEE …
CREATE PROCEDURE Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
REPLACE PROCEDURE
CONTAINS SQL, READS SQL DATA, and MODIFIES SQL DATA data access clause
using the Teradata Driver for the JDBC Interface

Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

UDTTransformsOff, PeriodStructOn and ArrayTransformsOff flags Teradata Vantage™ - Data Types and Literals, B035-1143.