JSON in to Oracle DB out

[Ryan Curtin]

I have a design question for the group… I am configuring an interface to accept JSON from a sending service and Cloverleaf will ingest it and transform the data to send out to an Oracle database. The JSON structure contains several repeating objects in it.

What is the best approach to ingesting the JSON as a whole and be able to transform and load the data into the database schema with the repeating elements (especially if the number of repetitions can vary)? Is there a means of doing this in the xlate or other option in the engine without programmatically breaking up the message into several individual messages per repetition and sending it out to the database.

Thanks in advance.

Topic

[Jim Kosloskey]

What release of Cloverleaf?

If you are on 6.2 or later, there is a JSON Configurator and the Xlate handles JSON.

The repetitions I think need to be specified as Array type in JSON in order for the ITERATE to reference the repetitions.

I really don’t know any more than that but there seems to be a fair amount of documentation on 6.2. 19.1 might have even more.

Personally I would want a Stored Procedure on the Oracle side with a mechanism defined to return success/fail notification back to Cloverleaf along with text related to the fail.

I would also want the Owner of the Oracle DB to be responsible for the Stored Procedure but design the Cloverleaf Interaction as a joint effort.

I have no experience with JSON and Cloverleaf but this seems doable.

How were you planning on interacting with the Oracle DB (JDBC via Cloverleaf or use the Cloverleaf available ODBC drivers)?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Ryan Curtin]

Using Cloverleaf 6.2.3 and the JDBC via cloverleaf db-out connector. I think this might be a bit of a db design concern because the xlate seems to only map 1-to-1 and so the last repetition I map to the db schema element is what sticks in that field.

[Author]

Replies