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