[R] maximum string length in RdbiPgSQL and in R

[William McCoy]

Because my problem involves the RdbiPgSQL package, I sent a message 
similar to this one to the Bioconductor list.  But while awaiting 
moderator approval of my message (because I am not a member of that 
list), it occurred to me to send it to R-help as the problem may be more 
general than just RdbiPgSQL.

Here's my situation:

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, 
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?

By the way, I can use collapse = "\n" in paste() and get the same 
result, so I don't think line length is the problem.

Or maybe someone has a better idea of how to read (in R) the file 
containing the query and sending it to my database.  Of course, I know I 
can execute the query outside of R and use read.table to make my 
dataframe, but I want to do this inside R.

Thanks for any ideas.


-- 

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