From: | "Woody Woodring" <george(dot)woodring(at)iglass(dot)net> |
---|---|
To: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | JDBC rewriting a bad query? |
Date: | 2008-03-17 19:29:17 |
Message-ID: | 01f901c88865$31530d20$80b1a8c0@istructure.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
I have an issue that I am trying to understand. We had an issue where we
had a query with a syntax error, but we would not get any errors in the log
files. It turns out that the query was being shortened by the time it got
to the server and not causing an error. The version of jdbc driver we are
running is 8.2-507
QUERY FROM WEB SERVER: ( the problem is one too many close parens ')' )
14:01:43,922 DEBUG [syr/lloyd/9333C6.W01] AvailabilityDAOSql: SET
search_path = 'public'
14:01:44,307 DEBUG [syr/lloyd/9333C6.W01] AvailabilityDAOSql: SELECT date,
enddate, status, threshdesc, pollgrpid, pollid, REPLACE(polldata,'dhct:','')
AS pollname FROM outagelog JOIN threshold USING(thresholdid, pollid) JOIN
poll USING (pollid) WHERE (status != 3) AND (lower(threshdesc) !~ 'dropped')
AND pollid IN (SELECT pollid FROM poll WHERE (rrdtypeid = 73) AND
(split_part(polldata,':',2)='IR')) AND (dsnum=2)) AND ((enddate >
1205640000) OR (enddate IS NULL)) AND (date < 1205726400) ORDER BY date,
status DESC
DEBUG FROM PGSQL SERVER: ( Chopped off everything after and including bad
')' )
Mar 17 14:01:43 brain03 postgres[9174]: [5-1] LOG: execute <unnamed>: SET
search_path = 'public'
Mar 17 14:01:44 brain03 postgres[9174]: [6-1] LOG: execute <unnamed>:
SELECT date, enddate, status, threshdesc, pollgrpid, pollid,
REPLACE(polldata,'dhct:','') AS pollname
Mar 17 14:01:44 brain03 postgres[9174]: [6-2] FROM outagelog JOIN threshold
USING(thresholdid, pollid) JOIN poll USING (pollid) WHERE (status != 3) AND
(lower(threshdesc) !~
Mar 17 14:01:45 brain03 postgres[9174]: [6-3] 'dropped') AND pollid IN
(SELECT pollid FROM poll WHERE (rrdtypeid = 73) AND
(split_part(polldata,':',2)='IR')) AND
Mar 17 14:01:45 brain03 postgres[9174]: [6-4] (dsnum=2)
WEB QUERY IN PSQL:
Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
syr=> SELECT date, enddate, status, threshdesc, pollgrpid, pollid,
REPLACE(polldata,'dhct:','') AS pollname FROM outagelog JOIN threshold
USING(thresholdid, pollid) JOIN poll USING (pollid) WHERE (status != 3) AND
(lower(threshdesc) !~ 'dropped') AND pollid IN (SELECT pollid FROM poll
WHERE (rrdtypeid = 73) AND (split_part(polldata,':',2)='IR')) AND (dsnum=2))
AND ((enddate > 1205640000) OR (enddate IS NULL)) AND (date < 1205726400)
ORDER BY date, status DESC;
ERROR: syntax error at or near ")" at character 366
We have found our issue, but it would have been nice to have had an error.
Could be we just need to be more conservative in our use of parens with
jdbc.
Thanks,
Woody
----------------------------------------
iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net
From | Date | Subject | |
---|---|---|---|
Next Message | robert engels | 2008-03-17 22:44:43 | setQueryTimeout problem !?!?! |
Previous Message | leonel | 2008-03-14 14:30:40 | Re: Blobs |