Creation of External Stored Procedure using JAVA; NoSuchMethodException

Database
Teradata Employee

Creation of External Stored Procedure using JAVA; NoSuchMethodException

Dear All,

I am trying to create an external stored procedure using JAVA but keept getting NoSuchMethodException.

I am using windows 7 to compile and create the JAR file (client)

My teradata is running on SUSE Linux(server)

My java class is:

import java.io.InputStreamReader;
import java.io.IOException;

import java.net.URL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Region{

public static void callSP(int ID, String url)
{

final int ClobSize = 1024*1000;
final char[] buffer = new char[ClobSize];

try {
/* Establish default connection */
Connection conn = DriverManager.getConnection("jdbc:default:connection");

/* Prepare the command */
String sql = "INSERT INTO webtab(?,?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,ID);

/* Get the data */
URL urlConn = new URL(url);
InputStreamReader data = new InputStreamReader(urlConn.openStream());
int length = data.read(buffer);
stmt.setCharacterStream(2, data, length);

/* Insert the data */
stmt.execute();
stmt.close();
}
catch(SQLException sqlE) {

}
catch(IOException ioE) {

}
}

}

My compilation statement for java class is:

javac -classpath "C:\Program Files\Teradata\Tdat\LTDBMS\bin\javFnc.jar" Region.java

My jar file creation statement is:

javac -classpath "C:\Program Files\Teradata\Tdat\LTDBMS\bin\javFnc.jar" Region.java

My registration for JAR file in Teradata looks like this:

CALL SQLJ.INSTALL_JAR('CJ?C:\web.jar', 'Web_JAR', 0);

Up until here, everything works fine and my JAR gets registered in Teradata. But when I run the SQL for definition of External Stored Procedure on SQL Assistant:

DATABASE EXT_P;
REPLACE PROCEDURE ext_p.GetWebData()
LANGUAGE JAVA
MODIFIES SQL DATA
PARAMETER STYLE JAVA
EXTERNAL NAME 'Web_JAR:Region.callSP()';

I get the following error:

java.lang.NoSuchMethodException: Region.callSP()
at java.lang.Class.throwNoSuchMethodException(Class.java:274)
at java.lang.Class.getDeclaredMethod(Class.java:586)
at com.teradata.fnc.Loader.checkMethod(Loader.java:1108)
at com.teradata.fnc.JavaPtest.main(JavaPtest.java:342)

I have tried doing this without using the client and creating the jar file on the server itself but same scenrio repeats.

Can anyone help me out with this. The code above is from Teradata document "SQL External Routine Programming".

Looking forward.

Thanks

2 REPLIES
Teradata Employee

Re: Creation of External Stored Procedure using JAVA; NoSuchMethodException

Sorry, jar file creation statement is:

jar -cf web.jar Region.class

Teradata Employee

Re: Creation of External Stored Procedure using JAVA; NoSuchMethodException

Solution:

SQL definition for External Stored Procedure was wrong. I needed to include the parameters for procedure ext_p.GetWebData()

The correct syntax is:

DATABASE EXT_P;
REPLACE PROCEDURE ext_p.GetWebData(IN region INTEGER,
IN region_count VARCHAR(64))
LANGUAGE JAVA
MODIFIES SQL DATA
PARAMETER STYLE JAVA
EXTERNAL NAME 'Web_JAR_PP:Region.callSP';

:)