R help - Nov 2010 - sqldf error only on Unix not Windows

Hello Group,

                I am having trouble with the sqldf package on unix.  The same
code works fine on windows.

Silly Example script:

# Load the package
library(sqldf)

# Use the titanic data set

data(women)
colnames(women)
head(women)

sqldf('select height, count(*) from women where height is not null group by
weight')

Unix Output and error:

bash-3.00$ R --vanilla <testR.R

R version 2.10.1 (2009-12-14)
Copyright (C) 2009 The R Foundation for Statistical Computing
ISBN 3-900051-07-0

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> # Load the package
> library(sqldf)
Loading required package: DBI
Loading required package: RSQLite
Loading required package: RSQLite.extfuns
Loading required package: gsubfn
Loading required package: proto
Loading required package: tcltk
Loading Tcl/Tk interface ... done
Loading required package: chron
>
>
> # Use the titanic data set
>
> data(women)
> colnames(women)
[1] "height" "weight"
> head(women)
  height weight
1     58    115
2     59    117
3     60    120
4     61    123
5     62    126
6     63    129
>
> sqldf('select height, count(*) from women where height is not null
group by weight')
Error in sqliteFetch(rs, n = -1, ...) :
  RSQLite driver: (RS_SQLite_fetch: failed first step: SQL logic error or
missing database)
Calls: sqldf ... dbGetQuery -> sqliteQuickSQL -> sqliteFetch -> .Call
Error in !dbPreExists : invalid argument type
Calls: sqldf ... dbGetQuery -> sqliteQuickSQL -> sqliteFetch -> .Call
Execution halted
Warning message:
RS-DBI driver warning: (closing pending result sets before closing this
connection)


Box:
Solaris: SunOS icsun7 5.10 Generic_118822-25 sun4u sparc SUNW,Sun-Fire-280R



I am unable to find any hits on the generated errors which makes me think either
something is really wrong (resource issues or something) or this is not a common
use of sqldf??  Is there a better package I should be using?

Thank you very much for your help,

 Alex Bryant
Software Development
Integrated Clinical Systems, Inc.
908-996-7208


________________________________
Confidentiality Note: This e-mail, and any attachment to...{{dropped:13}}

On Mon, Nov 1, 2010 at 9:28 AM, Alex Bryant <abryant at i-review.com>
wrote:
> Hello Group,
>
> ? ? ? ? ? ? ? ?I am having trouble with the sqldf package on unix. ?The
same code works fine on windows.
>
> Silly Example script:
>
> # Load the package
> library(sqldf)
>
> # Use the titanic data set
>
> data(women)
> colnames(women)
> head(women)
>
> sqldf('select height, count(*) from women where height is not null
group by weight')
>
Some things to try:

-  try adding dbname = tempfile() argument to your sqldf statement and
see if that makes any difference

- try it with the H2 database rather than sqlite (or with PostgreSQL)
  To use it with H2 make sure you have Java and the CRAN package, RH2,
installed.
  RH2 includes the H2 database itself so you don't need to install that.
  Then issue this line in R any time before your first sqldf call
     library(RH2)
  sqldf will notice it and automatically use the H2 database instead of sqlite

- try it with R 2.11

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com