Implementing JSONB fields in postgres

Doug Easterbrook doug at artsman.com
Wed Feb 16 05:53:25 UTC 2022


We’ve just created some JSONB fields in postgres… and I got hit with issues trying to insert records and there is nothing in the JSONB field


the documentation at omnis web site https://www.omnis.net/developers/resources/onlinedocs/index.jsp#


leads me to believe that all I need to do is something like tell the session object for the dam that all JSONB fields are a certain size .. lets say 99999999   (one less than the max char size in omnis)

eg

Do cSess.$addcustomtype(99999999,'JSONB') Returns #F


and then define and JSONB field in my schema’s to be length 99999999



and then and insert into a json field using table classes and bind notation will cause the dam to handle bind variables correctly.


insert into jsontest values(1,@[lResult])




I’ve done a simple test by changing out connection and field .. and I get a sql error as follows

SQL Server Error: ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: 
unnamed portal parameter $43 = ''

TM Error: The statement could not be executed
SQL State: 22P02 Invalid text representation



has anybody unlocked inserting into JSONB fields  ….  I’m thinking I may need to escape something…  



thanks in advance.




Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978



More information about the omnisdev-en mailing list