[R] RSQLite: Tracking an error message to its source
Jeff Newmiller
jdnewmil at dcn.davis.CA.us
Sun Jan 5 07:30:04 CET 2014
I recommend that you NOT try to go down that particular rabbit hole unless you are very confident in your computer science skills... database engines are very complicated. Learn to work with the error messages provided to you, and think in SQL when working with a SQL database.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
Andrew Hoerner <ahoerner at rprogress.org> wrote:
>Thanks, Ista! That did the job.
>
>Though I admit that it bothers me that, although the SQLite syntax
>documentation for "CREATE INDEX", here:
>http://www.sqlite.org/lang_createindex.html
>does say the database name is optional, it also says that, if you
>include
>it, the period the period between the db name and the table name is
>required.
>
>Can you, or anyone, give me any hint on where -- no, not where, _how_
>-- to
>find the code for any of the functions called between dbGetQuery method
>for
>conn="SQLiteConnection", or for any of the functions that are called by
>the
>dbGetQuery method for SQLite connections that are in the chain that
>ultimately calls sqliteExecStatement (exclusive of the latter, which I
>found), or for RS_SQLite_exec, or for any of the functions in the call
>chain between RS_SQLite_exec and RS-DBI, or for RS-DBI?
>
>Because If I am to continue working with the RSQLite interface, it is
>pretty clear that I will need to know how to do that.
>
>(I made up this term, "call chain". Is there a better name to refer to
>functions intermediate between a calling function and a subsequently
>called
>function?)
>
>Again, thanks for your help. I could have worked on that for a year and
>it
>would never have occurred to me that the period might be the problem.
>
>Warmest regards, andrewH
>
>
>On Sat, Jan 4, 2014 at 5:03 PM, Ista Zahn <istazahn at gmail.com> wrote:
>
>> 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.
>>
More information about the R-help
mailing list