[R] Q

[Prof Brian Ripley]

The problem is that relatively few DBMSes come even close to conformance 
with SQL (and Laurent Valdes did not tell us his DBMS even in his reply).

ANSI SQL says single quotes are used for literals, and double quotes for 
identifiers (e.g. table and column names).

Literals are only relevant to RODBC in sqlSave(fast=FALSE) and 
sqlUpdate(fast=FALSE), neither being the defaults.  In theory ODBC can 
tell you the quoting characters for each data type, and it seems now all 
the drivers I have access to are ODBC3, this works (it used not to).  Not 
all drivers use single quote for all types, but in all cases I tried where 
they use single quote for character strings, doubling it works.  So that 
will be incorporated in RODBC 1.1-9.

There is less conformance for quoting identifiers.  MySQL uses backtick by 
default (it can be run in ANSI_QUOTE mode), and some Microsoft drivers use 
[...] for table names.  RODBC 1.1.9 makes these settable options, and 
tries to select suitable defaults based on the DBMS name reported.

I will send RODBC_1.1-9 to CRAN shortly.  People testing R-2.5.0 alpha 
under Windows will be able to get it now from CRANextras (a default 
repository).

On Sun, 1 Apr 2007, Duncan Murdoch wrote:

> On 4/1/2007 3:39 AM, Prof Brian Ripley wrote:
>> I think this is about RODBC, uncredited.
>>
>> There is currently no way to do escapes here, and I am pretty sure that
>> ODBC drivers do not support 'slash' as an escape (or did you mean
>> backslash? I am still not sure that would work).
>>
>> sqlSave() has fast=TRUE and fast=FALSE options, and it is worth trying
>> both.  Since the default fast=TRUE passes character data as binary fields,
>> I don't understand why there is a problem (there might be for fast=FALSE
>> with single quotes in character fields).
>
> SQL doubles quotes to escape them, doesn't it?  I.e. if I wanted
>
> this 'value'
>
> as an SQL literal, I would use
>
> 'this ''value'''
>
> I think this is described in the SQL 92 standard (it defines
> <quote symbol> ::= <quote><quote>), but I don't know if it is
> universally implemented.  So if this syntax is supported in Laurent's
> driver/DBMS combination, he could simply double the quotes himself
> before trying to save them, i.e.
>
> values <- gsub("'", "''", values)
>
> Duncan Murdoch
>
>>
>> Without any relevant details (OS, R version, packages used, package
>> version, ODBC driver, DBMS) and any sort of example it is really hard to
>> offer help: please DO study the R posting guide (and note also the comment
>> about using a signature block).
>>
>> On Sat, 31 Mar 2007, Laurent Valdes wrote:
>>
>>> Hi everybody,
>>>
>>> I'm doing a sqlSave() in R, to insert a big data frame of 10000 rows.
>>> However, there is problems, since several rows contains quotations marks,
>>> that can leave inserts buggy.
>>> I would like to find a way to add slashes in front of these quotation marks.
>>>
>>> Best regards,
>>>
>>> Laurent
>>>
>>>
>>>
>>
>
> ______________________________________________
> R-help at stat.math.ethz.ch 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.
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595