0

The following code fails at the second to last line but retrieves the correct result set on the last line:

library(RJDBC)
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver","D:/sqljdbc4.jar")
conn <- dbConnect(drv, 
  'jdbc:sqlserver://[servername];databaseName=[databasename]',
  '[username]', '[password]'
)

new_records_table <- 'dbo.new_dummy'
try(dbRemoveTable(conn, new_records_table), silent=T) # in case the table was already there
input_table <- data.frame(col1=c('A'))
dbWriteTable(conn, new_records_table, input_table)

dbReadTable(conn, new_records_table) # fails
dbGetQuery(conn, paste('SELECT * FROM', new_records_table)) # succeeds

(where [servername], [databasename], [username], and [password] were suppressed).

I'm on Windows 7 64-bit using R version 3.5.1, and output from sessionInfo() shows "RJDBC_0.2-7.1", "rJava_0.9-10", and "DBI_1.0.0" as the other attached packages. For the SQL server, it's SQL Server 2016.

In the latest version of the RJDBC source on CRAN, in R.class, dbReadTable() is simply doing dbGetQuery(conn, paste("SELECT * FROM",.sql.qescape(name,TRUE,conn@identifier.quote))), and I didn't see anything weird with .sql.qescape when I tried it out either. Any ideas?

Edit: the error I'm seeing at the second to last line is

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for SELECT * FROM "dbo.new_dummy" (Invalid object name 'dbo.new_dummy'.)

Werner Hertzog
  • 2,002
  • 3
  • 24
  • 36
A Hood
  • 51
  • 5
  • You might try dropping the `dbo`. I find that sometimes interferes. I usually use something like `dbplyr::in_schema` to give an explicit schema. – Benjamin Jul 24 '18 at 01:02
  • Thanks, `dbReadTable(conn, 'new_dummy')` works. But the problem remains if the table is part of a non-dbo schema. – A Hood Jul 24 '18 at 12:39
  • In `dbReadTable`, I believe the call would be `dbReadTable(conn, 'new_dummy', schema = "schema_name")`. – Benjamin Jul 24 '18 at 13:00
  • Hmm... `RJDBC::dbReadTable(conn, '[another table]', schema='[a non-dbo schema]')` gives me the same error. Can you point to documentation that RJDBC::dbReadTable will use keyword argument "schema"? I didn't see any [here](https://cran.r-project.org/web/packages/RJDBC/RJDBC.pdf) or evidence of it in source. Would be nice, though. – A Hood Jul 24 '18 at 14:41

0 Answers0