I have the below UDF which tries to connect to vertica using JDBC...
Code: Select all
package com.testudf.udfex;
import java.sql.*;
import com.vertica.sdk.*;
public class TokenFactory extends TransformFunctionFactory{
@Override
public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes,ColumnTypes returnType){
argTypes.addVarchar();
returnType.addVarchar();
}
@Override
public void getReturnType(ServerInterface srvInterface, SizedColumnTypes inputTypes,SizedColumnTypes outputTypes){
outputTypes.addVarchar(inputTypes.getColumnType(0).getStringLength(),"Company");
}
@Override
public TransformFunction createTransformFunction(ServerInterface srvInterface) {
return new TokenizeString();
}
public class TokenizeString extends TransformFunction {
@Override
public void processPartition(ServerInterface srvInterface,PartitionReader inputReader,PartitionWriter outputWriter) throws UdfException,DestroyInvocation {
Connection mConn;
ResultSet rs=null;
String Comp =inputReader.getString(0);
Integer NUM =null;
try
{
Class.forName("com.vertica.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.err.println("Could not connect to the database.\n");
e.printStackTrace();
}
try {
mConn=DriverManager.getConnection("jdbc:vertica://host:5433/database", "user", "password");
mConn.setAutoCommit(false);
PreparedStatement pstmt=mConn.prepareStatement("SELECT mtrc_nm FROM mtrc where mtrc_cat_id=?");
pstmt.setLong(1,NUM);
rs = pstmt.executeQuery();
if(rs.next()){
Comp=rs.getString(0);
}
}
catch(SQLException se){
throw new UdfException(33,se.getMessage());
}
outputWriter.setString(NUM, Comp);
try {
mConn.commit();
mConn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
I was able to compile & create the jar. I was also able to import the UDF into Vertica. But when i try to call the UDF, i get the below error.
Code: Select all
select TokenizeString('123') over();
ERROR 3399: Failure in UDx RPC call InvokeProcessPartition(): Error in User Defined Object [TokenizeString], error code: 33
com.vertica.sdk.UdfException: No suitable driver found for jdbc:vertica://host:5433/database
at com.testudf.udfex.TokenFactory$TokenizeString.processPartition(TokenFactory.java:54)
at com.vertica.udxfence.UDxExecContext.processPartition(UDxExecContext.java:1432)
at com.vertica.udxfence.UDxExecContext.run(UDxExecContext.java:233)
at java.lang.Thread.run(Thread.java:744)
Am i missing anything in the process ?