different results using cursor vs. dbaccess

Hello, I've a query which I built w/ help from kind souls in this group that fetches the right rows and works fine via dbaccess, but when I write the query in an ESQL/C cursor, it fetches the wrong rows. What's wrong with my ESQL? Can I declare temporary tables like a, and b via ESQL cursors and expect them to work the same as in dbaccess? THANKS for any help!! In dbaccess, I do: SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR (REC_TYPE=LAST AND NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5 and b.f2=2 and rec_type=CURRENT))) ; The above works great in dbaccess. Now, in ESQL/C, I do: EXEC SQL BEGIN DECLARE SECTION int hv_old_val; int hv_f1; int hv_f2; int hv_f3; int hv_f4; int hv_f5; DBI_MYTAB hv_mytab; DBI_MYTAB a; DBI_MYTAB b; EXEC SQL END DECLARE SECTION; $ DECLARE MYTAB_CURSOR FOR SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3 =:b.f3 and a.f4=:b.f4 and a.f5 = :b.f5 andb.f2=:hv_f2 and rec_type=CURRENT))) ; $ OPEN MYTAB_CURSOR while (more) { FETCH MYTAB_CURSOR into :hv_mytab; // Here i print out the contents of each row fetched // and see different rows than what I got from dbaccess } Sent via Deja.com http://www.deja.com/ Before you buy.

lora@ragingbull.com wrote: > Hello, > I've a query which I built w/ help from kind souls in this group > that fetches the right rows and works fine via > dbaccess, but when I write the query in an ESQL/C cursor, it > fetches the wrong rows. What's wrong with my ESQL? > Can I declare temporary tables like a, and b via ESQL cursors > and expect them to work the same as in dbaccess? > THANKS for any help!! > > In dbaccess, I do: > > SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR (> REC_TYPE=LAST AND > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 > and a.f4=b.f4 and a.f5=b.f5 and > b.f2=2 and rec_type=CURRENT))) ; > Are CURRENT and LAST above strings? Particular values? CURRENT is a keyword for a CURRENT DATETIME value (ie the date and time now) which does not sound like what you want. > > The above works great in dbaccess. > > Now, in ESQL/C, I do: > > EXEC SQL BEGIN DECLARE SECTION > int hv_old_val; > int hv_f1; > int hv_f2; > int hv_f3; > int hv_f4; > int hv_f5; > DBI_MYTAB hv_mytab; > DBI_MYTAB a; > DBI_MYTAB b; > I assume the structure DBI_MYTAB is defined somewhere else? > EXEC SQL END DECLARE SECTION; > Personally I prefer the ANSI syntax: EXEC SQL DECLARE to the Informix specific syntax for portability sake, but that's just nit picking. > > $ DECLARE MYTAB_CURSOR FOR > SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND > (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3 =:b.f3 > and a.f4=:b.f4 and a.f5 = :b.f5 and> b.f2=:hv_f2 and rec_type=CURRENT))) ; > What are CURRENT and LAST here? Again CURRENT without quotes is a keyword that is replaced with current date/time and LAST is a keyword in a FETCH statement against a SCROLL cursor. Also the ':b.f4' & ':b.f5' are likely errors. A colon introduces a host variable, I believe what you want is simply "a.f4 = b.f4" and "a.f5 = b.f5" in the subquery. I'm surprised that this compiles and runs at all. Do you have any error handling after the DECLARE, OPEN or FETCH statements? If not how do you knwo you are not just printing randon garbage? > > $ OPEN MYTAB_CURSOR > while (more) > { > FETCH MYTAB_CURSOR into :hv_mytab; > // Here i print out the contents of each row fetched > // and see different rows than what I got from dbaccess > > } > Art S. Kagel

CURRENT and LAST are strings. I thought I needed the ":" before using the temp fields. Now can I do a.f1 = b.f1 ? I basically want to compare them. Do a and b need to be declared in the local SQL variable section? I had trouble compiling them w/o declaring them. After I put the : the compiler errors disappeared. In article <397DE089.F434113B@bloomberg.net>, kagel@bloomberg.net wrote: > lora@ragingbull.com wrote: > > > Hello, > > I've a query which I built w/ help from kind souls in this group > > that fetches the right rows and works fine via > > dbaccess, but when I write the query in an ESQL/C cursor, it > > fetches the wrong rows. What's wrong with my ESQL? > > Can I declare temporary tables like a, and b via ESQL cursors > > and expect them to work the same as in dbaccess? > > THANKS for any help!! > > > > In dbaccess, I do: > > > > SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR (> > REC_TYPE=LAST AND > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 > > and a.f4=b.f4 and a.f5=b.f5 and > > b.f2=2 and rec_type=CURRENT))) ; > > > > Are CURRENT and LAST above strings? Particular values? CURRENT is a > keyword for a CURRENT DATETIME value (ie the date and time now) which > does not sound like what you want. > > > > > The above works great in dbaccess. > > > > Now, in ESQL/C, I do: > > > > EXEC SQL BEGIN DECLARE SECTION > > int hv_old_val; > > int hv_f1; > > int hv_f2; > > int hv_f3; > > int hv_f4; > > int hv_f5; > > DBI_MYTAB hv_mytab; > > DBI_MYTAB a; > > DBI_MYTAB b; > > > > I assume the structure DBI_MYTAB is defined somewhere else? > > > EXEC SQL END DECLARE SECTION; > > > > Personally I prefer the ANSI syntax: EXEC SQL DECLARE to the Informix > specific syntax for portability sake, but that's just nit picking. > > > > > $ DECLARE MYTAB_CURSOR FOR > > SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND > > (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3=:b.f3 > > and a.f4=:b.f4 and a.f5 = :b.f5 and > > b.f2=:hv_f2 and rec_type=CURRENT))) ; > > > > What are CURRENT and LAST here? Again CURRENT without quotes is a > keyword that is replaced with current date/time and LAST is a keyword in a > FETCH statement against a SCROLL cursor. Also the ':b.f4' & ':b.f5' are > likely > errors. A colon introduces a host variable, I believe what you want is > simply > "a.f4 = b.f4" and "a.f5 = b.f5" in the subquery. I'm surprised that this > compiles and > runs at all. Do you have any error handling after the DECLARE, OPEN or > FETCH > statements? If not how do you knwo you are not just printing randon > garbage? > > > > > $ OPEN MYTAB_CURSOR > > while (more) > > { > > FETCH MYTAB_CURSOR into :hv_mytab; > > // Here i print out the contents of each row fetched > > // and see different rows than what I got from dbaccess > > > > } > > > > Art S. Kagel > > Sent via Deja.com http://www.deja.com/ Before you buy.

lora@ragingbull.com wrote: > CURRENT and LAST are strings. OK > > I thought I needed the ":" before > using the temp fields. Now can I No. > > do a.f1 = b.f1 ? I basically want to compare them. Yes. > > Do a and b need to be declared in the local SQL variable section? No, they are just aliases for the tables. Since the tablenames in the inner and outer query are the same you need to alias the tables in one or both so the engine does not get confused and you can corellate the sub-query. > I had trouble compiling them w/o declaring them. Don't know why. > > After I put the : the compiler errors disappeared. > Because by declaring a & b you created host variables with garbage for values and instead of comparing the inner query rows with the values from the outer query rows you were comparing them to the contents of the local vars a & b. This is why you are getting different results. OK Try this: $PREPARE astmt FROM "SELECT * \\ FROM mytab a \\ WHERE f1 = ? \\ AND f2=? \\ AND (REC_TYPE='CURRENT' \\ OR ( REC_TYPE='LAST' AND \\ NOT EXISTS ( \\ SELECT 0 \\ FROM mytab b \\ WHERE a.f2 = b.f2 \\ AND a.f3 = b.f3 \\ AND a.f4 = b.f4 \\ AND a.f5 = b.f5 \\ AND b.f2 = ? and rec_type=CURRENT))) "; /* Note that the last replaceable parameter (?) could have been a.f2 and it would work just as well. Then eliminate the second copy of hv_f2 in the OPEN below */ -- Error checking code.... $DECLARE mytab_cursor CURSOR FOR astmt; -- Error checking code.... $OPEN mytab_cursor USING :hv_old_val, :hv_f2, :hv_f2; -- More errror checking code.... while (sqlca.sqcode == 0) { $FETCH mytab_cursor INTO :hv_mytab; if (sqlca.sqlcode) break; } if (sqlca.sqlcode != SQLNOTFOUND) {\\ -- Error handling code. } else { -- End of Data processing, remember to handle the possibility of NO data. } > > In article <397DE089.F434113B@bloomberg.net>, > kagel@bloomberg.net wrote: > > lora@ragingbull.com wrote: > > > > > Hello, > > > I've a query which I built w/ help from kind souls in this group > > > that fetches the right rows and works fine via > > > dbaccess, but when I write the query in an ESQL/C cursor, it > > > fetches the wrong rows. What's wrong with my ESQL? > > > Can I declare temporary tables like a, and b via ESQL cursors > > > and expect them to work the same as in dbaccess? > > > THANKS for any help!! > > > > > > In dbaccess, I do: > > > > > > SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR > (> > > REC_TYPE=LAST AND > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 > > > and a.f4=b.f4 and a.f5=b.f5 and > > > b.f2=2 and rec_type=CURRENT))) ; > > > > > > > Are CURRENT and LAST above strings? Particular values? CURRENT is a > > keyword for a CURRENT DATETIME value (ie the date and time now) which > > does not sound like what you want. > > > > > > > > The above works great in dbaccess. > > > > > > Now, in ESQL/C, I do: > > > > > > EXEC SQL BEGIN DECLARE SECTION > > > int hv_old_val; > > > int hv_f1; > > > int hv_f2; > > > int hv_f3; > > > int hv_f4; > > > int hv_f5; > > > DBI_MYTAB hv_mytab; > > > DBI_MYTAB a; > > > DBI_MYTAB b; > > > > > > > I assume the structure DBI_MYTAB is defined somewhere else? > > > > > EXEC SQL END DECLARE SECTION; > > > > > > > Personally I prefer the ANSI syntax: EXEC SQL DECLARE to the Informix > > specific syntax for portability sake, but that's just nit picking. > > > > > > > > $ DECLARE MYTAB_CURSOR FOR > > > SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND > > > (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3> =:b.f3 > > > and a.f4=:b.f4 and a.f5 = :b.f5 and > > > b.f2=:hv_f2 and rec_type=CURRENT))) ; > > > > > > > What are CURRENT and LAST here? Again CURRENT without quotes is a > > keyword that is replaced with current date/time and LAST is a keyword > in a > > FETCH statement against a SCROLL cursor. Also the ':b.f4' & ':b.f5' > are > > likely > > errors. A colon introduces a host variable, I believe what you want > is > > simply > > "a.f4 = b.f4" and "a.f5 = b.f5" in the subquery. I'm surprised that > this > > compiles and > > runs at all. Do you have any error handling after the DECLARE, OPEN > or > > FETCH > > statements? If not how do you knwo you are not just printing randon > > garbage? > > > > > > > > $ OPEN MYTAB_CURSOR > > > while (more) > > > { > > > FETCH MYTAB_CURSOR into :hv_mytab; > > > // Here i print out the contents of each row fetched > > > // and see different rows than what I got from dbaccess > > > > > > } > > > > > > > Art S. Kagel > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

lora@ragingbull.com wrote: > CURRENT and LAST are strings. OK > > I thought I needed the ":" before > using the temp fields. Now can I No. > > do a.f1 = b.f1 ? I basically want to compare them. Yes. > > Do a and b need to be declared in the local SQL variable section? No, they are just aliases for the tables. Since the tablenames in the inner and outer query are the same you need to alias the tables in one or both so the engine does not get confused and you can corellate the sub-query. > I had trouble compiling them w/o declaring them. Don't know why. > > After I put the : the compiler errors disappeared. Because by declaring a & b you created host variables with garbage for values and instead of comparing the inner query rows with the values from the outer query rows you were comparing them to the contents of the local vars a & b. This is why you are getting different results. OK Try this: $PREPARE astmt FROM "SELECT * \\ FROM mytab a \\ WHERE f1 = ? \\ AND f2=? \\ AND (REC_TYPE='CURRENT' \\ OR ( REC_TYPE='LAST' AND \\ NOT EXISTS ( \\ SELECT 0 \\ FROM mytab b \\ WHERE a.f2 = b.f2 \\ AND a.f3 = b.f3 \\ AND a.f4 = b.f4 \\ AND a.f5 = b.f5 \\ AND b.f2 = ? and rec_type=CURRENT))) "; /* Note that the last replaceable parameter (?) could have been a.f2 and it would work just as well. Then eliminate the second copy of hv_f2 in the OPEN below */ -- Error checking code.... $DECLARE mytab_cursor CURSOR FOR astmt; -- Error checking code.... $OPEN mytab_cursor USING :hv_old_val, :hv_f2, :hv_f2; -- More errror checking code.... while (sqlca.sqcode == 0) { $FETCH mytab_cursor INTO :hv_mytab; if (sqlca.sqlcode) break; } if (sqlca.sqlcode != SQLNOTFOUND) {\\ -- Error handling code. } else { -- End of Data processing, remember to handle the possibility of NO data. } > > In article <397DE089.F434113B@bloomberg.net>, > kagel@bloomberg.net wrote: > > lora@ragingbull.com wrote: > > > > > Hello, > > > I've a query which I built w/ help from kind souls in this group > > > that fetches the right rows and works fine via > > > dbaccess, but when I write the query in an ESQL/C cursor, it > > > fetches the wrong rows. What's wrong with my ESQL? > > > Can I declare temporary tables like a, and b via ESQL cursors > > > and expect them to work the same as in dbaccess? > > > THANKS for any help!! > > > > > > In dbaccess, I do: > > > > > > SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR > (> > > REC_TYPE=LAST AND > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 > > > and a.f4=b.f4 and a.f5=b.f5 and > > > b.f2=2 and rec_type=CURRENT))) ; > > > > > > > Are CURRENT and LAST above strings? Particular values? CURRENT is a > > keyword for a CURRENT DATETIME value (ie the date and time now) which > > does not sound like what you want. > > > > > > > > The above works great in dbaccess. > > > > > > Now, in ESQL/C, I do: > > > > > > EXEC SQL BEGIN DECLARE SECTION > > > int hv_old_val; > > > int hv_f1; > > > int hv_f2; > > > int hv_f3; > > > int hv_f4; > > > int hv_f5; > > > DBI_MYTAB hv_mytab; > > > DBI_MYTAB a; > > > DBI_MYTAB b; > > > > > > > I assume the structure DBI_MYTAB is defined somewhere else? > > > > > EXEC SQL END DECLARE SECTION; > > > > > > > Personally I prefer the ANSI syntax: EXEC SQL DECLARE to the Informix > > specific syntax for portability sake, but that's just nit picking. > > > > > > > > $ DECLARE MYTAB_CURSOR FOR > > > SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND > > > (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3> =:b.f3 > > > and a.f4=:b.f4 and a.f5 = :b.f5 and > > > b.f2=:hv_f2 and rec_type=CURRENT))) ; > > > > > > > What are CURRENT and LAST here? Again CURRENT without quotes is a > > keyword that is replaced with current date/time and LAST is a keyword > in a > > FETCH statement against a SCROLL cursor. Also the ':b.f4' & ':b.f5' > are > > likely > > errors. A colon introduces a host variable, I believe what you want > is > > simply > > "a.f4 = b.f4" and "a.f5 = b.f5" in the subquery. I'm surprised that > this > > compiles and > > runs at all. Do you have any error handling after the DECLARE, OPEN > or > > FETCH > > statements? If not how do you knwo you are not just printing randon > > garbage? > > > > > > > > $ OPEN MYTAB_CURSOR > > > while (more) > > > { > > > FETCH MYTAB_CURSOR into :hv_mytab; > > > // Here i print out the contents of each row fetched > > > // and see different rows than what I got from dbaccess > > > > > > } > > > > > > > Art S. Kagel > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Actually, I tried again removing the ":" and removing the local SQL vars and this time the query works and it compiles fine. I am extremely grateful for your help. Thanks! In article <397E014A.8A590DB5@bloomberg.net>, kagel@bloomberg.net wrote: > lora@ragingbull.com wrote: > > > CURRENT and LAST are strings. > > OK > > > > > I thought I needed the ":" before > > using the temp fields. Now can I > > No. > > > > > do a.f1 = b.f1 ? I basically want to compare them. > > Yes. > > > > > Do a and b need to be declared in the local SQL variable section? > > No, they are just aliases for the tables. Since the tablenames in the > inner and outer > query are the same you need to alias the tables in one or both so the > engine does > not get confused and you can corellate the sub-query. > > > I had trouble compiling them w/o declaring them. > > Don't know why. > > > > > After I put the : the compiler errors disappeared. > > Because by declaring a & b you created host variables with garbage for > values and > instead of comparing the inner query rows with the values from the outer > query rows > you were comparing them to the contents of the local vars a & b. This is > why you > are getting different results. > > OK Try this: > > $PREPARE astmt FROM > "SELECT * \\ > FROM mytab a \\ > WHERE f1 = ? \\ > AND f2=? \\ > AND (REC_TYPE='CURRENT' \\ > OR ( REC_TYPE='LAST' AND \\ > NOT EXISTS ( \\ > SELECT 0 \\ > FROM mytab b \\ > WHERE a.f2 = b.f2 \\ > AND a.f3 = b.f3 \\ > AND a.f4 = b.f4 \\ > AND a.f5 = b.f5 \\ > AND b.f2 = ? and rec_type=CURRENT))) > "; > /* Note that the last replaceable parameter (?) could have been a.f2 and it > would > work just as well. Then eliminate the second copy of hv_f2 in the OPEN > below */ > -- Error checking code.... > $DECLARE mytab_cursor CURSOR FOR astmt; > -- Error checking code.... > $OPEN mytab_cursor USING :hv_old_val, :hv_f2, :hv_f2; > -- More errror checking code.... > while (sqlca.sqcode == 0) { > $FETCH mytab_cursor INTO :hv_mytab; > if (sqlca.sqlcode) > break; > } > if (sqlca.sqlcode != SQLNOTFOUND) {\\ > -- Error handling code. > } else { > -- End of Data processing, remember to handle the possibility of NO > data. > } > > > > > In article <397DE089.F434113B@bloomberg.net>, > > kagel@bloomberg.net wrote: > > > lora@ragingbull.com wrote: > > > > > > > Hello, > > > > I've a query which I built w/ help from kind souls in this group > > > > that fetches the right rows and works fine via > > > > dbaccess, but when I write the query in an ESQL/C cursor, it > > > > fetches the wrong rows. What's wrong with my ESQL? > > > > Can I declare temporary tables like a, and b via ESQL cursors > > > > and expect them to work the same as in dbaccess? > > > > THANKS for any help!! > > > > > > > > In dbaccess, I do: > > > > > > > > SELECT * FROM mytab a WHERE f1=1 AND f2=2 AND (REC_TYPE=CURRENT OR > > (> > > > REC_TYPE=LAST AND > > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=b.f2 and a.f3=b.f3 > > > > and a.f4=b.f4 and a.f5=b.f5 and > > > > b.f2=2 and rec_type=CURRENT))) ; > > > > > > > > > > Are CURRENT and LAST above strings? Particular values? CURRENT is a > > > keyword for a CURRENT DATETIME value (ie the date and time now) which > > > does not sound like what you want. > > > > > > > > > > > The above works great in dbaccess. > > > > > > > > Now, in ESQL/C, I do: > > > > > > > > EXEC SQL BEGIN DECLARE SECTION > > > > int hv_old_val; > > > > int hv_f1; > > > > int hv_f2; > > > > int hv_f3; > > > > int hv_f4; > > > > int hv_f5; > > > > DBI_MYTAB hv_mytab; > > > > DBI_MYTAB a; > > > > DBI_MYTAB b; > > > > > > > > > > I assume the structure DBI_MYTAB is defined somewhere else? > > > > > > > EXEC SQL END DECLARE SECTION; > > > > > > > > > > Personally I prefer the ANSI syntax: EXEC SQL DECLARE to the Informix > > > specific syntax for portability sake, but that's just nit picking. > > > > > > > > > > > $ DECLARE MYTAB_CURSOR FOR > > > > SELECT * FROM mytab a WHERE f1=:hv_old_val AND f2=:hv_f2 AND > > > > (REC_TYPE=CURRENT OR ( REC_TYPE=LAST AND > > > > NOT EXISTS ( SELECT 0 FROM mytab b WHERE a.f2=:b.f2 and a.f3> > =:b.f3 > > > > and a.f4=:b.f4 and a.f5 = :b.f5 and > > > > b.f2=:hv_f2 and rec_type=CURRENT))) ; > > > > > > > > > > What are CURRENT and LAST here? Again CURRENT without quotes is a > > > keyword that is replaced with current date/time and LAST is a keyword > > in a > > > FETCH statement against a SCROLL cursor. Also the ':b.f4' & ':b.f5' > > are > > > likely > > > errors. A colon introduces a host variable, I believe what you want > > is > > > simply > > > "a.f4 = b.f4" and "a.f5 = b.f5" in the subquery. I'm surprised that > > this > > > compiles and > > > runs at all. Do you have any error handling after the DECLARE, OPEN > > or > > > FETCH > > > statements? If not how do you knwo you are not just printing randon > > > garbage? > > > > > > > > > > > $ OPEN MYTAB_CURSOR > > > > while (more) > > > > { > > > > FETCH MYTAB_CURSOR into :hv_mytab; > > > > // Here i print out the contents of each row fetched > > > > // and see different rows than what I got from dbaccess > > > > > > > > } > > > > > > > > > > Art S. Kagel > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > Sent via Deja.com http://www.deja.com/ Before you buy.