Execute embedded sql in scalar function

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

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

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

Can I somehow use Java to create external procedure?

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

Thank you very much for your help, Navin.

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.)

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.

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