Re: sqlca.sqlcode after select statement
> If your database is normal, you will get sqlca.sqlcode == 0. If it is the > notorious > MODE ANSI database, then you will get the ANSI-mandated SQLNOTFOUND. Hi Jonathan, I just tested the following proggy using an ANSI database (by the way: I don't know why, but I think ANSI is the most used mode running a database in good old Germany) ...declaration stuff... $long my_long, my_sum; $int my_long_ind, my_sum_ind; ... $select sum(my_column), count(*) into $my_sum:my_sum_ind, $my_long:my_long_ind from my_table where 1=2; ... Stdout said: sqlcode = 0, my_sum IS NULL, my_sum_ind = -1 my_long = 0 and my_long_ind = 0 There seems to be no difference in handling the sqlcode in ANSI- and NO_ANSI-mode databases. Just a hint, Chris
Two questions arising from my answer -- this is the hard one, not least because I don't have OnLine running on my Win95 box where I'm typing this. Christian Brauer wrote: > > If your database is normal, you will get sqlca.sqlcode == 0. If it is the > > notorious > > MODE ANSI database, then you will get the ANSI-mandated SQLNOTFOUND. > > Hi Jonathan, > > I just tested the following proggy using an ANSI database > (by the way: I don't know why, but I think ANSI is the most used > mode running a database in good old Germany) > > ...declaration stuff... > $long my_long, my_sum; > $int my_long_ind, my_sum_ind; > ... > $select sum(my_column), count(*) > into $my_sum:my_sum_ind, $my_long:my_long_ind > from my_table where 1=2; > ... > > Stdout said: sqlcode = 0, my_sum IS NULL, my_sum_ind = -1 > my_long = 0 and my_long_ind = 0 > > There seems to be no difference in handling the sqlcode in > ANSI- and NO_ANSI-mode databases. Hmmm...I could be misremembering, or it could be that the aggregates alter the behaviour. The COUNT(*) has a perfectly good value, 0. C J Date would argue that the SUM should also be zero; I sympathize with this view. However, the sum of an empty set in ANSI SQL is a NULL. Now, if you were to select plain values instead of aggregates, you'd get the NOTFOUND (100) sqlca.sqlcode. Similarly for UPDATE or DELETE. So, I'll stand corrected... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Christian Brauer <Christian.Brauer@Dresdner-Bank.com> wrote... > > If your database is normal, you will get sqlca.sqlcode == 0. If it is the > > notorious > > MODE ANSI database, then you will get the ANSI-mandated SQLNOTFOUND. > > Hi Jonathan, > > I just tested the following proggy using an ANSI database > (by the way: I don't know why, but I think ANSI is the most used > mode running a database in good old Germany) > > ...declaration stuff... > $long my_long, my_sum; > $int my_long_ind, my_sum_ind; > ... > $select sum(my_column), count(*) > into $my_sum:my_sum_ind, $my_long:my_long_ind > from my_table where 1=2; > ... > > Stdout said: sqlcode = 0, my_sum IS NULL, my_sum_ind = -1 > my_long = 0 and my_long_ind = 0 > > > There seems to be no difference in handling the sqlcode in > ANSI- and NO_ANSI-mode databases. > > Just a hint, > > Chris Hi Chris, you got a value in my_long. What is the value of sqlca.sqlcode if you select (sum(my_column) only? I cannot test because I have no ANSI database. BTW: Your answer includes a valuable hint: The index variable can be asked simply to determine that there are no rows. Regards, Reinhard
> Hmmm...I could be misremembering, or it could be that the aggregates alter the > behaviour. > Now, if you were to select plain values instead of aggregates, you'd > get the > NOTFOUND (100) sqlca.sqlcode. Similarly for UPDATE or DELETE. So, I'll stand > corrected... > Yeah, that's it ! Selecting plain values WILL throw NOTFOUND (100) as it does using UPDATE or DELETE. I think I had problems with that some time ago... Greetings, Chris
> you got a value in my_long. What is the value of sqlca.sqlcode if > you select (sum(my_column) only? Same result: selecting sum(my_column) only say: sqlcode = 0, sum [value,index] = -2147483648:-1 Hth, Chris