JDBC connection doesn't support quoted identifier (dbWriteTable error)

Question

When I attempt to import a data frame onto a SQL server in R via a JDBC connection, I get the following error:

dbWriteTable(dbhandle, "dbo.Testing", My_DF, row.names=FALSE, append = TRUE, temporary = FALSE)
Error in .sql.qescape(names(value), TRUE, conn@identifier.quote) : 
The JDBC connection doesn't support quoted identifiers, but table/column name contains characters that 
must be quoted

I searched online and found a suggested solution by specifying quotes, i.e.

dbWriteTable(dbhandle, "dbo.Testing", My_DF, row.names=FALSE, append = TRUE, temporary = FALSE, identifier.quote = "\"")

However, I get exactly the same error message. Is there some argument in dbWriteTable that I'm still missing? As far as I know, there is nothing strange about my data frame's data type or structure.

Answer 1

Essentially, your main issue is two related things: 1) using a mixed case identifier, Testing in dbWriteTable and 2) not setting the double quotes feature in connection object of RJDBC.

According to RJDBC docs, the connection driver call, JDBC() defaults to NA for identifier quotes with further explanation of the argument:

JDBC (driverClass = "", classPath = "", identifier.quote = NA)

identifier.quote
      character to use for quoting identifiers in automatically generated SQL statements or NA if
      the back-end doesn’t support quoted identifiers. See details section below.

... Therefore the RJDBC implementation tries to adhere to the SQL92 standard, but not all databases are compliant. This affects mainly functions such as dbWriteTable that have to automatically generate SQL code. One major ability is the support for quoted identifiers. The SQL92 standard uses double-quotes, but many database engines either don't support it or use other character. The identifier.quote parameter allows you to set the proper quote character for the database used.

---

Specifically, the reason the following fails is mixing cases in your new table name, T (upper) esting (lower).

dbWriteTable(dbhandle, "Testing", My_DF, row.names=FALSE, append=TRUE, temporary=FALSE)

This forces dbWriteTable to run underlying SQL commands with double quotes such as the create-table command with double quotes in order to preserve cases in the name:

CREATE TABLE "Testing" (
   ...
)

RJDBC source code shows that it checks if double quotes were set in connection sesssion, and if not it throws an error. To fix consider either solution:

1. Set identifier.quote at JDBC() call:

   drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
               "/path/to/sql/server.jar",
               identifier.quote = "\"")
   
   dbHandle <- dbConnect(drv, "jdbc:sqlserver://...", "user", "***")
   

2. Keep all identifiers in all upper case (Oracle) that adhere to ANSI SQL-1992 standard or all lower case (Postgres).

   dbWriteTable(dbhandle, "TESTING", My_DF, row.names=FALSE, 
                 append=TRUE, temporary=FALSE)
   
   dbWriteTable(dbhandle, "testing", My_DF, row.names=FALSE, 
                 append=TRUE, temporary=FALSE)
   

   This will then allow any queries to be run without double quotes: select * from TeStInG, select * from testing, etc.

However, by default, identifiers in SQL Server are case insensitive unless you adjusted collation at server, database, or column levels. But to bypass RDBJC check and to effectively run the create-table command, consider simply setting the idenitier.quote at driver step.