Execute sql in TransformFunction

Roman_R

Hello, All!
You are recommended this blog for connecting Java to Vertica, but I have problem with vertica driver at the moment. I use Java UDTf to refer to the database.
 http://tomgendron.wordpress.com/2011/08/24/connecting-java-to-vertica/
 
 Java class with driver:
package com.mycompany.example;
import java.sql.*;
import java.util.Properties;

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;
             Properties myPop=new Properties();
              myPop.put("user", "dbadmin");
              myPop.put("password", "vertica1");
              try
                 {
                 Class.forName("com.vertica.Driver");
                 } catch (ClassNotFoundException e)
                    {
                     throw new UdfException(33,e.getMessage());
                  
                    }
            try {
                mConn=DriverManager.getConnection("jdbc:vertica://localhost:5433/vertica1",myPop);
                //mConn=DriverManager.getConnection("jdbc:vertica://192.168.2.140:5433/vertica1",myPop);
                mConn.setAutoCommit(false);
                PreparedStatement pstmt=mConn.prepareStatement("SELECT COMPANY FROM allcustomers  WHERE allcustomers.CUST_NUM = ?");
                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) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }                        
    }    
} 
}

Command line:
dbadmin=> create library  token1  as '/home/dbadmin/java/select1.jar'dbadmin-> language 'Java';
CREATE LIBRARY
dbadmin=> create transform function to1  as language 'Java' name
dbadmin-> 'com.mycompany.example.TokenFactory' library token1;
CREATE TRANSFORM FUNCTION
dbadmin=> select CUST_NUM,to1(COMPANY) over(partition by CUST_NUM) from allcustomers;

ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error in User Defined Object [to1], error code: 33com.vertica.sdk.UdfException: com.vertica.Driver
        at com.mycompany.example.TokenFactory$TokenizeString.processPartition(TokenFactory.java:36)
        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)

Thank you for any help.

Daniel_Leybovic

Create connection and execute query inside of UDFx is a bad practice, because Vertica will parallelize query by partitions...

https://community.vertica.com/vertica/topics/how_to_receive_data_from_db_tables_using_r_udf

https://community.vertica.com/vertica/topics/can_i_open_odbc_connection_from_within_vertica_using_r_...



PS
Read links, it should give you an answer for your questions about executing query inside of UDFx.