Informix ESQL C Problems on AIX
Hi, I am new to Informix's ESQL C APIs and
have run into some problems on AIX. The biggest
problem is that I can't seem to perform a select,
iterate the results and then perform another
select based on some values from the first set of
results. I know a join could work but on the
particular database I am working on, a join is
just too slow it seems for some reason. Here is
some psuedocode for what I'm doing:
----------
EXEC SQL connect to '<database>';
[Note: ESQL didn't seem to like the "WITH
CONCURRENT TRANSACTIONS" option, and I'm not -
sure- what that does anyways.]
EXEC SQL declare testCursor cursor for <some sql>;
EXEC SQL open testCursor;
for (;;) {
EXEC SQL fetch testCursor;
[Using the results I pass some variables to
the function below, testFunc(...) for this
example.]
}
EXEC SQL close testCursor;
EXEC SQL free testCursor;
}
void testFunc(...)
{
EXEC SQL declare addressCursor cursor for <some
sql>;EXEC SQL open addressCursor;
for (;;) {
EXEC SQL fetch addressCursor;
[Obtain values to return back to calling
function.]
}
EXEC SQL close addressCursor;
EXEC SQL free addressCursor;
}
----------
Now everything seems to work fine the first
time around, but the original cursor (testCursor)
seems to get corrupted after the second cursor
(addressCurser) is used. It will show that no
more records are available in testCursor
(SQLSTATE 24000 I believe) when I try to continue
past 1 iteration. If I simply comment out the
testFunc(...) call, it will return multiple
records as desired.
Another possibly related issue is that when I
check for errors and try to return() the program
will core dump, but if I exit() everything seems
fine. I have checked all the online
documentation that I could find and I have yet to
find any examples of anything like this. I would
appreciate any ideas as to how to make this work.
Thanks in advance,
Paul Montgomery
Systems Architects, Inc.
montgomery@sysar.com
Sent via Deja.com http://www.deja.com/
Before you buy.
you'll need an exec sql begin declare here.
> EXEC SQL connect to '<database>';
> [Note: ESQL didn't seem to like the "WITH
> CONCURRENT TRANSACTIONS" option, and I'm not -
> sure- what that does anyways.]
It's used for multi-threading. Don't worry about it right now.
>
> EXEC SQL declare testCursor cursor for <some sql>;
Seems you left out the exec sql prepare testStatement from <some sql>;
> EXEC SQL open testCursor;
> for (;;) {
> EXEC SQL fetch testCursor;
exec sql fetch next testCursor into :some_struct_or_variable;
while(sqlca.sqlcode == 0) {
testFunc(some_struct_or_variable);
exec sql fetch testCursor into :some_struct_or_variable;
}
> [Using the results I pass some variables to
> the function below, testFunc(...) for this
> example.]
> }
> EXEC SQL close testCursor;
> EXEC SQL free testCursor;
> }
>
> void testFunc(...)
> {
> EXEC SQL declare addressCursor cursor for <some
> sql>;
exec sql prepare again.
> EXEC SQL open addressCursor;
> for (;;) {
> EXEC SQL fetch addressCursor;
See above.
> [Obtain values to return back to calling
> function.]
> }
> EXEC SQL close addressCursor;
> EXEC SQL free addressCursor;
> }
>
> ----------
>
> Now everything seems to work fine the first
> time around, but the original cursor (testCursor)
> seems to get corrupted after the second cursor
> (addressCurser) is used. It will show that no
> more records are available in testCursor
> (SQLSTATE 24000 I believe) when I try to continue
> past 1 iteration. If I simply comment out the
> testFunc(...) call, it will return multiple
> records as desired.
> Another possibly related issue is that when I
> check for errors and try to return() the program
> will core dump, but if I exit() everything seems
> fine. I have checked all the online
> documentation that I could find and I have yet to
> find any examples of anything like this. I would
> appreciate any ideas as to how to make this work.
I'm making the assumption that you don't already have that stuff there.
If you do, send a reply and we'll look again.
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
Ok, I made a few modifications at your suggestion, specifically
adding "while(sqlca.sqlcode == 0) {" in place of "for (;;) {" (I was
checking the error codes internally anyways).
As far as the "exec sql begin declare" stuff, I had one global one
at the start of the program which both functions used. I instead made
2 separate begin delcare sections (one for each function) just in case
there was some weird buffer overwrite problem.
Also the fetch into stuff was taken care of by my cursor
declaration. As I understand it something like:
"EXEC SQL declare addressCursor cursor for
SELECT dno_type, dno_city, dno_state, dno_line INTO
:dbType, :dbCity, :dbState, :dbLine FROM
informix.dnotes WHERE dno_loadno = :orderNum ORDER BY dno_line;"will take care of inserting the variables into the right places
automatically when a "EXEC SQL fetch addressCursor;" is called. So
essentially the "exec sql prepare testStatement from <some sql>;"
statement is unnecesary correct?
Well at any rate it still doesn't work correctly. The first
iteration brings back the correct information but on the second record
retrieval it returns a no more records error. There must be something
going on under the sheets in the APIs that I don't see. It must not
like performing a select inside a select on the same data connection to
the database?
Thanks for your assistance, any other ideas?
Paul Montgomery
Systems Architects, Inc.
montgomery@sysar.com
In article <867g04$b75$1@nnrp1.deja.com>,
mars1972@my-deja.com wrote:
>
>
> you'll need an exec sql begin declare here.
>
> > EXEC SQL connect to '<database>';
> > [Note: ESQL didn't seem to like the "WITH
> > CONCURRENT TRANSACTIONS" option, and I'm not -
> > sure- what that does anyways.]
>
> It's used for multi-threading. Don't worry about it right now.
>
> >
> > EXEC SQL declare testCursor cursor for <some sql>;
>
> Seems you left out the exec sql prepare testStatement from <some sql>;
>
> > EXEC SQL open testCursor;
> > for (;;) {
> > EXEC SQL fetch testCursor;
>
> exec sql fetch next testCursor into :some_struct_or_variable;
>
> while(sqlca.sqlcode == 0) {
> testFunc(some_struct_or_variable);
> exec sql fetch testCursor into :some_struct_or_variable;
> }
>
> > [Using the results I pass some variables to
> > the function below, testFunc(...) for this
> > example.]
> > }
> > EXEC SQL close testCursor;
> > EXEC SQL free testCursor;
> > }
> >
> > void testFunc(...)
> > {
> > EXEC SQL declare addressCursor cursor for <some
> > sql>;>
> exec sql prepare again.
>
> > EXEC SQL open addressCursor;
>
> > for (;;) {
> > EXEC SQL fetch addressCursor;
>
> See above.
>
> > [Obtain values to return back to calling
> > function.]
> > }
> > EXEC SQL close addressCursor;
> > EXEC SQL free addressCursor;
> > }
> >
> > ----------
> >
> > Now everything seems to work fine the first
> > time around, but the original cursor (testCursor)
> > seems to get corrupted after the second cursor
> > (addressCurser) is used. It will show that no
> > more records are available in testCursor
> > (SQLSTATE 24000 I believe) when I try to continue
> > past 1 iteration. If I simply comment out the
> > testFunc(...) call, it will return multiple
> > records as desired.
> > Another possibly related issue is that when I
> > check for errors and try to return() the program
> > will core dump, but if I exit() everything seems
> > fine. I have checked all the online
> > documentation that I could find and I have yet to
> > find any examples of anything like this. I would
> > appreciate any ideas as to how to make this work.
>
> I'm making the assumption that you don't already have that stuff
there.
> If you do, send a reply and we'll look again.
Sent via Deja.com http://www.deja.com/
Before you buy.
paul_montgomery@my-deja.com wrote:
>
> Hi, I am new to Informix's ESQL C APIs and
> have run into some problems on AIX. The biggest
> problem is that I can't seem to perform a select,
> iterate the results and then perform another
> select based on some values from the first set of
> results. I know a join could work but on the
> particular database I am working on, a join is
> just too slow it seems for some reason. Here is
> some psuedocode for what I'm doing:
>
> ----------
> EXEC SQL connect to '<database>';
> [Note: ESQL didn't seem to like the "WITH
> CONCURRENT TRANSACTIONS" option, and I'm not -
> sure- what that does anyways.]
You have to name the connection to use WITH CONCURRENT TRANSACTIONS. The
clause is ONLY useful if you are using multiple connections in a single
task (single or multi-threaded). Without that clause switching connections
with CONNECT TO connection_name will close all existing cursors on the
current connection. With the clause the transactions survive switching
connections and returning.
>
> EXEC SQL declare testCursor cursor for <some sql>;
> EXEC SQL open testCursor;
> for (;;) {
> EXEC SQL fetch testCursor;
I assume you have an INTO clause on either the OPEN or the FETCH so you can
send the returned data SOMEWHERE?
> [Using the results I pass some variables to
> the function below, testFunc(...) for this
> example.]
> }
> EXEC SQL close testCursor;
> EXEC SQL free testCursor;
> }
>
> void testFunc(...)
> {
> EXEC SQL declare addressCursor cursor for <some
> sql>;> EXEC SQL open addressCursor;
> for (;;) {
> EXEC SQL fetch addressCursor;
> [Obtain values to return back to calling
> function.]
> }
> EXEC SQL close addressCursor;
> EXEC SQL free addressCursor;
> }
>
> ----------
>
> Now everything seems to work fine the first
> time around, but the original cursor (testCursor)
> seems to get corrupted after the second cursor
> (addressCurser) is used. It will show that no
> more records are available in testCursor
> (SQLSTATE 24000 I believe) when I try to continue
> past 1 iteration. If I simply comment out the
> testFunc(...) call, it will return multiple
> records as desired.
Sounds like something is trashing the stack to me. Look for strings
allocated or declared to hold SQL statements or returned data that is too
small and may be overrunning it bounds thereby overwritting the stack.
Make sure you did not free something that was NOT malloced in the first
place or free some object twice.
> Another possibly related issue is that when I
> check for errors and try to return() the program
This is a VERY good indication that the call stack is trashed.
> will core dump, but if I exit() everything seems
> fine. I have checked all the online
> documentation that I could find and I have yet to
> find any examples of anything like this. I would
> appreciate any ideas as to how to make this work.
Art S. Kagel
In article <867m10$gfm$1@nnrp1.deja.com>,
montgomery@sysar.com wrote:
> Ok, I made a few modifications at your suggestion, specifically
> adding "while(sqlca.sqlcode == 0) {" in place of "for (;;) {" (I was
> checking the error codes internally anyways).
>
> As far as the "exec sql begin declare" stuff, I had one global one
> at the start of the program which both functions used. I instead made
> 2 separate begin delcare sections (one for each function) just in case
> there was some weird buffer overwrite problem.
>
> Also the fetch into stuff was taken care of by my cursor
> declaration. As I understand it something like:
> "EXEC SQL declare addressCursor cursor for
> SELECT dno_type, dno_city, dno_state, dno_line INTO
> :dbType, :dbCity, :dbState, :dbLine FROM
> informix.dnotes WHERE dno_loadno = :orderNum ORDER BY dno_line;"> will take care of inserting the variables into the right places
> automatically when a "EXEC SQL fetch addressCursor;" is called. So
> essentially the "exec sql prepare testStatement from <some sql>;"
> statement is unnecesary correct?
>
> Well at any rate it still doesn't work correctly. The first
> iteration brings back the correct information but on the second record
> retrieval it returns a no more records error. There must be something
> going on under the sheets in the APIs that I don't see. It must not
> like performing a select inside a select on the same data connection
to
> the database?
>
> Thanks for your assistance, any other ideas?
>
Hmmm.... I tried doing the exec sql declare for... statement and I
couldn't get it to compile. I've never seen anyone do that, but that
doesn't mean much. You may have a newer vesion of the compiler. All I
can tell you is how I'd do it.
Here's an example. I hope it's not more information than you're looking
for. You'll may some compile warnings because the size of some of the
variables is not know, but you can ignore those.
Func1(dno_loadno)
exec sql begin declare section;
string *dno_loadno; /* Declare passed value types. */
exec sql end declare section;
{
exec sql begin declare section;
string dno_type[20]; /* Define locals. */
string dno_city[35];
string dno_state[3];
string dno_line[20];
exec sql end declare section;
exec sql prepare testStatement from ' /* Prepare the statement*/
SELECT
dno_type,
dno_city,
dno_state,
dno_line
FROM
informix.dnotes
WHERE
dno_loadno = ? /* ? = put variable here */
ORDER BY /* when you call open using*/
dno_line';
if(sqlca.sqlcode != 0) {
Do some error stuff...
}
exec sql declare testCursor for testStatement;
if(sqlca.sqlcode != 0) {
Do some error stuff...
}
exec sql open testCursor using :dno_loadno;
/* Where dno_loadno is a variable you passed in */
/* dno_loadno will be put in where you had the ? in the */
/* prepare*/
if(sqlca.sqlcode != 0) {
Do some error stuff...
}
exec sql fetch next testCursor into /* Fetch record into vars*/
:dno_type,
:dno_city,
:dno_state,
:dno_line ;
while(sqlca.sqlcode == 0) {
Func2(dno_type, dno_city, dno_state, dno_line);
exec sql fetch next testCursor into /* Fetch next... */
:dno_type,
:dno_city,
:dno_state,
:dno_line ;
}
exec sql close testCursor;
exec sql free testCursor;
}
Func2(hv_type, hv_city, hv_state, hv_line)
exec sql begin declare section;
string *hv_type;
string *hv_city;
string *hv_state;
string *hv_line;
exec sql end declare section;
{
Do the same type of stuff as in Func1()...
}
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
I agree with Art that something is probably getting overwritten. Another
thing to check is if your second function is performing a commit. This will
shut down the outer cursor unless you did a "declare ... cursor with
hold for select ..."
Jay Buckler
Art S. Kagel <kagel@bloomberg.net> wrote in message
news:38876736.A904A886@bloomberg.net...
> paul_montgomery@my-deja.com wrote:
> >
> > Hi, I am new to Informix's ESQL C APIs and
> > have run into some problems on AIX. The biggest
> > problem is that I can't seem to perform a select,
> > iterate the results and then perform another
> > select based on some values from the first set of
> > results. I know a join could work but on the
> > particular database I am working on, a join is
> > just too slow it seems for some reason. Here is
> > some psuedocode for what I'm doing:
> >
> > ----------
> > EXEC SQL connect to '<database>';
> > [Note: ESQL didn't seem to like the "WITH
> > CONCURRENT TRANSACTIONS" option, and I'm not -
> > sure- what that does anyways.]
>
> You have to name the connection to use WITH CONCURRENT TRANSACTIONS. The
> clause is ONLY useful if you are using multiple connections in a single
> task (single or multi-threaded). Without that clause switching
connections
> with CONNECT TO connection_name will close all existing cursors on the
> current connection. With the clause the transactions survive switching
> connections and returning.
>
> >
> > EXEC SQL declare testCursor cursor for <some sql>;
> > EXEC SQL open testCursor;
> > for (;;) {
> > EXEC SQL fetch testCursor;
>
> I assume you have an INTO clause on either the OPEN or the FETCH so you
can
> send the returned data SOMEWHERE?
>
> > [Using the results I pass some variables to
> > the function below, testFunc(...) for this
> > example.]
> > }
> > EXEC SQL close testCursor;
> > EXEC SQL free testCursor;
> > }
> >
> > void testFunc(...)
> > {
> > EXEC SQL declare addressCursor cursor for <some
> > sql>;> > EXEC SQL open addressCursor;
> > for (;;) {
> > EXEC SQL fetch addressCursor;
> > [Obtain values to return back to calling
> > function.]
> > }
> > EXEC SQL close addressCursor;
> > EXEC SQL free addressCursor;
> > }
> >
> > ----------
> >
> > Now everything seems to work fine the first
> > time around, but the original cursor (testCursor)
> > seems to get corrupted after the second cursor
> > (addressCurser) is used. It will show that no
> > more records are available in testCursor
> > (SQLSTATE 24000 I believe) when I try to continue
> > past 1 iteration. If I simply comment out the
> > testFunc(...) call, it will return multiple
> > records as desired.
>
> Sounds like something is trashing the stack to me. Look for strings
> allocated or declared to hold SQL statements or returned data that is too
> small and may be overrunning it bounds thereby overwritting the stack.
> Make sure you did not free something that was NOT malloced in the first
> place or free some object twice.
>
> > Another possibly related issue is that when I
> > check for errors and try to return() the program
>
> This is a VERY good indication that the call stack is trashed.
>
> > will core dump, but if I exit() everything seems
> > fine. I have checked all the online
> > documentation that I could find and I have yet to
> > find any examples of anything like this. I would
> > appreciate any ideas as to how to make this work.
>
> Art S. Kagel