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