Re: parameter select sql failing to prepare
This was in another thread recently: host variables cannot be in the SELECT
list directly, but can always be function arguments. There are many
solutions to your problem using this, but the simplest is probably:
INSERT INTO groupitem (GroupID, ItemID)
SELECT NVL(?, NULL), itemid FROM itemmaster WHERE itemnbr = ?;
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Mel Haynes Jr" <mhaynesjr@pcisys.net> wrote in message
news:10aidjrm6ih8g45@corp.supernews.com...
> I am testing my current java application against Informix (9.4 for
Windows)
> and am getting an error when I try to prepare the following statement:
>
> // GroupID, ItemID are both integers
> // ItemNbr is a string
> INSERT INTO GROUPITEM (GroupID, ItemID)> select ?, itemid from itemmaster where itemnbr = ?;
>
> I am getting the following error:
> ERROR: SQLException: -201 - A syntax error has occurred.
>
> Now I narrowed it down to the SELECT portion of the SQL. It doesn't like
the
> ? in the select statement. I know this because I substituted the ? in the
> select statement with a 1 just for testing and it prepared just fine.
>
> This same sql statement works just fine in SQL Server, which this app was
> first tested. My question is this: Why is this causing an error? I am
> staring at my old 7.X documentation ( yes I have all the manuals ) and in
> the Informix-SQL quick syntax guide, it looks like it should be a valid
SQL
> statement. Is this a problem with the Informix JDBC driver or a problem
with
> Informix itself? I have no way of testing this in any other environment. I
> turned on explain and nothing came out. Any advice on what I can do to
> diagnose or fix this problem would be highly appreciated. Thanks.
>
> Mel Haynes Jr
> Retail Solutions Group, LLC
Doug,
Thanks for your response. I will take a look at some solutions. I spent a
lot of time trying to get Google to return a solution, but I guess I was not
querying the engine right.
"Doug Lawry" <lawry@nildram.co.uk> wrote in message
news:c8g40b$e7b$1@nntp0.reith.bbc.co.uk...
> This was in another thread recently: host variables cannot be in the
SELECT
> list directly, but can always be function arguments. There are many
> solutions to your problem using this, but the simplest is probably:
>
> INSERT INTO groupitem (GroupID, ItemID)
> SELECT NVL(?, NULL), itemid FROM itemmaster WHERE itemnbr = ?;>
> --
> Regards,
> Doug Lawry
> www.douglawry.webhop.org
>
>
> "Mel Haynes Jr" <mhaynesjr@pcisys.net> wrote in message
> news:10aidjrm6ih8g45@corp.supernews.com...
> > I am testing my current java application against Informix (9.4 for
> Windows)
> > and am getting an error when I try to prepare the following statement:
> >
> > // GroupID, ItemID are both integers
> > // ItemNbr is a string
> > INSERT INTO GROUPITEM (GroupID, ItemID)> > select ?, itemid from itemmaster where itemnbr = ?;
> >
> > I am getting the following error:
> > ERROR: SQLException: -201 - A syntax error has occurred.
> >
> > Now I narrowed it down to the SELECT portion of the SQL. It doesn't like
> the
> > ? in the select statement. I know this because I substituted the ? in
the
> > select statement with a 1 just for testing and it prepared just fine.
> >
> > This same sql statement works just fine in SQL Server, which this app
was
> > first tested. My question is this: Why is this causing an error? I am
> > staring at my old 7.X documentation ( yes I have all the manuals ) and
in
> > the Informix-SQL quick syntax guide, it looks like it should be a valid
> SQL
> > statement. Is this a problem with the Informix JDBC driver or a problem
> with
> > Informix itself? I have no way of testing this in any other environment.
I
> > turned on explain and nothing came out. Any advice on what I can do to
> > diagnose or fix this problem would be highly appreciated. Thanks.
> >
> > Mel Haynes Jr
> > Retail Solutions Group, LLC
>
>