host variable in select sentence gives -201

Hi, I've recently migrated Informix Server from 7.31 to 11.50. Now, a program is failing with error code -201 (syntax error). This program runs with no error in 7.31. A short version of the program is here: main() { EXEC SQL BEGIN DECLARE SECTION; int x; int r; EXEC SQL END DECLARE SECTION; EXEC SQL DATABASE "clientes@clientes"; x=2; r=0; EXEC SQL SELECT nvl(dummy + :x,0) INTO :r from rvi_dual; printf("return code: %d\\ ",sqlca.sqlcode); printf("r = %d\\ ",r); exit 0; } The rvi_dual is: create table "desclie".rvi_dual ( dummy integer ); insert into rvi_dual values (1) Is this a bug ? Thanks in advance Roger

What is your current INFORMIXSERVER when you run this? Regards. On Mon, Jun 13, 2011 at 11:51 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > Hi, > I've recently migrated Informix Server from 7.31 to 11.50. > Now, a program is failing with error code -201 (syntax error). This program > runs with no error in 7.31. A short version of the program is here: > > main() > { > EXEC SQL BEGIN DECLARE SECTION; > int x; > int r; > EXEC SQL END DECLARE SECTION; > > EXEC SQL DATABASE "clientes@clientes"; > > x=2; > > r=0; > > EXEC SQL SELECT nvl(dummy + :x,0) INTO :r from rvi_dual; > > printf("return code: %d\\ ",sqlca.sqlcode); > > printf("r = %d\\ ",r); > > exit 0; > } > > The rvi_dual is: > > create table "desclie".rvi_dual > ( > > dummy integer > ); > > insert into rvi_dual values (1)> > Is this a bug ? > > Thanks in advance > > Roger > > > > ******************************************************************************* > 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... --0016e6d27210f8d98804a59fe693

INFORMIXSERVER=clientes

Roger Vilca Wrote: -------------------------------------------------------------------------------- Hi, I've recently migrated Informix Server from 7.31 to 11.50. Now, a program is failing with error code -201 (syntax error). This program runs with no error in 7.31. A short version of the program is here: main() { EXEC SQL BEGIN DECLARE SECTION; int x; int r; EXEC SQL END DECLARE SECTION; EXEC SQL DATABASE "clientes@clientes"; x=2; r=0; EXEC SQL SELECT nvl(dummy + :x,0) INTO :r from rvi_dual; printf("return code: %d\\ ",sqlca.sqlcode); printf("r = %d\\ ",r); exit 0; } The rvi_dual is: create table "desclie".rvi_dual ( dummy integer ); insert into rvi_dual values (1) Is this a bug ? Thanks in advance Roger -------------------------------------------------------------------------------- Response: I don't see anything specific with the short version of code you've posted. However, I might suggest checking your program for Informix 11.5 Keywords. We ran into a similar problem when upgrading from 9.3 to 9.4. In our case, we found an SQL statement using the keyword CYCLE as a table alias worked on 9.3, but returned a 201 error on 9.4.

Hi Dave, Thanks for your response. If you run this example program, this gives the error -201 ? I noticed that the select have the function nvl, but this function already existed in 7.31

Roger: I finally got a few minutes to test your problem on my server (11.70.FC1 on Linux). Here's what I've figured out. The problem is the replaceable parameter in the NVL function call it is causing a problem. Whether this worked before (ie in older versions) or not, I cannot say, but if you replace the parameter (ie :x) with a constant or a data column there is no problem. If you are certain that this should be working, then call IBM and open a support case (assuming you are on support). 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 Tue, Jun 14, 2011 at 4:12 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > Roger Vilca Wrote: > > > -------------------------------------------------------------------------------- > Hi, > I've recently migrated Informix Server from 7.31 to 11.50. > Now, a program is failing with error code -201 (syntax error). This program > runs with no error in 7.31. A short version of the program is here: > > main() > { > EXEC SQL BEGIN DECLARE SECTION; > int x; > int r; > EXEC SQL END DECLARE SECTION; > > EXEC SQL DATABASE "clientes@clientes"; > > x=2; > > r=0; > > EXEC SQL SELECT nvl(dummy + :x,0) INTO :r from rvi_dual; > > printf("return code: %d\\ ",sqlca.sqlcode); > > printf("r = %d\\ ",r); > > exit 0; > } > > The rvi_dual is: > > create table "desclie".rvi_dual > ( > > dummy integer > ); > > insert into rvi_dual values (1)> > Is this a bug ? > > Thanks in advance > > Roger > > > -------------------------------------------------------------------------------- > > Response: > I don't see anything specific with the short version of code you've posted. > However, I might suggest checking your program for Informix 11.5 Keywords. > We ran into a similar problem when upgrading from 9.3 to 9.4. In our case, > we > found an SQL statement using the keyword CYCLE as a table alias worked on > 9.3, > but returned a 201 error on 9.4. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071c696f9702f04a5b26ba6