[R] maximum string length in RdbiPgSQL and in R

[William D. McCoy]

Joe, Thanks, for your response.  A few hours ago I sent the following to 
others that had responded to my message on the bioconductor list:

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 e-mail supposed, the length of the query 
string was not the problem.  And I found it doesn't matter if I type in 
the queries at the terminal or use readLines() to take the query from a 
file, so there is no problem with hidden characters, etc.

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


Joe Conway wrote:
> William McCoy wrote:
> 
>>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, 
>>moderately long query (12 lines, 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 or for strings in R?
> 
> 
> I tried using this for a "queryfile"
> 
> 8<----------------
> select
> length(
> '0123456789...repaeted for total length of 500...0123456789'
> )
> 8<----------------
> 
> and it works fine for me:
> 
> 8<----------------
>  > conn <- dbConnect(PgSQL(),dbname="regression")
>  > sql <- readLines("/tmp/queryfile")
>  > df <- dbGetQuery(conn, paste(sql, collapse = " "))
>  > df
>    length
> 1    500
> 8<----------------
> 
> so I don't think length is the issue. Maybe you have an embedded control 
> character? Or is it possible that you are introducing a space somewhere 
> unexpected in your query, preventing a match? Try doing
>    paste(test.sql, collapse = " ")
> and then cut and paste the result into psql.
> 
> HTH,
> 
> Joe
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
> 


-- 

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