Informix NoSQL syntax in SQL + UDR way / Not mongoDB way
Eric Vercelletto could not update a BSON column from 4GL/ESQL using a JSON string in a prepared UPDATE with ?::BSON, getting error -937. Andreas Legner suggested ?::JSON::BSON, which fixed it. Art Kagel added that an implicit cast from lvarchar to BSON could be created as an alternative.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 then FOREACH crs_scan_coa_full INTO l_rec_coa_explicit.* parse the return string into a JSON string END FOREACH 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, l_rec_coa_explicit.end_year_num,l_rec_coa_explicit.end_period_num,l_rec_coa_explicit.group_code, l_rec_coa_explicit.class,l_rec_coa_explicit.section,"KA",l_rec_coa_explicit.acct_code 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 ------------------------------ [eric] [Vercelletto] [] [Founder] [kandooerp.org] [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. ------------------------------ #Informix
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 ... BR, Andreas ------------------------------ Andreas Legner ------------------------------
Touché! 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: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org
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. www.askdbmgt.com ------------------------------