[R] RSQLite: Tracking an error message to its source

[Andrew Hoerner]

Andrew Hoerner <ahoerner <at> rprogress.org> writes:

> 
> Dear folks--
> I am not sure if this should be framed as a question about RSQLite, about
> debugging, about SQLite, or about how to write a good question. I have a
> little function (copied below beneath the error messages along with my
> data), that is supposed to read a csv file and write it out to SQLite. I
> can not even begin to figure out how to write a minimal reproducible
> example – I do not even know whether the RS-DBI driver is part of R, of
> RSQLite, of SQLite, or is its own separate piece of software.
> 
> When I ran my functiont, like so:
> 
> > ImportRSQLite
("C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\minifile\\cps_00077.csv",
> + sep=",", DBname_c="TX1", yourTable_c="mini01", vars_L=vars_L,
> + indexName_c="IND01", indexVars_C=ndxs01)
> 
> I get this error message:
> “Error in sqliteExecStatement(con, statement, bind.data) :
>   RS-DBI driver: (error in statement: unknown database TX1)”
> 
> Then I thought maybe it was not looking in my R working directory, so I 
ran
> it again with a full path name, like this:
> 
> > ImportRSQLite
("C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\minifile\\cps_00077.csv",
> + sep=",", DBname_c="C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\TX1",
> yourTable_c="mini01", vars_L=vars_L, + indexName_c="IND01",
> indexVars_C=ndxs01)
> 
> And got this error message, which seems to simultaneously contradict that
> it is a wrong directory problem and also say that it can find the 
database
> after all:
> 
> Error in sqliteExecStatement(con, statement, bind.data) :
>   RS-DBI driver: (error in statement: unrecognized token: ":")
> In addition: Warning message:
> In sqliteImportFile(conn, name, value, ...) :
>   table mini01 exists in database: aborting dbWriteTable
> 
> When I go to my R_PROJ\INEQ_TRENDS\TESTS directory, I see a file of the
> name and size I expect if the database were written correctly.
> 
>  Any help anyone could offer would be much appreciated.
> 
> Warmest regards, andrewH
> 
> ###############################################
> ImportRSQLite <- function(yourFileName_c, sep=",", DBname_c, yourTable_c,
>                           vars_L, indexName_c=NULL, indexVars_C){
> # ImportRSQLite takes: the file name (if in your working directory) or 
file
> &  path (if elsewhere) of your
> # csv data file; a list containing the column names & optional 
datatypes; a
> database name; a table name
> # for the data; & creates an SQLite database with a table containing your
> data.
> 
>   # Make DB
>   require("RSQLite")
>   db <- dbConnect(SQLite(), dbname=DBname_c)   ## Will make DB, if not
> present
> 
>   # Write file to table     # Note: SQLite command is CREATE TABLE.
>   dbWriteTable(con=db, name=yourTable_c, value=yourFileName_c, sep=sep,
>              row.names=FALSE, header=TRUE, field.types=vars_L)
> 
>   # Add indexing if desired
>     if (!is.null(indexName_c)){
>     command   <-paste("CREATE INDEX IF NOT EXISTS ", DBname_c, ".",
>               indexName_c, " ON ", yourTable_c, " (", indexVars_C, ")",
> sep='')
>   }
> 
>   dbGetQuery(db, command)
>   dbDisconnect(db)
> }
> 
> And here is my data:
> ###############################################
> # TEST FOR ImportRSQLite
> ###############################################
> 
> vars_L <- list(YEAR="INTEGER",
>                SERIAL="INTEGER",
>                HWTSUPP="REAL",
>                STATEFIP="INTEGER",
>                MONTH="INTEGER",
>                PERNUM="INTEGER",
>                WTSUPP="REAL",
>                FAMSIZE="INTEGER",
>                AGE="INTEGER",
>                RACE="INTEGER",
>                FTOTVAL="REAL")
> 
> ndxs01 <-      c("HWTSUPP", "RACE")
> 
> And the data in the file I am importing looks like this, but longer:
> "YEAR","SERIAL","HWTSUPP","STATEFIP","MONTH","PERNUM","WTSUPP","FAMSIZE",
"AGE","RACE","FTOTVAL"
> 2001,6879,196.86,44,3,1,196.86,3,35,100,67010
> 2001,6931,413.27,44,3,2,413.27,1,35,100,10216
> 


DearFolks--
This is an update on my previous posting. 

This does not change the error, but I have fixed the code creating 
command, which now reads:
    command   <-paste("CREATE INDEX IF NOT EXISTS ", DBname_c, ".", 
              indexName_c, " ON ", yourTable_c, " (", 
              paste(indexVars_C, collapse=", "), ")", sep='')

I have established that the error is coming out of the call to dbGetQuery 
toward the end of my function code..

dbGetQuery is a generic function with methods for conn and statement.. 

DBI:::dbGetQuery  tells me that:
standardGeneric for "dbGetQuery" defined from package "DBI"

showMethods(DBI:::dbGetQuery) says that  there are methods for 
conn="SQLiteConnection", statement="character"

isS4(dbGetQuery) returns TRUE.

I have not been able to figure out how to look at the actual code of 
dbGetQuery.

But from the original error, it appears the dbGetQuery must call 
sqliteExecStatement, which I did find code for, and which in turn calls 
RS_SQLite_exec.

RS_SQLite_exec is allegedly in RSQLite, or so I gather from this code:
Call("RS_SQLite_exec", conId, statement, bind.data, 
        PACKAGE = .SQLitePkgName) 

So I have tried ::, :::, showMethod, and everything else I could think of, 
including plain google searches, and the only place I have been able to 
find any trace of RS_SQLite_exec is in other people’s error messages. No 
code anywhere. 

So I still have not found a path back to RS-DBI, which I assume generated 
the original message, passing it up through an unknown number of 
intermediate steps to RS_SQLite_exec, then directly to 
sqliteExecStatement, and then again through an unknown number of 
intermediate steps to dbGetQuery.

getAnywhere(RS_SQLite_exec) says ”no object named ‘RS_SQLite_exec’ was 
found”
So does getAnywhere("RS-DBI").

You know, for a language that prides itself on being open source, there 
are still things that are pretty hard for a non-expert to find. That’s 
unfortunate.

Warmest regards, andrewH