[R-sig-DB] Unwanted carriage returns storing dataframes with dbWriteTable

[Prof Brian Ripley]

I suspect this is related to the similar problem seen in RSQLite by the 
same author, Cc:ed here :

https://stat.ethz.ch/pipermail/r-help/2007-January/122998.html
https://stat.ethz.ch/pipermail/r-sig-db/2007q1/000248.html

There is a function RMySQL:::mysqlWriteTable that has

    sql4 <- paste("LOAD DATA LOCAL INFILE '", fn, "'",
                   " INTO TABLE ", name,
                   " LINES TERMINATED BY '\n' ", sep="")

I believe that \n needs to be \r\n on Windows. See 
http://dev.mysql.com/doc/refman/5.0/en/load-data.html .  The alternative 
is to alter RMySQL:::safe.write to work with a binary file.


On Tue, 12 Aug 2008, Hansruedi Baetschmann wrote:

> Hi
>
> If I save a dataframe with a character-typed last column to a relational 
> database with dbWriteTable, the  values in the last column of the resulting 
> table in the database will have a '\r' (carriage return) appended. If I read 
> back the dataframe with dbReadTable the last column in the resulting 
> dataframe has also '\r' appended (see protocol below). Setting or unsetting 
> sql-mode (in the SQL-Server Version 5.0) to or from 'NO_BACKSLASH_ESCAPE' has 
> no effect on this behavour. How can I avoid these unwanted '\r'-tail without 
> a workaround-wrapper, which adds an extra
> dummy column before writing and removes it after reading back ? 
> I use R.2.7.1 and package RMySQL
>
> Hansruedi
>
>
>> version
>              _                           platform       i386-pc-mingw32 
> arch           i386                        os             mingw32 
> system         i386, mingw32               status 
> major          2                           minor          7.1 
> year           2008                        month          06 
> day            23                          svn rev        45970 
> language       R                           version.string R version 2.7.1 
> (2008-06-23)
>
>> library("RMySQL")
>> con=dbConnect("MySQL",dbname="rTables",user="root", 
>> password="xxxxx",host="localhost")
>> dbGetInfo(con)
>
> ...
> $conType
> [1] "localhost via TCP/IP"
>
> $serverVersion
> [1] "5.0.24-community-nt-log"
>
> $protocolVersion
> [1] 10
>
> $threadId
> [1] 46
>
> $rsId
> list()
>
>> dfin=data.frame(
> +   a=c(1,2,3,4),
> +   b=c("a","b","c","d"),
> +   c=c("x","y","z","t"),
> +   row.names=2)
>> dfin
> a c
> a 1 x
> b 2 y
> c 3 z
> d 4 t
>> dbGetQuery(con, "DROP TABLE IF EXISTS df")
> NULL
>> dbWriteTable(con,"df",dfin,row.names=FALSE)
> [1] TRUE
>> dfout=dbReadTable(con,"df")
>> dfout
> a   c
> 1 1 x\r
> 2 2 y\r
> 3 3 z\r
> 4 4 t\r
>> dbDisconnect(con)
> [1] TRUE
>> 
>
> -- 
> Hansruedi Baetschmann
> dipl.math.ETH et lic.oec.HSG
> Functional Genomics Center Zurich
> Winterthurerstrasse 190 / Y32H66
> CH-8057 Zürich
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using 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