Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

  • 1.  Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Tue June 02, 2020 11:27 AM
    Hello,

    CLOB/BLOB columns can be NULL, if created without NOT NULL constraint... (tested with dbaccess/SQL)

    But in ESQL/C, how do you specify/check that an ifx_lo_t is NULL?

    I mean something equivalent to ifx_loc_t member loc_indicator set to -1 (null) or 0 (not null)

    Is this just something that ifx_lo_t implementors have overlooked?

    I assume we should be able to use some APIs like:

    int ifx_lo_setnull( ifx_lo_t * lo, 1/0);
    int ifx_lo_isnull( ifx_lo_t * lo, int &isnull);

    or did I miss something?

    Thanks
    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------


  • 2.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Tue June 02, 2020 11:54 AM
    Sebastian:

    I have not tried this specifically with a BLOB or CLOB, but you should be able to use an indicator variable in the FETCH statement in ESQL/C:

    $define smallint clob_ind;
    ...
    $FETCH cursor_name INTO ..., :clob_loc INDICATOR :clob_ind, ..
    ...
    if (clob_ind == -1) {
       
    }

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 3.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Tue June 02, 2020 12:25 PM
    We have generic code (it's a DB driver like ODBC) using struct sqlda / ifx_sqlvar_t allocated dynamically, and this is where we would like to set or get NULL indicators...

    How to use the INDICATOR clause if you don't know the number of result columns or SQL input parameters at compile time?

    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 4.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Tue June 02, 2020 01:11 PM
    Sebastian:

    Then you can use the 'I' fields in the sqlda structure:

    From sqlda.h:

       int2 sqlitype;       
       int2 sqlilen;         
       char *sqlidata;       

    So, once the DESCRIBE completes you populate the indicator fields:

    $define smallint clob_ind;

    my_sqlda->sqlvar[3].sqlitype = CSHORTYPE;
    my_sqlda->sqlvar[3].sqlilen   = 2;
    my_sqlda->sqlvar[3].sqlidata = clob_ind;

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 5.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Wed June 03, 2020 02:39 AM
    Great! Thanks a lot Art, I will try that.
    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 6.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Wed June 03, 2020 08:09 AM
    You're welcome! Oh, oops, there was an error in my post, the third assignment should be:

    my_sqlda->sqlvar[3].sqlidata = (char *)&clob_ind;

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 7.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Wed June 03, 2020 02:46 AM
    Edited by SEBASTIEN SF FLAESCH Wed June 03, 2020 04:03 AM
    ... or maybe just sqlind member!!!

    typedef struct sqlvar_struct
    {
    int2 sqltype; /* variable type */
    int4 sqllen; /* length in bytes */
    char *sqldata; /* pointer to data */
    int2 *sqlind; /* pointer to indicator */             <---

    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.esqlc.doc/ids_esqlc_0583.htm

    I wonder how I could miss that...

    Anyway, thanks for directing me on this.

    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 8.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Wed June 03, 2020 08:15 AM
    Yes, the sqlind member is equivalent to setting the sqlitype, sqlilen, and sqlidata to a short. Either way works.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 9.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Thu June 04, 2020 04:36 AM
    Edited by SEBASTIEN SF FLAESCH Thu June 04, 2020 08:15 AM
    Hello!

    Some optimization question regarding sqlind/NULL:

    To insert smart lobs data with ESQL/C, I have to create an ifx_lo_t object with ifx_lo_create(), initialize it, etc...
    ... and then bind it with (ifx_sqlvar_t *)->sqldata ...

    To insert NULLs (when I set the sqlind indicator to -1), is it required to create/initialize the ifx_lo_t object or can I just leave the ifx_lo_t object undefined (by undefined I mean set the while ifx_lo_t structure to zero with memset()), or just set sqldata = NULL?

    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 10.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Thu June 04, 2020 08:22 AM
    Sebastian:

    Good question. All of my code always assigns a data location to the sqldata field even if I am inserting a NULL by setting the sqli* fields, so that is what I would do. But I can't say that the data pointer is required. So, my suggestion is assign a pointer to an ifx_lo_t object but I probably would not initialize it.

    Or, better, run a couple of tests and report.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 11.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Thu June 04, 2020 08:33 AM
    At the mi_exec level when you pass in the data, then along with the null indicator I always pass a valid data (datum) pointer.  

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 12.  RE: Define ifx_lo_t as NULL to insert or query for NULL BLOB/CLOB

    Posted Thu June 04, 2020 08:44 AM
    Edited by SEBASTIEN SF FLAESCH Thu June 04, 2020 08:47 AM
    Thanks Art,

    To insert NULLs, I do now fill the ifx_lo_t structure pointed by sqldata with zeros and set the content of sqlind buffer to -1.
    That works for me.

    I feel also that it's more risky to pass sqldata = NULL ( I did not test ) and it would anyway make my code more complex:

    I do allocate the ifx_lo_t structure at describe time from the CLOB/BLOB type detection, before knowing if values will be NULL or NOT NULL so anyway there is an ifx_lo_t structure available and pointed by sqldata.

    Would be interesting to know the cost of ifx_lo_def_create_spec() + ifx_lo_create(), though.
    If it's nothing but initializing the structure then it's safest solution I guess.

    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------