Re: [R] Q

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Tue 03 Apr 2007 - 08:05:29 GMT

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

______________________________________________
R-help@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.
Received on Tue Apr 03 18:10:53 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Tue 03 Apr 2007 - 08:30:47 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.