UDF with JDBC not working

Moderator: NorbertKrupa

Post Reply
rvramanan21
Newbie
Newbie
Posts: 3
Joined: Sat May 31, 2014 2:26 pm

UDF with JDBC not working

Post by rvramanan21 » Tue Jun 17, 2014 5:22 pm

Hi All,

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)
When i created the UDF jar, i tried included the vertica jdbc jar directly & tried including it through a manifest file and none helped.

Am i missing anything in the process ?
Last edited by NorbertKrupa on Tue Jun 17, 2014 8:23 pm, edited 1 time in total.
Reason: formatting

Post Reply

Return to “Vertica User Defined Functions (UDFs)”