Teradata Java UDF

Database
Enthusiast

Teradata Java UDF

I have a java udf that basically just does a bunch of shows on each and every procedure and macro and then inserts those into a table. The jar is installed and my definition for the procedure is below. The problem i'm getting is i keep getting the error:

Executed as Single statement.  Failed [7825 : 38000] in UDF/XSP/UDM database.doWork: SQLSTATE 38U01: No suitable driver found for jdbc:default:connection

Elapsed time = 00:00:03.323

STATEMENT 1: Select Statement failed.

My definition for the procedure is:

REPLACE PROCEDURE fLoadRequestText(OUT counter INTEGER)
LANGUAGE JAVA
MODIFIES SQL DATA
PARAMETER STYLE JAVA
EXTERNAL NAME 'JarUDF2:fLoadRequestText2.doWork';

And my java code is:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.sql.PreparedStatement;

class Global {
public static int insert_count = 0;
}
class Runner implements Runnable {
private Thread t;
private String threadName;
private List<ArrayList> array;

Runner( String name, List<ArrayList> list){
threadName = name;
array = list;
System.out.println("Creating " + threadName );
}
public void run() {
System.out.println("Running " + threadName );
try {
String driver = "com.teradata.jdbc.TeraDriver";
Class.forName(driver).newInstance();
Connection con = DriverManager.getConnection( "jdbc:default:connection" );
Statement stmt = con.createStatement();
for (ArrayList temp:array){
String dbName = (String) temp.get(0);
String tblName = (String) temp.get(1);
String tblKind = (String) temp.get(2);
String sql = "";
if (tblKind.equalsIgnoreCase("p")){
sql = "Show procedure " + dbName +"."+ tblName + ";";

}
else if (tblKind.equalsIgnoreCase("v")){
sql = "Show macro " + dbName +"."+ tblName + ";";
}
else if (tblKind.equalsIgnoreCase("f")){
sql = "Show function " + dbName +"."+ tblName + ";";
}
String requestText = "";
try{
ResultSet rs = stmt.executeQuery(sql);
rs.next();
requestText = rs.getString(1).toLowerCase();
rs.close();
}
catch (Exception e){
/* do nothing*/
}

/*do an insert*/
int sizeRequest = requestText.length();
if (sizeRequest>=32000){
int mod = sizeRequest % 32000;
int rowNum = 1;
/*we need to get before the current word we are at*/
Pattern regex = Pattern.compile(".{1,31999}(?:\s|$)", Pattern.DOTALL);
Matcher regexMatcher = regex.matcher(requestText);
while (regexMatcher.find()) {
fLoadRequestText2.doInsert(con,dbName,tblName,tblKind,regexMatcher.group(),rowNum);
rowNum = rowNum++;
}
}
else{
fLoadRequestText2.doInsert(con,dbName,tblName,tblKind,requestText,1);
}
}
stmt.close();
con.close();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Thread " + threadName + " exiting.");
}

public void start ()
{
System.out.println("Starting " + threadName );
if (t == null)
{
t = new Thread (this, threadName);
t.start ();
}
}

}
public class fLoadRequestText2 {
/**
* Developed by shawn marks 9-20-2013
* @param input_param (String)
* @throws SQLException
*/
public static void doInsert(Connection con,String dbName,String tblName, String tblKind,String requestText, int rowNum) throws SQLException{
PreparedStatement pstmt = con.prepareStatement("INSERT INTO SYSDBA.definitions(Databasename, Objectname , TableKind, Rownumb,Definition) VALUES (?, ?, ?,?,?)");
pstmt.setString(0, dbName);
pstmt.setString(0, tblName);
pstmt.setString(0, tblKind);
pstmt.setInt(0, rowNum);
pstmt.setString(0, requestText);
pstmt.executeUpdate();
pstmt.close();
Global.insert_count++;
}
public static void doWork(int counter) throws SQLException{
String sql = "Select trim(databasename) as databasename,trim(tablename) as tableName,tablekind from dbc.tables where tablekind in ('p','m','f')";
try{
/* Establish default connection. */
String driver = "com.teradata.jdbc.TeraDriver";
Class.forName(driver).newInstance();
Connection con = DriverManager.getConnection( "jdbc:default:connection/TMODE=TERADATA,CHARSET=UTF8" );

/* Execute the statement to get all procedures and macros*/
Statement stmt = con.createStatement();
stmt.execute("locking table SYSDBA.definitions for write;");
ResultSet rs = stmt.executeQuery(sql);

/* Define dictionary for the macros and procedure and an array list that we use to return the result*/
Dictionary<String, String> Obj_dict = new Hashtable();
ArrayList <ArrayList> results = new ArrayList();

while(rs.next()){
ArrayList <String> temp = new ArrayList();
temp.add(rs.getString("databasename"));
temp.add(rs.getString("tablename"));
temp.add(rs.getString("tablekind"));
results.add(temp);
}
rs.close();

/* delete all from table*/
sql = "delete from SYSDBA.definitions;";
rs = con.createStatement().executeQuery(sql);
rs.close();

int numThreads = 30;

for (int i = 0; i< results.size();i=i+numThreads+1){
if (i+numThreads>results.size()){
Runner r = new Runner("Thread"+i,results.subList(i, results.size()-1));
r.start();
}
else{
Runner r = new Runner("Thread"+i,results.subList(i, i+numThreads));
r.start();
}

}
/* delete from the definitions where the objects are no longer in dbc.tables*/
sql = "delete from SYSDBA.definitions a where not exists (Select 1 from dbc.tables b where a.Objectname = b.tablename and a.databasename = b.databasename and a.tablekind=b.tablekind);";
rs = stmt.executeQuery(sql);
rs.close();
stmt.close();
con.close();
}
catch (Exception e) {
throw new SQLException(e.getMessage(),"38U01");
}
counter = Global.insert_count;
}
}
3 REPLIES
Enthusiast

Re: Teradata Java UDF

the method for the connection was taken from the SQL External Routine Programming PDF on page 283.

Enthusiast

Re: Teradata Java UDF

it also says here to do the same thing:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html

Java External Stored Procedures

The Java External Stored Procedure (XSP) portion of the ANSI SQL standard is provided by Teradata Database 12.0 or later, when used in conjunction with the Teradata JDBC Driver 12.0 or later. This includes the SQLJ database and tables, jar file installation, Java XSP definition, and Java XSP access to the JDBC default connection.

For more information, refer to SQL External Routine Programming.

Use Java XSPs in the following manner:

  • Compile the Java source outside of the database and place the resulting class or classes (the byte code) in a jar file

    Note:  Teradata Database 12.0 and 13.0 do not support Java Stored Procedures compiled with JDK 6.0. Only Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 are supported.

  • Register the resulting jar file with the database by calling an XSP named SQLJ.INSTALL_JAR
  • Create the JAVA XSP with its EXTERNAL NAME clause specifying the jar file and associated Java class

Once created, access the Java routine in the same manner as any XSP. Java XSPs can execute SQL code using the standard JDBC driver interface. Since the stored procedure is running on the database and is invoked from within a logged-on session, a connection URL of jdbc:default:connection should be used.

Teradata Employee

Re: Teradata Java UDF

First, this forum thread title is incorrect. This is not a Java UDF, this is a Java Stored Procedure.

JDBC/SQL access is supported for Java Stored Procedures, but is not supported for Java UDFs.

Since you have a Java Stored Procedure, you should be able to use the JDBC default connection for SQL access.

Some points:

- Don't do Class.forName on "com.teradata.jdbc.TeraDriver" in a Java Stored Procedure, because it's not needed.

- You are using multiple threads in your Java Stored Procedure. That's not recommended because you may interfere with the Teradata Database's workload management.

- You are using the TMODE connection parameter with the JDBC default connection, but that is not supported. The JDBC default connection inherits all the session attributes of the caller's session, and the session's transaction mode cannot be changed.

Here on Developer Exchange, we provide sample Java Stored Procedures, and sample programs to install them and call them.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html

If you haven't already done so, please download them and test them in your environment to verify whether they work for you.