CODEWITHSUNDEEP
rrodbc
Felipe
Felipe

Reputation: 9421

RODBC package in R creates column names with quotes... why?

So, I am using the RODBC package in R to write and read from my database and this is what happens:

Let's use mtcars for our example

I save mtcars to my database: 

sqlSave(con, mtcars, "MTCARS",rownames = F)

Then I read what I just saved: 

sqlQuery(con,"select * from MTCARS")

Perfect! It works... Now let's try this: 

sqlQuery(con,"select mpg from MTCARS")
[1] "42S22 904 [Oracle][ODBC][Ora]ORA-00904: \"MPG\": invalid identifier\n"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select mpg from MTCARS'"

So I check the database and it saves the column name with quotes!! So for my query to work I have to do this:

sqlQuery(con,"select \"mpg\" from MTCARS")

Why? How can I fix this?

Upvotes: 2

Views: 708

Answers (1)

joran
joran

Reputation: 173577

Oracle generally only uses uppercase column names. At some point in the process the data frame column names are being quoted because it realizes they aren't uppercase. I'm not sure if that is happening in RODBC, the driver or the db itself.

In any case, converting the column names to uppercase first seems to fix the problem.

Upvotes: 2

Related Questions