R JDBC error "Unable to retrieve JDBC result set for insert into ..."

Question

I am trying to write an R data.frame to a Netezza table. It has about 55K rows and I have set 4GB as memory limit for Java (options(java.parameters = "-Xmx4096m"))

Query:

insert into MY_TABLE_NAME select * from external 'csv_file_containing_data_frame.csv' using (delim ',' remotesource 'jdbc');

The above line of SQL works without any issues when I run it from a tool like DbVisualizer but I get the following error when I try to run it from RStudio.

R Code:

driver <- JDBC(driverClass="org.netezza.Driver", classPath = "drivers//nzjdbc.jar", "'")

connWrite <- dbConnect(driver, "jdbc:netezza://DB_SERVER:1234//DB_NAME", username, password)

str_insert_query <- paste(
    "insert into MY_TABLE select * from external '", OutputFile , "' using (delim ',' remotesource 'jdbc');", sep = ""

dbSendQuery(connWrite, str_insert_query[1])

dbDisconnect(connWrite)

Error Message:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for insert into MY_TABLE select * from external 'C:/.../csv_file_containing_data_frame.csv' using (delim ',' remotesource 'jdbc'); (netezza.bad.query.result)

• dbWriteTable works but is so slow that it cannot be used.
• Tried to assign the result of dbSendQuery() to a variable but it didn't work.

Any help will be greatly appreciated. Thank you!

Answer 1

Need to use dbSendUpdate.

dbSendUpdate(connWrite, str_insert_query[1])