ODBC and SERIALs

Hi all, as possibly many other programmers before I have a problem with SERIALs: I need to know the value of a SERIAL column of a previously inserted row. And furthermore I need to know this information via ODBC (for an old 7.23 installation). As far as I know there is a way to get this kind of information via ESQL but I have not found a solution for ODBC yet. My Google search ended unsuccessfully. The only way I can think of now is to lock the appropriate table, inserting a new row and issuing a select with a MAX(column). But I think that would have great performance impact in a multi user environment. Any help would be greatly appreciated (even if somebody can proof that it is not directly possible with ODBC). Andreas

Hi Andreas, you can try: select dbinfo('sqlca.sqlerrd1') serial_value from systables where tabid = 1 Jean-Guy "Andreas Wacknitz" <a.wacknitz@francotyp.com> a 'crit dans le message news: 95dv6h$gt1js$1@ID-51953.news.dfncis.de... > Hi all, > > as possibly many other programmers before I have a problem with SERIALs: > I need to know the value of a SERIAL column of a previously inserted row. > And furthermore I need to know this information via ODBC (for an old 7.23 > installation). > As far as I know there is a way to get this kind of information via ESQL but > I have not found a solution for ODBC yet. My Google search ended > unsuccessfully. > The only way I can think of now is to lock the appropriate table, inserting > a new > row and issuing a select with a MAX(column). But I think that would have > great > performance impact in a multi user environment. > Any help would be greatly appreciated (even if somebody can proof that it is > not directly possible with ODBC). > > Andreas > >

Hi Jean-Guy, thank you for your answer. I works in such a way that it returns the required value. I wonder if it is secure in a multi user environment. Is it guaranteed that the sequence insert into table (field1, field2) values (val1, val2); select dbinfo('sqlca.sqlerrd1') serial_value from systables where tabid = 1; returns always the correct SERIAL value (even if another insert into table interferes)? Best regards, Andreas "Jean-Guy Charron" <jeanguy.charron@systhemes.ca> schrieb im Newsbeitrag news:kBye6.5033$H_5.41268@weber.videotron.net... > Hi Andreas, > > you can try: select dbinfo('sqlca.sqlerrd1') serial_value > from systables > where tabid = 1 > > Jean-Guy > > "Andreas Wacknitz" <a.wacknitz@francotyp.com> a 'crit dans le message news: > 95dv6h$gt1js$1@ID-51953.news.dfncis.de... > > Hi all, > > > > as possibly many other programmers before I have a problem with SERIALs: > > I need to know the value of a SERIAL column of a previously inserted row. > > And furthermore I need to know this information via ODBC (for an old 7.23 > > installation). > > As far as I know there is a way to get this kind of information via ESQL > but > > I have not found a solution for ODBC yet. My Google search ended > > unsuccessfully. > > The only way I can think of now is to lock the appropriate table, > inserting > > a new > > row and issuing a select with a MAX(column). But I think that would have > > great > > performance impact in a multi user environment. > > Any help would be greatly appreciated (even if somebody can proof that it > is > > not directly possible with ODBC). > > > > Andreas > > > > > >

Andreas Wacknitz wrote: > thank you for your answer. I works in such a way that it returns the > required value. I wonder if it is secure in a multi user environment. Yes, it is secure in a multi-user environment -- the value is tied to your session. > Is it guaranteed that the sequence > insert into table (field1, field2) values (val1, val2);> select dbinfo('sqlca.sqlerrd1') serial_value from systables where tabid = > 1; > > returns always the correct SERIAL value (even if another insert into table > interferes)? If the other insert is from your session, then obviously that messes it up. Nobody else's insert (not even an insert from your program on another connection) will mess it up. I have also seen a report that you can use "EXECUTE FUNCTION dbinfo('sqlca.sqlerrd1')" for this; simple experimentation with IDS 9.21shows that this is false (SQL -674: Routine (dbinfo) can not be resolved). > "Jean-Guy Charron" <jeanguy.charron@systhemes.ca> schrieb: > > you can try: select dbinfo('sqlca.sqlerrd1') serial_value > > from systables > > where tabid = 1 > > > > Jean-Guy > > > > "Andreas Wacknitz" <a.wacknitz@francotyp.com> a écrit: > > > as possibly many other programmers before I have a problem with SERIALs: > > > I need to know the value of a SERIAL column of a previously inserted row. > > > And furthermore I need to know this information via ODBC (for an old 7.23 > > > installation). > > > As far as I know there is a way to get this kind of information via ESQL but > > > I have not found a solution for ODBC yet. My Google search ended > > > unsuccessfully. > > > The only way I can think of now is to lock the appropriate table, inserting > > > a new row and issuing a select with a MAX(column). But I think that would > > > have great performance impact in a multi user environment. > > > Any help would be greatly appreciated (even if somebody can proof that it > > > is not directly possible with ODBC). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"