Implementing JSONB fields in postgres

Phil (OmnisList) phil at pgpotter.co.uk
Wed Feb 16 09:28:49 UTC 2022


Hi Doug,

Firstly, no I haven't tried this at all.

Documentation says in the JSON Column Types section :
Note that JSON string literals must be suitably escaped in respect of 
quotes and square brackets, as shown above...

My understanding is that if you define the custom type as:
Do cSess.$addcustomtype(99999999,'JSONB')

Then when your table has a character field of length 99999999, it will 
assume its a JSONB field, and not a normal character field.


What's in your lResult variable?

First glance, indicates your thoughts are correct, lResult needs escape 
characters set?

But at the same time the example does not look that complicated to 
compile the JSON field.

So, it seems unlikely you have not set that correctly.

Maybe your code is still trying to calculate out a square bracket?

regards
Phil Potter
Based in Chester in the UK.

On 16/02/2022 05:53, 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 sitehttps://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
>


More information about the omnisdev-en mailing list