View Only
  • 1.  Informix NoSQL syntax in SQL + UDR way / Not mongoDB way

    Posted Sun July 25, 2021 06:13 AM
    Hi all,

    I am at the moment testing using NoSQL data in some tables ( stored as BSON), thru the use of ESQ/C or 4GL language

    All good when I read data from the table, that is
        LET qry_stmt = "SELECT acct_code,desc_text,start_year_num,start_period_num,end_year_num,end_period_num,group_code,class::JSON,section",
        "   from coa where cmpy_code = ? "
        " and bson_value_lvarchar (class, 'lvl1') = ? "

        PREPARE p1 from qry_stmt
        DECLARE crs_scan_coa_full CURSOR FOR p1

    FOREACH crs_scan_coa_full INTO l_rec_coa_explicit.*   
         parse the return string into a JSON string
    all works great

    now I want to insert or update the row, including the BSON column with its whole contents by doing something like this
    LET qry_stmt = "UPDATE coa SET (desc_text,start_year_num,start_period_num,end_year_num,end_period_num,group_code,class,section) ",
        " = ( ?,?,?,?,?,?,?::BSON,?) ",
        " WHERE cmpy_code = ? and acct_code = ?"
        PREPARE p_udp_coa FROM qry_stmt

    ... set the JSON string in the midtime then
    EXECUTE p_udp_coa USING l_rec_coa_explicit.desc_text,l_rec_coa_explicit.start_year_num,l_rec_coa_explicit.start_period_num,

    But this one does not work, I get an error like this
    The error code (-937) was received.
    User Defined Routine error. mi_db_error_raise() has been called from within a user-defined routine and set a fatal error. For an explanation, look at the SQLSTATE that the user-defined routine set.

    Any hints about how to cast the JSON string to BSON in the prepare, considering there is no built in function to cast the variable from JSON to BSON before passing it the to the prepared statement ?

    Thanks for your lights
    I would like for now to avoid doing the update with the  bson_xxxxx UDRs that would have me to update every json member one by one

    Thanks in advance

    [eric] [Vercelletto] []
    [Pont l'Abbé] [France]
    [+33 626 52 50 68]

    Disclaimer: 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.

  • 2.  RE: Informix NoSQL syntax in SQL + UDR way / Not mongoDB way

    Posted Sun July 25, 2021 07:20 AM
    Hi Eric,

    so l_rec_coa_explicit.class is what you're calling a JSON string?

    Maybe ?::JSON::BSON, instead of ?::BSON, is all you need.

    If not I'd start with a simplified version of this ...


    Andreas Legner

  • 3.  RE: Informix NoSQL syntax in SQL + UDR way / Not mongoDB way

    Posted Mon July 26, 2021 02:21 AM



    You got it Andreas, now it works


    Thanks bunches!!!





    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    KandooERP Founder and CTO
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout:
    www :



  • 4.  RE: Informix NoSQL syntax in SQL + UDR way / Not mongoDB way

    IBM Champion
    Posted Mon July 26, 2021 08:58 AM
    Eric, an alternative would be to create your own lvarchar_to_bson direct IMPLICIT CAST.

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