Implementing JSONB fields in postgres

[Doug Easterbrook]

hi Bas et al.

thanks for the suggestions - I got my insert to work and I’ll play a little later today to find out which one did it for me, or if I need all three


essentially I already had added this custom type to me postgresql session, so I do not yet know if this assisted the solution

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



before i did my insert (using bind variables), I added some code

# JSON field
If len($cinst.PMB_SEND_TRACKING)=0
Calculate $cinst.PMB_SEND_TRACKING as #NULL
End If

that set the actual line in the table class current line to #NULL which allowed an insert.


so, success!!!!   thanks

this lets me move forward and determine how I want to do it optimally



I now have other options to explore in our table class since I could now
1) remove the field from  $insertnames  if its emtpy
2) or, if emtpy, replace the $insertnames of 

PMB_SEND_TRACKING = @[$cinst.PMB_SEND_TRACKING] 
with
PMB_SEND_TRACKING = NULL


now to come up with a generic solution that works for our code base and how we built our internal data dictionary. ….    not yet sure what path I will take.


this saved a bunch of time — again ,thanks



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

> On February 16, 2022, at 5:09 AM, Bastiaan Olij <bastiaan at muxworks.com.au> wrote:
> 
> Hi Doug,
> 
> I think with an empty string its still treating it as a string, and an empty json isn't valid. It's probably expecting a NULL.
> 
> Its been awhile since I last worked on this but I'm pretty sure I overwrote the $insert and $update logic to create an SQL query that uses @[var] if I have a value and NULL if I don't
> 
> Alternatively if you can, just set the string to '[]' which is a valid empty array in json.
> Cheers,
> 
> Bas
> 
> On 16/02/2022 4:53 pm, Doug Easterbrook via omnisdev-en wrote:
>> 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
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> -- 
> Kindest Regards,
> 
> Bastiaan Olij
> bastiaan at muxworks.com.au
> +61-432144833
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com