[BioC] max query length in RdbiPgSQL

[William McCoy]

Well I've think I've sorted this out.  First of all, all of the queries 
I have tested on RdbiPgSQL have been ones that worked with psql and I 
later found out they also all work fine with RODBC when sent to my 
PostgreSQL database.

As those who responded to my query supposed, the length of the query 
string was not the problem.  And it doesn't matter if I type in the 
queries at the terminal or use readLines() to take the query from a file.

It turns out that the queries that failed in RdbiPgSQL (and worked fine 
in RODBC and psql) are those that used a "date" data type in the "where" 
clause.  Maybe this is a known limitation of RdbiPgSQL -- I hadn't seen 
that documented anywhere and I don't understand it since the query 
presumably is just sent to the database backend and the results should 
be returned.

I do notice that using RdbiPgSQL results in dataframes having columns 
with no attributes.  Whereas when I use RODBC the resulting dataframes 
have appropriate attributes such as class "factor" and class "date". 
But I still don't see why the results don't show up in my dataframe when 
a date field is used as a constraint in a "where" clause when using 
RdbiPgSQL.

By the way, I should have said this is with R 2.1.1, Rdbi 1.1.2, and 
RdbiPgSQL 1.1.4.

I think for now I will use RODBC.  It appears to be more robust, more 
useful (attribute-wise), and more versatile (should work with other 
databases).

I thank everyone for their help.

Bill

Seth Falcon wrote:
> On 13 Sep 2005, wdmccoy at geo.umass.edu wrote:
> 
> 
>>I have been using RdbiPgSQL successfully for a year or two.  I
>>commonly save my queries in text files that I can use either in
>>PostgreSQL's psql (useful for testing and editing) or in R using
>>readLines().  For example (in R):
>>
>>library(RdbiPgSQL)
>>conn <- dbConnect(PgSQL(), host = "localhost", dbname = "agdb")
>>test.sql < readLines("queryfile")
>>test.df <- dbGetQuery(conn, paste(test.sql, collapse = " "))
>>
>>This works fine for all the multiline files I have tried -- except
>>one.  I have recently encountered a problem with a moderately
>>complex, long query (459 characters).  I can execute the query with
>>no problem in psql and it returns the 14 rows that I expect.  When I
>>execute the query in R as above, I get a dataframe with the expected
>>column names, but no rows.  I get no error message.  I am wondering
>>if the query string is too long.  Is there a maximum length for
>>queries in RdbiPgSQL.
> 
> 
> Disclaimer: I haven't had an opportunity to play with the RdbiPgSQL
> package.
> 
> A quick look at the code does not reveal any obvious max length for
> queries.
> 
> Can you try a different long query?  For example, it should be easy to
> construct very long queries using AS:
> 
> SELECT name AS "really long string here" FROM someTable LIMIT 20;
> 
> And there are probably better ways that I'm not thinking of.
> 
> 459 seems not that big, so I suspect there is something else going
> on.  I would examine the query, perhaps there is a quoting/comment
> char issue?   
> 
> + seth
> 

-- 

William D. McCoy
Geosciences
University of Massachusetts, Amherst
wdmccoy at geo.umass.edu