SQLSTATE after a cursor fetch
Guys, I have two instances running - IDS/UDO 9.14 uc4 with client SDK 2.10 - IDS2000 with client SDK 2.30 I am trying to execute the following ESQL/C code void SelectFromOneTable() { EXEC SQL begin declare section; int i, v1, v2, v3, v4, v5; EXEC SQL end declare section; EXEC SQL prepare selectStatement from 'select * from all;'; EXEC SQL declare selectCursor cursor for selectStatement; EXEC SQL open selectCursor; while (SQLCODE == 0) { EXEC SQL fetch selectCursor into :i, :v1, :v2, :v3, :v4, :v5; printf("INFO: sqlstate %s \\n", SQLSTATE); } printf("INFO: key %d, values %d, %d, %d, %d, %d \\n", i, v1, v2, v3, v4, v5); EXEC SQL close selectCursor; EXEC SQL free selectStatement; EXEC SQL free selectCursor; } I am expecting a non zero SQLCODE to terminate the fetches - possibly a code of 200, This code loops indefinately on both setups - the SQLSTATE is always "00000" - I only have 2 rows in table 'all'. Any ideas where I what is happening? Mary thanks Mark Jenkins
Umm, pardon the silly question, but where are you setting SQLCODE? If you don't have that defined to sqlca.sqlcode, this will never return. When there are no more rows to fetch, then sqlca.sqlcode will be set to SQLNOTFOUND (100). You need to: $include sqlca; as well... "Mark Jenkins" <mjenkins@adc.metrica.co.uk> wrote in message news:820r9t$5tc1@www.informix.com... > Guys, > I have two instances running > - IDS/UDO 9.14 uc4 with client SDK 2.10 > - IDS2000 with client SDK 2.30 > I am trying to execute the following ESQL/C code > > void SelectFromOneTable() > { > EXEC SQL begin declare section; > int i, v1, v2, v3, v4, v5; > EXEC SQL end declare section; > > EXEC SQL prepare selectStatement from 'select * from all;'; > EXEC SQL declare selectCursor cursor for selectStatement; > EXEC SQL open selectCursor; > > while (SQLCODE == 0) > { > EXEC SQL fetch selectCursor into :i, :v1, :v2, :v3, :v4, :v5; > printf("INFO: sqlstate %s \\n", SQLSTATE); > } > > printf("INFO: key %d, values %d, %d, %d, %d, %d \\n", > i, v1, v2, v3, v4, v5); > EXEC SQL close selectCursor; > EXEC SQL free selectStatement; > EXEC SQL free selectCursor; > } > > I am expecting a non zero SQLCODE to terminate the fetches - possibly a code > of 200, > > This code loops indefinately on both setups - the SQLSTATE is always > "00000" - I only have 2 rows in table 'all'. > > Any ideas where I what is happening? > > Mary thanks > Mark Jenkins > > >
Mark Jenkins wrote: > Guys, > I have two instances running > - IDS/UDO 9.14 uc4 with client SDK 2.10 > - IDS2000 with client SDK 2.30 > I am trying to execute the following ESQL/C code > > void SelectFromOneTable() > { > EXEC SQL begin declare section; > int i, v1, v2, v3, v4, v5; > EXEC SQL end declare section; > -- Add EXEC SQL WHENEVER ERROR STOP; -- Rerun test. -- If you get an error message, you'll know what the trouble is. -- If you don't, then neither you nor I know what's up. -- There's nothing immediately obviously wrong with your code except that you don't -- error check every statement religiously, which is the only way to be sure that nothing -- goes wrong. -- Also see below... > > EXEC SQL prepare selectStatement from 'select * from all;'; > EXEC SQL declare selectCursor cursor for selectStatement; > EXEC SQL open selectCursor; > > while (SQLCODE == 0) > { > EXEC SQL fetch selectCursor into :i, :v1, :v2, :v3, :v4, :v5; > printf("INFO: sqlstate %s \\n", SQLSTATE); > } > > printf("INFO: key %d, values %d, %d, %d, %d, %d \\n", > i, v1, v2, v3, v4, v5); > EXEC SQL close selectCursor; > EXEC SQL free selectStatement; > EXEC SQL free selectCursor; > } > > I am expecting a non zero SQLCODE to terminate the fetches - possibly a code > of 200, SQLCODE is a synonym for sqlca.sqlcode, at least in theory. If your code doesn't produce any error, try changing your while loop to test sqlca.sqlcode. If it then stops (with code 100, SQLNOTFOUND), then you've got a bug in the way SQLCODE is handled. > > This code loops indefinately on both setups - the SQLSTATE is always > "00000" - I only have 2 rows in table 'all'. > > Any ideas where I what is happening? > > Mary thanks > Mark Jenkins -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Guys, I've found the solution to my problem and thought I should let people know I was building the esql code below using a makefile. The libraries I was using were SYSLIBS = -lifsql -lifcli Depending on their order you either get an infinite loop or the correct result. Is this a case of RTFM and don't use makefiles, or an undocumented feature? Cheers Mark Mark Jenkins wrote in message <820r9t$5tc1@www.informix.com>... >Guys, >I have two instances running >- IDS/UDO 9.14 uc4 with client SDK 2.10 >- IDS2000 with client SDK 2.30 >I am trying to execute the following ESQL/C code > >void SelectFromOneTable() >{ > EXEC SQL begin declare section; > int i, v1, v2, v3, v4, v5; > EXEC SQL end declare section; > > EXEC SQL prepare selectStatement from 'select * from all;'; > EXEC SQL declare selectCursor cursor for selectStatement; > EXEC SQL open selectCursor; > > while (SQLCODE == 0) > { > EXEC SQL fetch selectCursor into :i, :v1, :v2, :v3, :v4, :v5; > printf("INFO: sqlstate %s \\n", SQLSTATE); > } > > printf("INFO: key %d, values %d, %d, %d, %d, %d \\n", > i, v1, v2, v3, v4, v5); > EXEC SQL close selectCursor; > EXEC SQL free selectStatement; > EXEC SQL free selectCursor; >} > >I am expecting a non zero SQLCODE to terminate the fetches - possibly a code >of 200, > >This code loops indefinately on both setups - the SQLSTATE is always >"00000" - I only have 2 rows in table 'all'. > >Any ideas where I what is happening? > >Mary thanks >Mark Jenkins > > >
Hmmm. So you're trying to use both ESQL/C and CLI (ODBC) in a single program? I'm not sure whether that is supported. I know I had problems with CLI and ESQL/C when I was trying to get NewEra to work; I would hope that they'd fixed such interworking problems by now, but I wouldn't want to guarantee that. If you're building ESQL/C code into an executable, you should be linking with the esql script. Period. I still haven't had a convincing scenario where this was not possible. Mark Jenkins wrote: > Guys, > I've found the solution to my problem and thought I should let people know > > I was building the esql code below using a makefile. The libraries I was > using were > > SYSLIBS = -lifsql -lifcli > > Depending on their order you either get an infinite loop or the correct > result. > Is this a case of RTFM and don't use makefiles, or an undocumented feature? > Cheers > Mark > > Mark Jenkins wrote in message <820r9t$5tc1@www.informix.com>... > >Guys, > >I have two instances running > >- IDS/UDO 9.14 uc4 with client SDK 2.10 > >- IDS2000 with client SDK 2.30 > >I am trying to execute the following ESQL/C code > > > >void SelectFromOneTable() > >{ > > EXEC SQL begin declare section; > > int i, v1, v2, v3, v4, v5; > > EXEC SQL end declare section; > > > > EXEC SQL prepare selectStatement from 'select * from all;'; > > EXEC SQL declare selectCursor cursor for selectStatement; > > EXEC SQL open selectCursor; > > > > while (SQLCODE == 0) > > { > > EXEC SQL fetch selectCursor into :i, :v1, :v2, :v3, :v4, :v5; > > printf("INFO: sqlstate %s \\n", SQLSTATE); > > } > > > > printf("INFO: key %d, values %d, %d, %d, %d, %d \\n", > > i, v1, v2, v3, v4, v5); > > EXEC SQL close selectCursor; > > EXEC SQL free selectStatement; > > EXEC SQL free selectCursor; > >} > > > >I am expecting a non zero SQLCODE to terminate the fetches - possibly a > code > >of 200, > > > >This code loops indefinately on both setups - the SQLSTATE is always > >"00000" - I only have 2 rows in table 'all'. > > > >Any ideas where I what is happening? > > > >Mary thanks > >Mark Jenkins > > > > > > -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>