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

[Hansruedi Baetschmann]

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