Execute embedded sql in scalar function

Roman_R

Hello All,

I am creating an external scalar function on Java. Is it possible to execute inline sql inside this function? What connection parameters should I use in this case?

Navin_C

Hi Roman,

I believe it is possible, but then it is not recommended.

You need to open a jdbc connection to the database and UDF executes parallel on the nodes in a cluster, it will open as many threads/processes on the nodes.

A UDF is meant for transforming data and not retrieving data.

Hope this helps

Roman_R

Hello Navin,

 I Just try to find how I can execute select into statements in procedure or function. Procedure is better. But in Vertica we have only external procedures that should be wrote only on script language and Java is not suit in this case.

So I decide to write UDF and place into this select into statement.

If you can advice better solution for me it will be great.

Thank you 

Navin_C

if your requirement is to only execute select statement / insert statement then external procedure is the best way.
If your requirement is like creating and using prepared statement to load database then go for writing programs in JAVA which can open a connection to vertica and execute your statement and close the connection. 

For this you can refer to this blog: http://tomgendron.wordpress.com/2011/08/24/connecting-java-to-vertica/

Hope this helps

Roman_R

Can I somehow use Java to create external procedure?

Navin_C

External procedure are only allowed to be written in shell script for now.

Roman_R

Thank you very much for your help, Navin.

[Deleted User]

For what it's worth:  External procedures can, I believe, technically be any executable command.  So you could write a standalone Java program and use that.  (Or use a shell script to invoke a Java program; etc.)

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/

My java class:
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 {
                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.

Roman_R

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();
            }
            
                            
    }
    
}
    
}