[R] maximum string length in RdbiPgSQL and in R

[Joe Conway]

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