[R] sqldf() difference between R 3.1.2 and 3.0.1

[Doran, Harold]

I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10 yields the error below that I am having a difficult time deciphering. Hence, same code behaves differently on different versions of R and sqldf().

Error in sqliteSendQuery(con, statement, bind.data) :
  error in statement: no such column: V1


Reproducible example below as well as complete sessionInfo all provided below.


My function and code using the function are below.

dorReader <- function(dorFile, layout, sepChar = '\n'){
                sepChar <- as.character(sepChar)
                dorFile <- as.character(dorFile)
                layout$type2 <- ifelse(layout$type == 'C', 'character',
                                                                ifelse(layout$type == 'N', 'numeric', 'Date'))
                dor <- file(dorFile)
                attr(dor, "file.format") <- list(sep = sepChar)
                getVars <- paste("select",
               paste("substr(V1, ", layout$Start, ", ",
                     layout$Length, ") '", layout$Variable.Name, "'", collapse = ", "), "from dor")
                dat <- sqldf(getVars)

                classConverter <- function(obj, types){
                                out <- lapply(1:length(obj),FUN = function(i){FUN1 <- switch(types[i],character = as.character,numeric = as.numeric,factor = as.factor, Date = as.character); FUN1(obj[,i])})
                                names(out) <- colnames(obj)
                                as.data.frame(out)
                }
                dat <- classConverter(dat, layout$type2)
                names(dat) <- layout$Variable.Name
                dat
}

### contents of fwf file 'sample.txt'
1234567
1234567
1234567
1234567
1234567
1234567
1234567
1234567

layout <- data.frame("Variable.Name" =c('test1', 'test2'), "Length" = c(3,4), "Start" =c(1,4), "End" = c(3,7), "type" = c('N', 'N'))

tmp <- dorReader('sample.txt', layout)

### SessionInfo where functions behaves as expected
> sessionInfo()
R version 3.0.1 (2013-05-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252

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

other attached packages:
[1] sqldf_0.4-7.1         RSQLite.extfuns_0.0.1 RSQLite_0.11.4        DBI_0.2-7             gsubfn_0.6-5
[6] proto_0.3-10          MiscPsycho_1.6        statmod_1.4.18

loaded via a namespace (and not attached):
[1] chron_2.3-45 tools_3.0.1




### SessionInfo for version not working
> sessionInfo()
R version 3.1.2 (2014-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

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

other attached packages:
[1] sqldf_0.4-10  RSQLite_1.0.0 DBI_0.3.1     gsubfn_0.6-6  proto_0.3-10

loaded via a namespace (and not attached):
[1] chron_2.3-45 tools_3.1.2

	[[alternative HTML version deleted]]