RSQLite: Tracking an error message to its source

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

+ 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 

it again with a full path name, like this:

ImportRSQLite

+ 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 

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 

&  path (if elsewhere) of your
# csv data file; a list containing the column names & optional 

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",

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

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

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

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?

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.