Stefan Uhmann
2009-Sep-15 14:52 UTC
[R] quoting a table name due to a special character in sqlQuery (RODBC)
Dear List, I have a problem with RODBC on a Paradox-DB, sqlQuery, and special characters in table names. Unfortunately, some of the latter include the underscore <_>. And I am not able to change them. That's not a problem, when I quote the table name: > sqlQuery(channel2, 'SELECT * FROM "anmeldung-alt"') Btw, if I swap ' and " it does not work: > sqlQuery(channel2, "SELECT * FROM 'anmeldung-alt'") [1] "HY000 -3003 [Microsoft][ODBC Paradox Driver] Syntaxfehler in Abfrage. Die Abfrage ist unvollst?ndig." [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM 'anmeldung-alt''" It gets nasty, when I try to build a function and to substitute the 'SELECT * FROM "anmeldung-alt"' with a paste(): > tabelle [1] "anmeldung-alt" > paste('select * from ', '"', tabelle, '"', sep="") [1] "select * from \"anmeldung-alt\"" > gsub('\"', '"', paste('select * from ', '"', tabelle, '"', sep="")) [1] "select * from \"anmeldung-alt\"" Can anybody point me into the right direction, please? Thanks in advance, Stefan
Stefan Uhmann
2009-Sep-15 17:18 UTC
[R] quoting a table name due to a special character in sqlQuery (RODBC)
It works, great! Thank you, Phil. /Stefan Phil Spector schrieb, Am 15.09.2009 18:28:> Stefan - > Have you tried surrounding the problem names with > backticks (`) instead of quotes (' or ")? > > - Phil Spector > Statistical Computing Facility > Department of Statistics > UC Berkeley > spector at stat.berkeley.edu > > > On Tue, 15 Sep 2009, Stefan Uhmann wrote: > >> Dear List, >> >> I have a problem with RODBC on a Paradox-DB, sqlQuery, and special characters >> in table names. Unfortunately, some of the latter include the underscore <_>. >> And I am not able to change them. >> >> That's not a problem, when I quote the table name: >>> sqlQuery(channel2, 'SELECT * FROM "anmeldung-alt"') >> Btw, if I swap ' and " it does not work: >>> sqlQuery(channel2, "SELECT * FROM 'anmeldung-alt'") >> [1] "HY000 -3003 [Microsoft][ODBC Paradox Driver] Syntaxfehler in Abfrage. >> Die Abfrage ist unvollst?ndig." >> [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM 'anmeldung-alt''" >> >> It gets nasty, when I try to build a function and to substitute the 'SELECT * >> FROM "anmeldung-alt"' with a paste(): >>> tabelle >> [1] "anmeldung-alt" >>> paste('select * from ', '"', tabelle, '"', sep="") >> [1] "select * from \"anmeldung-alt\"" >>> gsub('\"', '"', paste('select * from ', '"', tabelle, '"', sep="")) >> [1] "select * from \"anmeldung-alt\"" >> >> Can anybody point me into the right direction, please? >> >> Thanks in advance, >> Stefan >> >> ______________________________________________ >> R-help at r-project.org mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. >>