R help - Mar 2006 - "\r" with RSQLite

What am I doing wrong, or is the \r that I'm getting
in the example below a bug?
> a <- (1:10)
> b <- (LETTERS[1:10])
> df <- as.data.frame(cbind(a, b))
> 
> df
    a b
1   1 A
2   2 B
3   3 C
4   4 D
5   5 E
6   6 F
7   7 G
8   8 H
9   9 I
10 10 J
> library(RSQLite)
> drv <- dbDriver("SQLite")
> con <- dbConnect(drv, dbname = "Test")
>     dbWriteTable(con, "DF", df, row.names = FALSE,
overwrite = TRUE)
[1] TRUE
>     df2 <- dbGetQuery(con, "SELECT DISTINCT * FROM
DF")
> dbDisconnect(con)
[1] TRUE
> df2
    a   b
1   1 A\r
2   2 B\r
3   3 C\r
4   4 D\r
5   5 E\r
6   6 F\r
7   7 G\r
8   8 H\r
9   9 I\r
10 10 J\r
> sessionInfo()
R version 2.2.1, 2005-12-20, i386-pc-mingw32 

attached base packages:
[1] "methods"   "stats"     "graphics" 
"grDevices"
"utils"     "datasets" 
[7] "base"     

other attached packages:
 RSQLite      DBI 
 "0.4-1" "0.1-10" 


Mikkel Grum
Genetic Diversity
International Plant Genetic Resources Institute

\r is a carriage return character which some editors may use as a line
terminator when writing files.  My guess is that RSQLite writes your
data frame to a temp file using \r as a line terminator and then runs
a script to have SQLite import the data (together with \r - this would
be the problem), but I have no idea if that's really the case. Check
the documentation or ask the maintainer.

> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Mikkel Grum
> Sent: Wednesday, March 15, 2006 1:46 PM
> To: r-help at stat.math.ethz.ch
> Cc: dj at research.bell-labs.com
> Subject: [R] "\r" with RSQLite
>
> What am I doing wrong, or is the \r that I'm getting
> in the example below a bug?
>
> > a <- (1:10)
> > b <- (LETTERS[1:10])
> > df <- as.data.frame(cbind(a, b))
> >
> > df
>     a b
> 1   1 A
> 2   2 B
> 3   3 C
> 4   4 D
> 5   5 E
> 6   6 F
> 7   7 G
> 8   8 H
> 9   9 I
> 10 10 J
> > library(RSQLite)
> > drv <- dbDriver("SQLite")
> > con <- dbConnect(drv, dbname = "Test")
> >     dbWriteTable(con, "DF", df, row.names = FALSE,
> overwrite = TRUE)
> [1] TRUE
> >     df2 <- dbGetQuery(con, "SELECT DISTINCT * FROM
> DF")
> > dbDisconnect(con)
> [1] TRUE
> > df2
>     a   b
> 1   1 A\r
> 2   2 B\r
> 3   3 C\r
> 4   4 D\r
> 5   5 E\r
> 6   6 F\r
> 7   7 G\r
> 8   8 H\r
> 9   9 I\r
> 10 10 J\r
>
> > sessionInfo()
> R version 2.2.1, 2005-12-20, i386-pc-mingw32
>
> attached base packages:
> [1] "methods"   "stats"     "graphics" 
"grDevices"
> "utils"     "datasets"
> [7] "base"
>
> other attached packages:
>  RSQLite      DBI
>  "0.4-1" "0.1-10"
>
>
> Mikkel Grum
> Genetic Diversity
> International Plant Genetic Resources Institute
>
> ______________________________________________
> 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
>

That is a bug, namely, the default end of line on the windows version 
should be "\r\n" instead of "\n".  The workaround is to
specify
eol="\r\n" in dbWriteTable(), e.g.,

   dbWriteTable(con, "DF", df, eol = "\r\n")
   dbReadTable(con, "DF")

Hope this helps,

--
David

PS The object .Platform includes path.sep and file.sep but not
end of line separator (as of 2.2.0) -- would it make sense to
also include eol.sep?

Mikkel Grum wrote:
> What am I doing wrong, or is the \r that I'm getting
> in the example below a bug?
> 
> > a <- (1:10)
> > b <- (LETTERS[1:10])
> > df <- as.data.frame(cbind(a, b))
> > 
> > df
>     a b
> 1   1 A
> 2   2 B
> 3   3 C
> 4   4 D
> 5   5 E
> 6   6 F
> 7   7 G
> 8   8 H
> 9   9 I
> 10 10 J
> > library(RSQLite)
> > drv <- dbDriver("SQLite")
> > con <- dbConnect(drv, dbname = "Test")
> >     dbWriteTable(con, "DF", df, row.names = FALSE,
> overwrite = TRUE)
> [1] TRUE
> >     df2 <- dbGetQuery(con, "SELECT DISTINCT * FROM
> DF")
> > dbDisconnect(con)
> [1] TRUE
> > df2
>     a   b
> 1   1 A\r
> 2   2 B\r
> 3   3 C\r
> 4   4 D\r
> 5   5 E\r
> 6   6 F\r
> 7   7 G\r
> 8   8 H\r
> 9   9 I\r
> 10 10 J\r
> 
> > sessionInfo()
> R version 2.2.1, 2005-12-20, i386-pc-mingw32 
> 
> attached base packages:
> [1] "methods"   "stats"     "graphics" 
"grDevices"
> "utils"     "datasets" 
> [7] "base"     
> 
> other attached packages:
>  RSQLite      DBI 
>  "0.4-1" "0.1-10" 
> 
> 
> Mikkel Grum
> Genetic Diversity
> International Plant Genetic Resources Institute
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com

[ diverted from R-help to R-devel ]
>>>>> "DJ" == David James <dj at
research.bell-labs.com>
>>>>>     on Thu, 16 Mar 2006 09:51:31 -0500 writes:
    DJ> That is a bug, namely, the default end of line on the windows version
    DJ> should be "\r\n" instead of "\n".  The workaround
is to specify
    DJ> eol="\r\n" in dbWriteTable(), e.g.,

    DJ> dbWriteTable(con, "DF", df, eol = "\r\n")
    DJ> dbReadTable(con, "DF")

    DJ> Hope this helps,

    DJ> --
    DJ> David

    DJ> PS The object .Platform includes path.sep and file.sep but not
    DJ> end of line separator (as of 2.2.0) -- would it make sense to
    DJ> also include eol.sep?

I think it would.  AFAIK, the Macs used to have  "\r" such that

  Unix:    "\n"
  Windoze: "\r\n"
  Mac :    "\r"

but it could well be that this has only been for Mac OS x.y, x <= 9,
i.e., is no longer true for MacOS X (which would behave like `Unix' then).

Brian Ripley is currently enjoying holidays AFAIK; I think he
might want to shed more light on this as well.

Martin