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

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.

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

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