Implementing JSONB fields in postgres

Doug Easterbrook doug at
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

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)


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
Phone (403) 650-1978

More information about the omnisdev-en mailing list