Has anyone worked with JSON/JSONB databases in CW?
CREATE TABLE test(id int, data JSONB, PRIMARY KEY (id));
Anyway to use the IDE and standard ABC File classes w/ ODBC against it?
Or would you have to hand code everything?
Hi Kevin,
I havenāt, and I hope I never have to. Think of the Clarion dictionary as a mapping between the data that Clarion works with, which comes in in the record buffer, and rows and columns in the database. Usually that is a nice, simple, mapping: The CustomerName in the Customer table maps to the Customer name field in Clarions Customer table. When you are dealing with JSON data, that mapping is no longer simple, and the only tool you really have to help you is the PROP:Name of the field (and possibly also the table. That is going to get messy awful fast.
You might get a better idea looking at the example here:
where in their simple example they have:
SELECT info ->> ācustomerā AS customer
FROM orders
WHERE info ā āitemsā ->> āproductā = āDiaperā;
In Clarion you might set this up with two tables: orders and order_items, but the field āproductā in order_items is gotten from the database by getting "info ā āitemsā ->> āproductā: from the orders table in the database. So in Clarion I think you would give āOrdersā as the Postgres table name for the order_item table in Clarion, and that mess in the double quotes above would be the external name for the column. If you could stop there it might be possible, but still very messy and probably difficult to maintain and debug, but go down a bit and you have
SELECT info ->> ācustomerā AS customer,
info ā āitemsā ->> āproductā AS product
FROM orders
WHERE CAST ( info ā āitemsā ->> āqtyā AS INTEGER) = 2
Now you have to wrap cast ā¦ as integer around your column name.
So short answer: for simple stuff you might be able to use PROP:Name and get clarion to do things without having to handcode your prop:sql. But there may be some relatively simple things that you want to do that Clarion will not be able to do, and then you will be in messy handcode real fast.
1 Like
Yeah, I was not expecting a resounding āyesā. It would require a new File IO subsystem relying heavily on tools like jFiles moving data into and out of our standard CW structures all in the background. I was just reading about it and found it interesting - a lot like NoSQL is interesting, but i donāt want to use it, 
JSON and XML are great for transferring data between systems. If the source system decides it needs to collect another attribute it can add that attribute to the JSON file it exports, and the receiving system can just ignore that new attribute until it feels like dealing with it. Clarion programs, along with most business processes, is designed to deal with structured information. If thereās a lot of stuff you donāt know what to do with and donāt care about, then a sensible first step is just to take the stuff you do care about and put it in a structure where you can deal with it. Whether you use jFiles/whatever or the database to āreadā the JSON/XML into tables is up to you: the first method makes it easier to switch databases but is possibly more time-consuming to write and maintain.
I just went through this with reading XML; I originally set it up using Oracleās tools to read the XML, then decided that a SQLite database was a more sensible target for the data. So I couldnāt use the Oracle code any more. The clarion code (using Robert Paresiās iQ-XML) was much larger and clunkier and I had to start from scratch.
1 Like
Kevin,
Iām with you on trying to ease it up. Iāve found it very useful to have JSON in a database. Bringing in a JSON object so far was to read it as a CSTRING. Best way was then to use a JFiles object to manipulate the JSON and then write it back using the string again. Iām sure there could be a way to use a BLOB type instead of the long CSTRING, just to make sure that I donāt run into a size limit. My project so far didnāt require much of the JSON values, so I donāt have answers yet.
1 Like