Question: max query length in RdbiPgSQL
0
gravatar for William McCoy
14.2 years ago by
William McCoy20 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. I am not a regular subscriber to bioconductor, so please reply to me personally if you can help. Thanks. -- Dr. William D. McCoy Assoc. Prof. Geosciences University of Massachusetts, Amherst wdmccoy at geo.umass.edu
rdbipgsql • 579 views
ADD COMMENTlink modified 14.2 years ago by Seth Falcon7.4k • written 14.2 years ago by William McCoy20
Answer: max query length in RdbiPgSQL
0
gravatar for Seth Falcon
14.2 years ago by
Seth Falcon7.4k
Seth Falcon7.4k 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
ADD COMMENTlink written 14.2 years ago by Seth Falcon7.4k
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
ADD REPLYlink written 14.2 years ago by William McCoy20
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 16.09
Traffic: 223 users visited in the last hour