Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

  • 1.  ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Fri December 13, 2024 12:04 PM
    Edited by Sebastien FLAESCH Sat December 14, 2024 04:37 AM

    Hello,

    Since ESQL/C sqlca.sqlerrd[] is now an int8 array, I expected to get the last generated bigserial in this register, after doing an INSERT.

    Seems it is not the case...

    I see a value zero in sqlca.sqlerrd[1] , is this normal?

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 2.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Fri December 13, 2024 12:11 PM

    Sebastien:

    Yes, that is expected. You still have to call dbinfo() to get the BIGSERIAL or (heaven forbid) SERIAL8 value assigned to a new row. 

    Perhaps a Feature Request to include BIGSERIAL and SERIAL8 in sqlca when appropriate should be created in the Ideas page?

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 3.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Sat December 14, 2024 04:37 AM

    Hello Art,

    I tried to add that idea here:

     https://communities.actian.com/s/actian-communities-ideas?category=OneDB%2F+HCL+Informix

    But it hangs...

    Maybe it's no longer possible with my current Actian/HCL account (we will renew the partnership)

    I wish I could spend more time on coding instead of struggling with all these procedures.

    Last day, I also wanted to report the bug we found in CONNECT TO.
    Not possible for now.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 4.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Sun December 15, 2024 08:03 AM

    Sebastien:

    RFE #  INFX-I-655 created:

    V15 sqlca structure to report BIGSERIAL & SERIAL8 value assigned by INSERT

    In versions of the engine prior to v15.0 & CSDK library versions prior to v15.0, the SQLCA structure fields were int4 and could not hold the values returned from inserting to a BIGSERIAL or SERIAL8 column requiring a separate call to the DBINFO() function to retrieve the last inserted value. However, in the v15.0 engine and libraries, the fields of SQLCA are int8 type so it is now possible for an INSERT to return even BIGSERIAL & SERIAL8 values.



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 5.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Sun December 15, 2024 12:26 PM

    Thank you Art for creating INFX-I-655!

    Obviously, saving another round-trip with the server to execute an additional SELECT DBINFO('bigserial') would be great!

    For those which do not use SERIAL/BIGSERIAL columns:
    Getting the new generated number after INSERT is a must for applications, as this column is usually the primary key.
    The portable SQL solution is to use SEQUENCEs, but that is more coding and not required if the only target SQL engine type is Informix.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 6.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Sun December 15, 2024 03:37 PM

    While certainly seeing the point, there's a little caveat:

    • a table can have simultaneously a serial and a bigserial or serial8 column - not sure how realistic or who'd do this, but it is possible, and therefor the two had to be returned separately, in two different sqlerrd fields
    • so, even if a table only has one serial type column, the application might not know, or had to be made to know, which one it is ... and had to check both sqlerrd fields
    • and there's at least the theoretical case where a serial has to change into bigserial on database side, but application couldn't change (and hopefully is flexible enough to also accept bigserial), so would want to expect the new serial value in sqlerrd[1] no matter what

    How would you/anyone like to see this solved?

    Cheers,
     Andreas



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 7.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Sun December 15, 2024 04:05 PM

    Andreas:

    That's interesting. Being able to have both a SERIAL and a BIGSERIAL or SERIAL8 brings up something my mother-in-law used to say: "Just because you can doesn't mean that you should!"

    It has nothing to do with the discussion you started here, but I struggle to think of a valid reason to have two different autoincrement type columns in a singlet table as a database designer.

    The obvious solution would be to take the opportunity of having to move to CSDK v15.0 to expand the sqlca.sqlerrd array to provide a second serial value field. But, you do make a good point certainly about having to modify code because you have altered a serial key to a bigserial key.

    How about this as a solution: Treat sqlca.sqlerrd[1] as the value of the first autoincrement column in a table's definition and say sqlca.sqlerrd[6] as the value of the second autoincrement column. Then existing code doesn't break as long as it is recompiled/linked with the new libraries, or the compatibility layer I was discussing becomes a possibility, while new code takes advantage of the new expanded structure to avoid the extra round trip of calling DBINFO() as Sebastien called it.

    Then it does not matter if the first column is serial or bigserial and the second is the other autoincrement type, and it doesn't matter if there is only a serial type that is later altered to bigserial.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 8.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Mon December 16, 2024 02:01 AM
    Edited by Sebastien FLAESCH Mon December 16, 2024 08:30 AM

    Hello Andreas, Art,

    To me, having 2 auto-incremented columns (SERIAL and BIGSERIAL) in the same table looks strange.

    Using more than 1 SERIAL or more that 1 BIGSERIAL results in error:
    -362: Can have only one column of serial/(serial8 or bigserial) type.

    So you can create this table:

    create table tab1 ( s1 serial, bs1 bigserial, name varchar(50) );

    But not this one:

    create table tab1 ( s1 serial, s2 serial, name varchar(50) );

    Why?

    The fact that a SERIAL + BIGSERIAL is allowed in the same table looks more like a design mistake.

    As you certainly know, each sqlca.sqlerrd[] element has a specific purpose:

    https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=structure-fields-sqlca

    and the size of the sqlca.sqlerrd[] array is 6 (from 0 to 5 in C, from 1 to 6 in 4GL)

    Art: If you suggest to have an 7th element at C index 6, I am not sure this is a good idea.

    For now, the most important is to get the last generated SERIAL or BIGSERIAL in the C-style sqlca.sqlerrd[1], to help 99% of cases where tables have one single auto-incremented column.

    For those rare cases where there is a SERIAL column AND a BIGSERIAL column in the same table, they can use DBINFO('sqlca.sqlerrd1') in addition to DBINFO('bigserial'):

    > create table tab1 ( s1 serial(100), bs1 bigserial(10000000000), name varchar(50) );

Table created.

> insert into tab1 (name) values ('aaa');

1 row(s) inserted.

> select dbinfo('sqlca.sqlerrd1'), dbinfo('bigserial');


(expression)         (expression) 

         100            10000000000

1 row(s) retrieved.

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 9.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Mon December 16, 2024 07:09 AM
    Edited by Vicente Salvador Cubedo Mon December 16, 2024 07:13 AM

    My two cents,

    Best Solution can be to do not change current sqlerrd specification

    sqlca.sqlerrd[1] stores last serial or 0

    and simply add one more component to the array, so:

    sqlca.sqlerrd[7] stores last bigserial or 0

    This column types are  not the same, so it makes sense to me to have diferent process logic and diferent positions in memory structure.


    ------------------------------
    Vicente Salvador Cubedo
    Software Architect
    DEISTER, S.A.
    Barcelona
    +34 932063298
    ------------------------------

    Original Message


  • 10.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Tue December 17, 2024 05:36 AM

    Vicente,

    I am not sure it's a good idea to extend the sqlca structure.

    AFAIK, sqlca structure should follow some standard definition and is used in different SQL client languages.
    But seems all DB vendors have implemented their own "standard".

    See the sqlca.h header file, there are dependencies to other clients:

    typedef struct sqlca_s
    {
    int4 sqlcode;
#ifdef DB2CLI
    char sqlerrm[600]; /* error message parameters */
#else /* DB2CLI */
    char sqlerrm[72]; /* error message parameters */
#endif /* DB2CLI */
 ...

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


  • 11.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Mon December 16, 2024 04:27 PM

    FYI we store the current serial, bigserial, and serial8 values for each fragment in different elements of an internal structure*. There's room for only one value per type, so although you can have columns of more than one type (serial and bigserial for example) you can't have more than one column of an individual type (e.g. serial and serial). 

    *They're stored in the partition structure in slot 1 on the partition page. These values can be displayed with oncheck -pt.



    ------------------------------
    John Lengyel
    ------------------------------

    Original Message


  • 12.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Wed December 18, 2024 01:28 PM

    I explored this a little more, wondering how much effort it might be and where ...

    Turns out both last serial8 and bigserial values (only one of them can be the right one) already are being sent to the client, since quite some time even, and only aren't made available through sqlca.sqlerrd[], for then obvious reasons.   How they'd be available to a program, without additional round trip, is through ifx_getserial8(ifx_int8_t *int8p) and ifx_getbigserial(bigint *bserial) functions.

    So all three serial types' last generated values are available to a client (esql/c, can't quite tell for others right now), only the onus is on the application to know which one to consult.
    Hint:  unused ones seem to be set zero.

    I wonder if it makes sense to change (new, v15) clients in a way that sqlerrd[1], if still 0 after an insert, would be populated with whatever non-zero value could be found in serial8 or bigserial, so an application could rely on one place only and hadn't to be data type aware?  The app, of course, still had to be ready for such potentially larger values.
    I guess that's what should be done...

     Andreas



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 13.  RE: ESQL/C 15 and sqlca.sqlerrd[1] for new BIGSERIAL

    Posted Thu December 19, 2024 02:16 AM
    Edited by Sebastien FLAESCH Thu December 19, 2024 03:41 AM

    Thanks for the investigation Andreas!

    This is very interesting and we could certainly implement an API in Genero BDL to interface with ifx_getbigserial() and ifx_getserial8()

    Still, I would expect that sqlca.sqlerrd[1] gets the last generated SERIAL8 or BIGSERIAL (if there is no additional SERIAL column).

    FYI:

    It appears that the ESQL/C documentation only mentions ifx_getserial8() and is missing ifx_getbigserial():

    https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=library-ifx-getserial8-function

    (BTW in such case you should always give a link to the doc)

    Maybe the missing ifx_getbigserial() doc topic is related to the conditional definition in sqlhdr.h ?

    #ifndef NOBIGINT
...
MI_EXT_DECL void ifx_getbigserial(bigint *bigintp);
#endif /* NOBIGINT */
MI_EXT_DECL void ifx_getserial8(ifx_int8_t *int8p);

    But I assume today with V15 ifx_getbigserial() and co are available on any supported platform?

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    Original Message


Related Content