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

[Ista Zahn]

dbGetQuery doesn't like the "." in your index name. Change to
something else, e.g.,

            command   <-paste("CREATE INDEX IF NOT EXISTS ", DBname_c, "_",
              indexName_c, " ON ", yourTable_c, " (",
              paste(indexVars_C, collapse=", "), ")", sep='')
  }

Best,
Ista

On Sat, Jan 4, 2014 at 7:00 PM, Andrew Hoerner <ahoerner at rprogress.org> wrote:
> 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
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.