JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Extensibility
Enthusiast

JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Hi,

I am trying to create a JAVA UDF in TD 13.10. In this UDF, I am trying to connect to Teradata Database and read column for tables. When I execute the Java Class and function from eclipse, I am able to establish connection to TD and execute the query and process the result. However, when I deploy the function in TD and execute it from TD environment, I am not able to execute the function and instead I am getting SQL Exception 38000 (java null pointer exception).
I changed the DDL for Java UDF in TD and included the clause READS SQL Data instead of NO SQL (not sure its supported with JAVA UDF - I read that it works for JAVA procedures) but even then I was not able to get through.
Below is the code in the Java method which is being called as Java UDF in TD:

try {
conn=DriverManager.getConnection("jdbc:default:connection");
query="My Query";

PreparedStatement stmt=conn.prepareStatement(query);
ResultSet rs=stmt.executeQuery();
while(rs.next()) {
TS2=rs.getTimestamp(1);
}

}
catch (Exception sqle)
{

throw new SQLException(sqle.getMessage()) ;

}

Can someone please help us out on this. Is it even possible to execute SQL statements in JAVA UDF or does it only work with Java procedures ?

Thanks,
Ajay

6 REPLIES
Teradata Employee

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Java UDFs do not have access to the JDBC default connection, so you cannot execute SQL statements using the JDBC default connection. (C/C++ UDFs have the same limitation.)

You are correct that the JDBC default connection is only available for Java Stored Procedures.

Enthusiast

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Hi Tom,

Thanks for your reply. So if it is not possible to execute SQL statements using JDBC default connection in UDF, is there any work around available. We were creating UDF because we wanted to use it in Select clause i.e. Select function_name(P1,P2). But if we cannot execute SQL statement even in Java UDF then are there any alternatives ? Creating a Java Stored procedure would defeat the purpose as we won't be able to call it in Select clause.

Thanks,
Ajay
Teradata Employee

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Speaking generally, you may be able to use a table operator instead of a UDF. A table operator is a kind of UDF, so it is still subject to the same limitation of not being able to use the JDBC default connection. However, your application may be able to execute a preliminary insert...select to populate a volatile table or global temp table, and then a table operator can utilize those rows.

In order to be able to suggest a workaround, I would need to understand more about what you are trying to accomplish. What is the intended purpose of your Java UDF? And what information did you intend the Java UDF to query using the JDBC default connection?

Enthusiast

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Business users want to have flexibility to execute some simple conversions at report level. So the need to UDF. Basically the UDF will accept 2 parameters (2 columns for table) and we need to transform parameter 1 based on value of parameter 2 and return the output. In order to complete the transformation, we have a need to get a 3rd value from a table based on the value of parameter 2. Hence the need for select clause. I can move all the transformation part to Java Code but unfortunately, we need the value from table based parameter 2.

I suggested that we do this in pre-calculated table form but the problem is that the parameter values can vary depending upon user and region. Hence the need for function. This basically also rules out Stored procedure because again the users are expecting the output on row by row basis in their reports.

Thanks,
Ajay

Teradata Employee

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Regarding: "I suggested that we do this in pre-calculated table form but the problem is that the parameter values can vary depending upon user and region."

That approach may be the one you need to use. You can select user-specific values from a table by comparing with the USER built-in system function. Other attributes like region can perhaps be designated with a Query Band value, and you can also reference those in WHERE-clause conditions.

Enthusiast

Re: JAVA UDF in TD 13.10 With SQL Query - not able to establish connection with TD

Hi Tom,

We explored this option of using a configuration table but the problem is that users want to use this function in report. If we put it in a table, then it will have to to be joined in framework and here is problem 2 i.e. the parameter columns that user will select can vary. It could be col1 or col2 from table 1 or col x from table 2 which user decides to pass. So we won't be able to join the table in reporting tool framework and then we will have to introduce user queries there as well. Pre-calculated table will then limit the users to use the data that has been prepared by ETL and they won't have the flexibility to build some new transformation directly in reports. The users will have to come back to ETL team if they want new params and transformations.

Thanks,
Ajay