[R] RMySql inserts \r when using dbWriteTable

[Prof Brian Ripley]

You will find this discussed in the list archives: most recently see the 
thread starting

https://stat.ethz.ch/pipermail/r-help/2008-August/170562.html

Bottom line: the package maintainer needs to correct a bug in the package 
code (and did not do so in the subsequent release 0.6-1), but meantime you 
can correct the code as described in that thread.

On Wed, 5 Nov 2008, Albert Nigrin wrote:

> I am using R 2.8 and the latest versions of RMySQL on a Windows XP 64 bit
> machine.

Please be accurate, as the posting guide asks.  E.g. is this a '64 bit' 
version of R you are running, what are the actual version numbers?

> I was wondering if someone could help me figure out how to use dbWriteTable
> without inserting \r into my table.  Consider the following code snippet,
> which is
> run after I connect to my database.
>
> myDFOut = dbReadTable(conn, "myDF")
> print(myDFOut)
> myDFIn = data.frame(x=paste("x", 1:5, sep = ""), y = paste("y", 1:5, sep =
> ""))
> dbWriteTable(conn, name= "myDF", value = myDFIn, row.names=F, append =TRUE,
> eol = "\r\n" )
> myDFOut = dbReadTable(conn, "myDF")
> print(myDFIn)
> print(myDFOut)
>
> I get the output:
>> myDFOut = dbReadTable(conn, "myDF")
>> print(myDFOut)
> data frame with 0 columns and 0 rows
>> myDFIn = data.frame(x=paste("x", 1:5, sep = ""), y = paste("y", 1:5, sep =
> ""))
>> dbWriteTable(conn, name= "myDF", value = myDFIn, row.names=F, append
> =TRUE, eol = "\r\n" )
> [1] TRUE
>> myDFOut = dbReadTable(conn, "myDF")
>> print(myDFIn)
>   x  y
> 1 x1 y1
> 2 x2 y2
> 3 x3 y3
> 4 x4 y4
> 5 x5 y5
>> print(myDFOut)
>   x    y
> 1 x1 y1\r
> 2 x2 y2\r
> 3 x3 y3\r
> 4 x4 y4\r
> 5 x5 y5\r
>
> Is there any way not to insert the "\r" in the database?   I also get the
> same output, if I change the eol argument to "\n" in dbWriteTable, as in the
> following command.
>
> dbWriteTable(conn, name= "myDF", value = myDFIn, row.names=F, append =TRUE,
> eol = "\n" )
>
> I have read previous discussion concerning SQLite, but I still can't figure
> out what to do.
>
> Thanks for your help.
>
> Albert
>
> ______________________________________________
> 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.
>

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