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 <istaz...@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 <ahoer...@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 peoples 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. Thats
> > 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.
>
--
J. Andrew Hoerner
Director, Sustainable Economics Program
Redefining Progress
(510) 507-4820
[[alternative HTML version deleted]]
______________________________________________
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.
