RSQLite problems

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

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

-------------- next part --------------
"safe.write" <-
function (value, file, batch, ..., sep=",", eol="\n", quote.string = FALSE) 
{
    N <- nrow(value)
    if (N < 1) {
        warning("no rows in data.frame")
        return(NULL)
    }
    if (missing(batch) || is.null(batch)) 
        batch <- 10000
    else if (batch <= 0) 
        batch <- N
    from <- 1
    to <- min(batch, N)
    while (from <= N) {
        if (usingR()) 
            write.table(value[from:to, , drop = FALSE], file = file, 
                append = TRUE, quote = quote.string, sep = sep, 
                na = .SQLite.NA.string, row.names = FALSE, col.names = FALSE, 
                eol = eol, ...)
        else write.table(value[from:to, , drop = FALSE], file = file, 
            append = TRUE, quote.string = quote.string, sep = ",", 
            na = .SQLite.NA.string, dimnames.write = FALSE, end.of.row = "\n", 
            ...)
        from <- to + 1
        to <- min(to + batch, N)
    }
    invisible(NULL)
}

"sqliteWriteTable" <-
function (con, name, value, field.types, row.names = TRUE, overwrite = FALSE, 
    append = FALSE, ..., sep = ",") 
{
    if (overwrite && append) 
        stop("overwrite and append cannot both be TRUE")
    if (!is.data.frame(value)) 
        value <- as.data.frame(value)
    if (row.names) {
        value <- cbind(row.names(value), value)
        names(value)[1] <- "row.names"
    }
    if (missing(field.types) || is.null(field.types)) {
        field.types <- sapply(value, dbDataType, dbObj = con)
    }
    i <- match("row.names", names(field.types), nomatch = 0)
    if (i > 0) 
        field.types[i] <- dbDataType(con, field.types$row.names)
    names(field.types) <- make.db.names(con, names(field.types), 
        allow.keywords = F)
    if (length(dbListResults(con)) != 0) {
        new.con <- dbConnect(con)
        on.exit(dbDisconnect(new.con))
    }
    else {
        new.con <- con
    }
    if (dbExistsTable(con, name)) {
        if (overwrite) {
            if (!dbRemoveTable(con, name)) {
                warning(paste("table", name, "couldn't be overwritten"))
                return(FALSE)
            }
        }
        else if (!append) {
            warning(paste("table", name, "exists in database: aborting dbWriteTable"))
            return(FALSE)
        }
    }
    if (!dbExistsTable(con, name)) {
        sql1 <- paste("create table ", name, "\n(\n\t", sep = "")
        sql2 <- paste(paste(names(field.types), field.types), 
            collapse = ",\n\t", sep = "")
        sql3 <- "\n)\n"
        sql <- paste(sql1, sql2, sql3, sep = "")
        rs <- try(dbSendQuery(new.con, sql))
        if (inherits(rs, ErrorClass)) {
            warning("could not create table: aborting assignTable")
            return(FALSE)
        }
        else dbClearResult(rs)
    }
    fn <- tempfile("rsdbi")
    safe.write(value, file = fn, ..., sep=sep)
    on.exit(unlink(fn), add = TRUE)
    if (FALSE) {
        sql4 <- paste("COPY '", name, "' FROM '", fn, "' USING DELIMITERS ','", 
            sep = "")
        rs <- try(dbSendQuery(new.con, sql4))
        if (inherits(rs, ErrorClass)) {
            warning("could not load data into table")
            return(FALSE)
        }
        else dbClearResult(rs)
        TRUE
    }
    conId <- as(new.con, "integer")
    sep <- as.character(sep[1])
    .Call("RS_SQLite_importFile", conId, name, fn, sep, PACKAGE = "RSQLite")
}