IDS Forum

Re: Syntax error on this query, but only when ....

Posted By: Fernando Nunes
Date: Monday, 25 February 2013, at 10:34 a.m.

In Response To: RE: Syntax error on this query, but only when .... (Paul Watson)

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

Messages In This Thread

• Syntax error on this query, but only when supplyin
  Laird Nelson -- Saturday, 23 February 2013, at 1:35 a.m.
  • Re: Syntax error on this query, but only when supp
    Ivan Zavis DBA -- Saturday, 23 February 2013, at 4:59 a.m.
    • Re: Syntax error on this query, but only when ....
      Laird Nelson -- Sunday, 24 February 2013, at 1:46 p.m.
      • Re: Syntax error on this query, but only when ....
        Art Kagel -- Sunday, 24 February 2013, at 1:56 p.m.
        • Re: Syntax error on this query, but only when ....
          Laird Nelson -- Monday, 25 February 2013, at 10:06 a.m.
          • RE: Syntax error on this query, but only when ....
            Paul Watson -- Monday, 25 February 2013, at 10:18 a.m.
            • Re: Syntax error on this query, but only when ....
              Laird Nelson -- Monday, 25 February 2013, at 10:23 a.m.
            • Re: Syntax error on this query, but only when ....
              Fernando Nunes -- Monday, 25 February 2013, at 10:34 a.m.
              • Re: Syntax error on this query, but only when ....
                Laird Nelson -- Monday, 25 February 2013, at 10:48 a.m.
                • Re: Syntax error on this query, but only when ....
                  Fernando Nunes -- Monday, 25 February 2013, at 10:50 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.