R help - Sep 2009 - 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

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.
>>