Re: [R] RSQLite problems

From: David James <dj_at_research.bell-labs.com>
Date: Fri 28 Oct 2005 - 12:12:59 EST

Hi,

Thanks for reporting the two problems. I'm attaching a simple update to two functions that will allow you to specify a different separator, e.g., using your example:

   dbWriteTable(con, "barley", barley, overwrite = TRUE, sep = ";")

This workaround still relies in dumping the data.frame into a temporary file and then importing into SQLite, but using prepared statements (which SQLite 3 supports) will require some more work.

I'll look into the problem with the trailing newline soon.

--
David

Na Li wrote:

>
> Hi, I'm experimenting with using (R)SQLite to do data management. Here are
> two little problems that I've encountered:
>
> 1. The presence of ',' in string values causes trouble since ',' is also the
> delimiter used in the SQL statement.
>
> 2. A newline '\n' line attached to the last string value of each row.
>
> Some examples:
>
> > library (RSQLite)
> Loading required package: DBI
> > sqlite <- dbDriver ("SQLite")
> > db <- dbConnect (sqlite, dbname = "test.dbms")
> > data (barley)
> > dbWriteTable (db, "barley", barley, overwrite = TRUE)
> [1] TRUE
> > barley[1:3,]
> yield variety year site
> 1 27.00000 Manchuria 1931 University Farm
> 2 48.86667 Manchuria 1931 Waseca
> 3 27.43334 Manchuria 1931 Morris
> > dbReadTable (db, "barley")[1:3,]
> yield variety year__1 site
> 1 27.00000 Manchuria 1931 University Farm\n
> 2 48.86667 Manchuria 1931 Waseca\n
> 3 27.43334 Manchuria 1931 Morris\n
>
> > barley$site <- as.character (barley$site)
> > barley$site[1] <- "University, Farm"
> > dbWriteTable (db, "barley", barley, overwrite = TRUE)
> Error in sqliteWriteTable(conn, name, value, ...) :
> RS-DBI driver: (RS_sqlite_import: /tmp/RtmpgSNaLn/rsdbi6a5d128c line 1
> expected 5 columns of data but found 6)
>
> I'm using RSQLite 0.4.0 with R 2.1.1 on Mac OS X.
>
> Cheers,
>
> Michael
>
> ______________________________________________
> 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

______________________________________________ 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

Received on Fri Oct 28 12:27:36 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:40:51 EST