0

I'm executing SQL using RPostgreSQL and sqldf packages.

Connection:

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="dbname",host="host",port=5432,user="usr",password="pwd")

Then I build a pretty complex SQL statement and at the end I have (vSQL is the SQL select statement):

vSQLDF <- sqldf(vSQL, connection=con)

Now I get the following error:

Error in dbPreExists && !overwrite : invalid 'x' type in 'x && y'

I was not able to create any reproducible example, all I can say is:

  1. The generated SQL statement works when executed directly in the database (I've copied the string from R to pgAdmin)
  2. The connection works, I can use sqldf("select * from any_table, connection=con")
  3. It works when I generate shorter SQL using different input parameters for my script
  4. The SQL length only is not a problem, I've tried: inSQL = paste(rep("select 1::int ",10000),collapse=" union all "), which results in different error: RS-DBI driver: (could not Retrieve the result : ERROR: stack depth limit exceeded
  5. There are no strange characters in my SQL statement

Any idea what this error means?

Session info:

> sessionInfo()
R version 2.15.2 (2012-10-26)
Platform: x86_64-pc-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=cs_CZ.UTF-8       LC_NUMERIC=C               LC_TIME=en_GB.UTF-8        LC_COLLATE=cs_CZ.UTF-8     LC_MONETARY=en_GB.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=C                 LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_GB.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] tcltk     stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] lattice_0.20-13       sqldf_0.4-6.4         RSQLite.extfuns_0.0.1 RSQLite_0.11.2        chron_2.3-43          gsubfn_0.6-5         
 [7] proto_0.3-10          RPostgreSQL_0.3-3     DBI_0.2-5            

loaded via a namespace (and not attached):
[1] grid_2.15.2  tools_2.15.2

PostgreSQL:

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

RStudio 0.97.332

lmo
  • 37,904
  • 9
  • 56
  • 69
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155

2 Answers2

1

Without the code and a modest amount of background detail, this is just a guessing game, but I see nowhere that you have configured sqldf to use a different driver than the default with the appropriate options. You have not mentioned that you have followed all the instructions relevant to PostgreSQL in the sqldf page at googlecode.com:

In the sqldf code is a line:

if (request.con) dbPreExists <- attr(connection, "dbPreExists")

You can perhaps get insight by supplyin g you connection object to a similar call:

dbPreExists <- attr(connection, "dbPreExists") #see error gets thrown.

You can also debug sqldf with:

debug(sqldf)  # then repeat operation

A browser session will be started and you can single step through the process.

IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Yeah, I just thought someone has seen this type of error before. The problem is that I have two valid SQL queries and one them just returns this cryptic error using sqldf. It won't help if I copy the code here as both SQLs are really long and with complex business logic. Btw. my connection is defined correctly, see the first two lines of code where `con` is defined, including PostgreSQL driver. – Tomas Greif Jul 01 '13 at 16:48
  • I saw the first two lines. I didn't see mention that the options had been set as specified in the cited page. – IRTFM Jul 01 '13 at 16:55
0

Response from Gabor Grothendieck:

I don't think manual connections were ever tested with postgres. Use RPostgreSQL directly if you want to use those.

Manual and automatic connections do work in sqldf with sqlite and automatic connections do work in sqldf with RPostgreSQL.

See his original post for entire response.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155