Implementing JSONB fields in postgres

Bastiaan Olij bastiaan at muxworks.com.au
Wed Feb 16 13:09:44 UTC 2022


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



More information about the omnisdev-en mailing list