Can I open ODBC connection from within Vertica using R function?

Siddarth

In my R function, I am trying to connect to Vertica using RODBC.

$cat sample.R

sample <- function(test)
{
library(RODBC)
myconn <-odbcConnect("SFPDSN")
pundat <- sqlQuery(myconn, "select * from test_super;")
return(1)
}
sampleFactory <- function()
{
list(name=sample ,udxtype=c("transform"),intype=c("int"),outtype=c("int"))
}

"sqlQuery" is working when I execute these staments in R. But when I run my R function in Vertica it gives me error.

dbadmin=> \i sam.sql
Drop library sampleLib cascade;
DROP LIBRARY
CREATE LIBRARY sampleLib AS '/home/dbadmin/sample.R' LANGUAGE 'R';
CREATE LIBRARY
CREATE TRANSFORM FUNCTION sample AS NAME 'sampleFactory' LIBRARY sampleLib;
CREATE TRANSFORM FUNCTION
vsql:sam.sql:7: ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [sample] at [/scratch_a/release/vbuild/vertica/UDxFence/RInterface.cpp:1236], error code: 0, message: Exception in processPartitionForR: [first argument is not an open RODBC channel]
dbadmin=>


Below statement are working fine in R.
>library(RODBC)
>myconn <-odbcConnect("SFPDSN")
>pundat <- sqlQuery(myconn, "select * from test_super;")

Where "SFPDSN" is the odbc connection.

Sunil_V

I suspect you cannot open ODBC connection in R-UDx. UDx are designed to run as a SQL function and data will always come from the table(s) that you are included in the FROM clause of the SELECT statement. If you need more data to process from a different table, I think you cannot use ODBC connection inside R-UDx. May be you could consider changing the SQL to include the data that you need to execute the function as R-UDx.

Siddarth

Thanks for the reply Sunil.

I have also raised this issue with the support team. Will update once I get anything from them.

Siddarth

Error that I am getting:

dbadmin=> Drop library sampleLib cascade;
dbadmin=> CREATE LIBRARY sampleLib AS '/home/dbadmin/Ashok/sample.R' LANGUAGE 'R';
dbadmin=> CREATE TRANSFORM FUNCTION sample AS NAME 'sampleFactory' LIBRARY sampleLib;
dbadmin=> CREATE TABLE foo(a INT);
dbadmin=> INSERT/*DIRECT*/ INTO foo VALUES(1);
OUTPUT
--------
    1
(1 row)

dbadmin=> SELECT sample(a) over() FROM foo;
ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [sample] at [/scratch_a/release/vbuild/vertica/UDxFence/RInterface.cpp:1236], error code: 0, message: Exception in processPartitionForR: [first argument is not an open RODBC channel]

Siddarth

This works. Database needs to be restarted after setting VERTICAINI.