PostgreSQL JSONB data type in DB resource

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Jon, Steve, Ian, Dave

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

PostgreSQL JSONB data type in DB resource

#1

Postby ericb » Tue Jun 23, 2020 9:42 pm

Does Ebase support PostgreSQL's jsonb data type in DB resources? I've tried different combinations of data types for the resource field as well as the mapped form field, but haven't found any that worked without requiring a manual typecast in the insert/update query (which means assisted SQL can't be used, unless there's a way to typecast in assisted SQL?).
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: PostgreSQL JSONB data type in DB resource

#2

Postby Jon » Thu Jun 25, 2020 8:31 am

It should work if you set the form field type to Object.
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: PostgreSQL JSONB data type in DB resource

#3

Postby ericb » Thu Jun 25, 2020 1:46 pm

Hi Jon,

I do indeed have the form field type as Object. I can't find any resource field type that doesn't lead to an SQL error though.

Any kind of string type (varchar, clob, longvarchar) gives me this error:
ERROR: column "other_providers" is of type jsonb but expression is of type character varying Hint: You will need to rewrite or cast the expression.
Meanwhile, any binary type (blob, longvarbinary) lead to this error:
No value specified for parameter 48.
(parameter 48 in this case corresponds to my JSONB column)

The workaround I've found for now is to create a new resource just to update this column, which uses native SQL so I can do a "::JSON" cast in the update/insert statements. (I got this from this blog post, under the "JSON Data Insertion" section). I wanted to keep the original resource using Assisted SQL, because it has quite a few fields and is used in many forms. Changing it to Native SQL would require every field to be mapped in every form, which we don't need and would be a big bother to change. I'm still using the original resource to load the JSON field, but I've set it to Read Only so the resource doesn't try to update it. This way I avoid needing an extra call to the DB when loading my form. For this solution, I'm using the CLOB type in both resources for the JSON column.
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: PostgreSQL JSONB data type in DB resource

#4

Postby Jon » Thu Jun 25, 2020 3:07 pm

Ah, I tested this in the latest version - V5.8 and it worked fine with a resource field type of either VARCHAR or LONGVARBINARY. But I think we may have changed something in this release which has effectively fixed this problem.

I'm not exactly what syntax you're using, but it should in general be possible to put your cast into the column list (in the Select columns box) with Assisted SQL - this is certainly possibly with functions. But you may need to be careful to use the AS keyword to rename the column in the result set e.g. something like this:

Code: Select all

select col1, todate(col2) as col2, touppercase(col3) as col3 
The resulting column names should match the resource field names.
0 x

ericb
Ebase User
Posts: 82
Joined: Fri Jan 15, 2016 2:34 pm

Re: PostgreSQL JSONB data type in DB resource

#5

Postby ericb » Thu Jun 25, 2020 3:11 pm

Ah, that may explain why it didn't work for me; this application is still running on 5.5.0.

I tried adding the cast to the Assisted SQL column list, but it looks like it might not be carried over to the update/insert logic, since I'm still getting the same error. I'll continue using my workaround for now, but good to know that in 5.8+ it shouldn't be needed anymore.

Thanks!
0 x


Who is online

Users browsing this forum: No registered users and 7 guests