R JDBC error "Unable to retrieve JDBC result set for insert into ..."
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
Need to use dbSendUpdate.
dbSendUpdate(connWrite, str_insert_query[1])