dbinfo in a concurrent environment.

Hi, we are considering moving from ESQL to a plain C-Driver (Informix CLI). In ESQL we've used sqlca.sqlerrd structures to fetch the inserted serial. Now I am wondering if: ~~~~~ insert into test_if (name, num) values ("asd", 0);select dbinfo('sqlca.sqlerrd1') from systables where tabname='systables'; ~~~~~ is the equivalent and works all the time (ie independent of transaction level and how many insert from other processes occur in between those two statements). From my understanding dbinfo is related to the user session, so I should be fine, but I couldn't find anything definite in the docs. Thanks, Florian

Florian: Yes, the dbinfo('sqlca.sqlerrd1') is the correct way to get the serial number just applied. I'm a big fan if ESQL/C so I'm curious as to why your organization is switching to CLI/ODBC. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Feb 9, 2016 at 3:33 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: > Hi, > > we are considering moving from ESQL to a plain C-Driver (Informix CLI). In > ESQL we've used sqlca.sqlerrd structures to fetch the inserted serial. > > Now I am wondering if: > ~~~~~ > insert into test_if (name, num) values ("asd", 0);> select dbinfo('sqlca.sqlerrd1') from systables where tabname='systables'; > ~~~~~ > is the equivalent and works all the time (ie independent of transaction > level > and how many insert from other processes occur in between those two > statements). > > >From my understanding dbinfo is related to the user session, so I should > be > fine, but I couldn't find anything definite in the docs. > > Thanks, > Florian > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113eb324a04ade052b54e727

Hi Art, thanks that is good to know. The reason for the switch is mainly abstraction. For one we do not want to maintain our own database wrapper for multiple databases (after all ESQL is not as portable as one would hope) and interestingly enough the CLI variants seem to be faster by a factor of two or so (in relatively simple tests), though that might be just because they fetch everything immediately over the net (ie we might be able to tune our ESQL stuff)? Additionally, removing the preprocessor step from our toolchain should make compilation smoother. But if you have a good idea on how to support oracle, informix and postgres with ESQL, without writing loads of wrapper code, I am all ears. Cheers, Florian

Florian: Thanks for the rundown. You are correct. I originally standardized on ESQL/C because of its promise of being portable while CLI/ODBC was originally the private interface for Sybase and later, by inheritance, MS SQL Server before it was opened and became the ODBC standard. However, as you have noted, ESQL/C has not been adopted well by the other SQL databases. Oracle's *SQL is brain dead with far less capability than Informix's implementation and MS SQL Server no longer supports it at all. I don't know myself how complete ECPG is for PostgreSQL. Alternatives? Only one that I know of would be to use Querix's ESQL/C compiler and libraries rather than native ones. These are database agnostic and there are even library functions to massage SQL at runtime into a form that works better for the target database so you can write SQL to a single standard and use it across multiple database types. While it's not free like IBM's Informix SDK it is inexpensive. Worth exploring. If you want I can put you in touch with the right people at Querix who can tell you what is and isn't doable this way. Beyond that, I understand your decision. Also, yes, you can tune your ESQL/C. For example, if you are using ODBC bulk fetch capabilities, yes that will run about 2X faster than using a simple cursor in ESQL/C, but Informix's ESQL/C also supports Array Fetch which is faster still and is what the ODBC library is using under the hood to implement the bulk fetch. Take a look at the array fetch code in my dbcopy.ec utility as an example (there is also a simple array fetch sample program in the ESQL/C demo subdirectory). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Feb 9, 2016 at 7:07 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: > Hi Art, > > thanks that is good to know. The reason for the switch is mainly > abstraction. > For one we do not want to maintain our own database wrapper for multiple > databases (after all ESQL is not as portable as one would hope) and > interestingly enough the CLI variants seem to be faster by a factor of two > or > so (in relatively simple tests), though that might be just because they > fetch > everything immediately over the net (ie we might be able to tune our ESQL > stuff)? Additionally, removing the preprocessor step from our toolchain > should > make compilation smoother. > > But if you have a good idea on how to support oracle, informix and postgres > with ESQL, without writing loads of wrapper code, I am all ears. > > Cheers, > Florian > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1135e456a3d3b7052b56036b

Hi Art, On 09.02.2016 14:08, Art Kagel wrote: > Oracle's *SQL is brain dead with > far less capability than Informix's implementation and MS SQL Server no > longer supports it at all. I don't know myself how complete ECPG is for > PostgreSQL. PostgresSQL at least has an Informix compatibility mode for ESQL, which also mirrors the sqlca struct features from Informix partially, but I thin in the end their focus is more on libpq. > If you want I can put you in touch with the right people at Querix who can > tell you what is and isn't doable this way. Thank you for the offer, as it currently stands I do not think we will go down that road, but I'll most certainly get back to you if we do! Cheers, Florian