Syntax error on this query, but only when supplyin
A JDBC/EclipseLink-generated query failed with error -201 on Informix 11.50, but only as a prepared statement: SELECT CASE WHEN (COUNT(id)=?) THEN ? ELSE (MAX(version)+?) END ... Testing showed the problem was the '?' placeholder in the THEN branch of the CASE; with a literal 1 there it worked. The explanation offered was that Informix must know the parameter's data type at parse time, unlike some other databases. Suggested workaround: cast the placeholder, e.g. ?::INT (or CAST). The poster was still trying to get EclipseLink to emit that cast, so no confirmed outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Had a hard time coming up with a descriptive subject; sorry. The following query is being output by our ORM (EclipseLink). Informix 11.50 is saying there's a syntax error (-201; coming from the database itself, not from EclipseLink or the JDBC driver). The query is as follows: SELECT CASE WHEN (COUNT(id) = ?) THEN ? ELSE (MAX(version) + ?) END FROM ngp.rules_document WHERE ((rule_status <> ?) AND (file_name = ?)) (The question marks are where the Informix JDBC driver substitutes parameters when preparing statements. It is only when these parameter slots are involved that the syntax error is thrown. To be clear, the question marks themselves are not being sent to the database.) With the slots filled in by hand (i.e. when you run the query as a regular old statement, not as a prepared statement) you get: SELECT CASE WHEN (COUNT(id) = 0) THEN 1 ELSE (MAX(version) + 1) END FROM ngp.rules_document WHERE ((rule_status <> 'TEST') AND (file_name = 'foobar')) But that query runs fine without any syntax error (unless I've mistyped it :-)). So what is the source of the -201 error? Is there some kind of "gotcha" around the JDBC driver and PreparedStatements in this regard? Best, Laird -- http://about.me/lairdnelson --001636c5c2583529bb04d65e83d8
Try COUNT(*) instead of COUNT(id). I think that count(some_value) is supported from version 11.70xy only. On 23.02.2013 07:35, Laird Nelson wrote: > Had a hard time coming up with a descriptive subject; sorry. > > The following query is being output by our ORM (EclipseLink). Informix > 11.50 is saying there's a syntax error (-201; coming from the database > itself, not from EclipseLink or the JDBC driver). > > The query is as follows: > > SELECT CASE WHEN (COUNT(id) = ?) THEN ? ELSE (MAX(version) + ?) END FROM > ngp.rules_document WHERE ((rule_status<> ?) AND (file_name = ?)) > > (The question marks are where the Informix JDBC driver substitutes > parameters when preparing statements. It is only when these parameter > slots are involved that the syntax error is thrown. To be clear, the > question marks themselves are not being sent to the database.) > > With the slots filled in by hand (i.e. when you run the query as a regular > old statement, not as a prepared statement) you get: > > SELECT CASE WHEN (COUNT(id) = 0) THEN 1 ELSE (MAX(version) + 1) END FROM > ngp.rules_document WHERE ((rule_status<> 'TEST') AND (file_name = > 'foobar')) > > But that query runs fine without any syntax error (unless I've mistyped it > :-)). > > So what is the source of the -201 error? Is there some kind of "gotcha" > around the JDBC driver and PreparedStatements in this regard? > > Best, > Laird > -- Ivan Zaviç System& DB Administrator Mobile:+381-69-846-99-08 mail: ivan.zavis@mi-system.co.rs _________________________________________________________ M&I SYSTEMS CO. Bulevar vojvode Stepe 16, 21000 Novi Sad, Serbia Tel/Fax: +381-(0)21-68-98-608 Mail: info@mi-system.co.rs, URL: http://www.mi-system.co.rs Odricanje od odgovornosti: Ovaj dokument namenjen je samo licima kojima je upuen i za pozivanje na isti od stane bilo kog lica, neophodna je naknadna pismena potvrda njegovog sadr§aja. Shodno tome, M&I Systems, Co. Novi Sad odrie svaku odgovornost i ne prihvata bilo kakvu obavezu (ukljuujui sluaj nepa§nje) za posledice koje mo§e pretrpeti bilo koje lice zbog injenja ili neinjenja na bazi takve informacije pre nego çto takva lica prime dodatnu pismenu potvrdu. Ukoliko ste greçkom primili ovu elektronsku poruku, uniçtite ili izbriçite istu sa vaçeg raunara. Svako umno§avanje, çirenje, kopiranje, obelodanjivanje, izmene, distribucija i/ili objavljivanje ove elektronske poruke je strogo zabranjeno. Sadr§aj ove elektronske poruke ne predstavlja nu§no stavove M&I Systems, Co. Novi Sad
On Sat, Feb 23, 2013 at 1:59 AM, Ivan Zavis DBA <ivan.zavis@mi-system.co.rs>wrote: > Try COUNT(*) instead of COUNT(id). I think that count(some_value) is > supported from version 11.70xy only. > Thanks, Ivan; however, the SQL query as I described in my email works fine when input manually (i.e. without prepared statement binding). So I don't think that the COUNT syntax is the problem. (And the way that the SQL itself is output is under the control of the ORM--we can influence it a little, but not a lot.) Thanks again, Best, Laird -- http://about.me/lairdnelson --20cf301b638977e9dd04d67cd7b4
Have you tried to capture what the SQL looks like when the engine sees it?
Did you try running onstat -g SQL or -g SES immediately after the error is
returned to see what then engine saw and was complaining about? Maybe the
placeholders in the SQL the applications is generating are not being
replaced correctly?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Sun, Feb 24, 2013 at 1:46 PM, Laird Nelson <ljnelson@gmail.com> wrote:
> On Sat, Feb 23, 2013 at 1:59 AM, Ivan Zavis DBA
> <ivan.zavis@mi-system.co.rs>wrote:
>
> > Try COUNT(*) instead of COUNT(id). I think that count(some_value) is
> > supported from version 11.70xy only.
> >
>
> Thanks, Ivan; however, the SQL query as I described in my email works fine
> when input manually (i.e. without prepared statement binding). So I don't
> think that the COUNT syntax is the problem.
>
> (And the way that the SQL itself is output is under the control of the
> ORM--we can influence it a little, but not a lot.)
>
> Thanks again,
> Best,
> Laird
>
> --
> http://about.me/lairdnelson
>
> --20cf301b638977e9dd04d67cd7b4
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec554d2329ce6ed04d67cf987
On Sun, Feb 24, 2013 at 10:56 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Have you tried to capture what the SQL looks like when the engine sees it?
> Did you try running onstat -g SQL or -g SES immediately after the error is
> returned to see what then engine saw and was complaining about? Maybe the
> placeholders in the SQL the applications is generating are not being
> replaced correctly?
It is definitely the placeholders, but it still makes no sense to me. It
is the "THEN 1" clause--specifically treating its operand as a parameter.
EclipseLink wants to use a parameter slot for the 1 value ("THEN ?"), and
Informix doesn't seem to handle that (other databases have no problems
here?).
Here's some simple JDBC test code snippets from a colleague. Note below
that the SQL does NOT make the value supplied to the THEN clause a
parameter. It is only when this numeric literal 1 value is used that this
statement can succeed. But if you change the 1 into a parameter and then
set its value using JDBC prepared statement syntax, you get a syntax error.
So the following works:
String testSql = "SELECT CASE WHEN (COUNT(id) = ?) THEN 1 ELSE
(MAX(version) + ?) END FROM ngp.rules_document WHERE ((status <> ?) AND
(file_name = ?))";
stmt = conn.prepareStatement(testSql);
System.out.println(testSql);
stmt.setLong(1, 0);
stmt.setLong(2, 1);
stmt.setString(3, "TEST");
stmt.setString(4, "FinancialRules.xls");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
}
But change that 1 to a ? and the stmt.setLong(2, 1) will cause the
statement execution to fail.
Is there something forbidden about setting parameter values in case
statements?
Our next step is to run onstat -g SQL but I thought maybe this might yield
some answers in the meantime. Thanks everyone for your time.
Best,
Laird
--
http://about.me/lairdnelson
--001636c92ca7ace56a04d68de35c
Have you tried quoting the 1 ? Or maybe something like 1::INT - just
something that EclipseLink will not get confused by
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Laird
Nelson
Sent: Monday, February 25, 2013 9:07 AM
To: ids@iiug.org
Subject: Re: Syntax error on this query, but only when .... [29591]
On Sun, Feb 24, 2013 at 10:56 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Have you tried to capture what the SQL looks like when the engine sees it?
> Did you try running onstat -g SQL or -g SES immediately after the error is
> returned to see what then engine saw and was complaining about? Maybe the
> placeholders in the SQL the applications is generating are not being
> replaced correctly?
It is definitely the placeholders, but it still makes no sense to me. It
is the "THEN 1" clause--specifically treating its operand as a parameter.
EclipseLink wants to use a parameter slot for the 1 value ("THEN ?"), and
Informix doesn't seem to handle that (other databases have no problems
here?).
Here's some simple JDBC test code snippets from a colleague. Note below
that the SQL does NOT make the value supplied to the THEN clause a
parameter. It is only when this numeric literal 1 value is used that this
statement can succeed. But if you change the 1 into a parameter and then
set its value using JDBC prepared statement syntax, you get a syntax error.
So the following works:
String testSql = "SELECT CASE WHEN (COUNT(id) = ?) THEN 1 ELSE
(MAX(version) + ?) END FROM ngp.rules_document WHERE ((status <> ?) AND
(file_name = ?))";
stmt = conn.prepareStatement(testSql);
System.out.println(testSql);
stmt.setLong(1, 0);
stmt.setLong(2, 1);
stmt.setString(3, "TEST");
stmt.setString(4, "FinancialRules.xls");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
}
But change that 1 to a ? and the stmt.setLong(2, 1) will cause the
statement execution to fail.
Is there something forbidden about setting parameter values in case
statements?
Our next step is to run onstat -g SQL but I thought maybe this might yield
some answers in the meantime. Thanks everyone for your time.
Best,
Laird
--
http://about.me/lairdnelson
--001636c92ca7ace56a04d68de35c
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
On Mon, Feb 25, 2013 at 7:18 AM, Paul Watson <paul@oninit.com> wrote: > Have you tried quoting the 1 ? I can't; the SQL is actually under the control of our ORM ( http://www.eclipse.org/eclipselink/). They can't understand why Informix would have a problem with the syntax ( http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg07794.html). Best, Laird -- http://about.me/lairdnelson --20cf301b63894b43d904d68e20f9
I think Paulo got it... But It's not Eclipse which is getting confused.
It's the engine and I suppose the cause is because it needs to know the
type of the parameters when parsing the statement.
So, using ::INT after "?" should solve the issue...
I don't know how other databases handle this.... maybe they wait until bind
time... Apparently Informix doesn't handle it like that...
I tried using 11.50.FC9 and 4GL...
Regards
On Mon, Feb 25, 2013 at 3:18 PM, Paul Watson <paul@oninit.com> wrote:
> Have you tried quoting the 1 ? Or maybe something like 1::INT - just
> something that EclipseLink will not get confused by
>
> Cheers
> Paul
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Laird
> Nelson
> Sent: Monday, February 25, 2013 9:07 AM
> To: ids@iiug.org
> Subject: Re: Syntax error on this query, but only when .... [29591]
>
> On Sun, Feb 24, 2013 at 10:56 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Have you tried to capture what the SQL looks like when the engine sees
> it?
>
> > Did you try running onstat -g SQL or -g SES immediately after the error
> is
>
> > returned to see what then engine saw and was complaining about? Maybe the
> > placeholders in the SQL the applications is generating are not being
> > replaced correctly?
>
> It is definitely the placeholders, but it still makes no sense to me. It
> is the "THEN 1" clause--specifically treating its operand as a parameter.
> EclipseLink wants to use a parameter slot for the 1 value ("THEN ?"), and
> Informix doesn't seem to handle that (other databases have no problems
> here?).
>
> Here's some simple JDBC test code snippets from a colleague. Note below
> that the SQL does NOT make the value supplied to the THEN clause a
> parameter. It is only when this numeric literal 1 value is used that this
> statement can succeed. But if you change the 1 into a parameter and then
> set its value using JDBC prepared statement syntax, you get a syntax error.
>
> So the following works:
>
> String testSql = "SELECT CASE WHEN (COUNT(id) = ?) THEN 1 ELSE
> (MAX(version) + ?) END FROM ngp.rules_document WHERE ((status <> ?) AND
> (file_name = ?))";
>
> stmt = conn.prepareStatement(testSql);
> System.out.println(testSql);
> stmt.setLong(1, 0);
> stmt.setLong(2, 1);
> stmt.setString(3, "TEST");
> stmt.setString(4, "FinancialRules.xls");
> ResultSet rs = stmt.executeQuery();
> while (rs.next()) {
> System.out.println(rs.getInt(1));
> }
>
> But change that 1 to a ? and the stmt.setLong(2, 1) will cause the
> statement execution to fail.
> Is there something forbidden about setting parameter values in case
> statements?
>
> Our next step is to run onstat -g SQL but I thought maybe this might yield
> some answers in the meantime. Thanks everyone for your time.
>
> Best,
> Laird
>
> --
> http://about.me/lairdnelson
>
> --001636c92ca7ace56a04d68de35c
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf307813fcb7d1a304d68e4528
On Mon, Feb 25, 2013 at 7:34 AM, Fernando Nunes <domusonline@gmail.com>wrote: > It's the engine and I suppose the cause is because it needs to know the > type of the parameters when parsing the statement. > So, using ::INT after "?" should solve the issue... > I don't know how other databases handle this.... maybe they wait until bind > time... Apparently Informix doesn't handle it like that... > Thanks, Fernando; working with EclipseLink now to see if there's a way to override something and add "::INT" to certain parameter placeholders. Best, Laird -- http://about.me/lairdnelson --20cf301b639581a13404d68e7750
::INT would be a shorcut to CAST()... Maybe that's easier? On Mon, Feb 25, 2013 at 3:48 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Mon, Feb 25, 2013 at 7:34 AM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > It's the engine and I suppose the cause is because it needs to know the > > type of the parameters when parsing the statement. > > So, using ::INT after "?" should solve the issue... > > I don't know how other databases handle this.... maybe they wait until > bind > > time... Apparently Informix doesn't handle it like that... > > > > Thanks, Fernando; working with EclipseLink now to see if there's a way to > override something and add "::INT" to certain parameter placeholders. > > Best, > Laird > > -- > http://about.me/lairdnelson > > --20cf301b639581a13404d68e7750 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf307c9bba82416604d68e7f95