Repeatable Statement to insert a collection in a database
-
PatrickHas successfully completed the online course Introduction1 year ago #34476
Hi, we want to pass a collection to an SQL connector via a server-side business object in order to write all the objects it contains to the database. Unfortunately it doesn’t work for us yet. We are currently using the Simplifier version 6.0.
Is there an example of repeatableStatement to download? The current link in the documentation is broken!
Example:
https://community.simplifier.io/marketplace/example-shoppinglist-with-dynamic-sql/Here’s a simple, anonymized example of our implementation:
Jennifer HäfnerHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification1 year ago #34487::Hi Patrick,
Thanks for pointing out the broken link. We have removed it now, because the application that it pointed to is currently not available on the marketplace.
In your case, the Connector call with the repeatable statement looks fine.
But in your Business Object function, the code to execute the Connector call needs to be changed to:
Simplifier.Connector.ConnectorName.CallName({ profile: collection })
This is because the input for a Connector call is always an object (key – value pair of parameter names and their values), and you have provided the alias ‘profile’ for your input parameter ‘parameterCollection’.
I hope this solves your issue.
Jennifer HäfnerHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification1 year ago #34691::Hey Patrick,
Your statement seems to be fine. Maybe there’s a bug in the Simplifier version that you are using, but unfortunately, we do not support Simplifier 6.0 anymore, so there won’t be any fixes for this issue.
Is the parameter collection also empty when you are using the connector call in the Process Designer and map an input collection to the input parameter?
In general, please consider upgrading to a more recent version, so you are up to date with all the features and bug fixes.
Jennifer HäfnerHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification1 year ago #34953::Hi Patrick,
Good to hear that the update has fixed your issue!
Regarding the runtime exception: have you added the connector to the list of artefacts that the business objects has access to (select the business object in the list, click on the edit button, add the connector at the left side of the screen)? Otherwise, the business object cannot execute the connector calls, for security reasons.
PatrickHas successfully completed the online course Introduction1 year ago #34525::Thank you, but our issue is not solved yet! I entered the alias in the connector call, as described above, and an array with objects is passed to the connector via the server-side business object, but no data is written to the database. The data types for the collection and the struct are correct. If you select the template as a value via the connector’s test function, however, data with 0 values will be written to the database, but an individual JSON with the same syntax as in the template will be ignored. Any ideas?
Attachments:
You must be logged in to view attached files.Jennifer HäfnerHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification1 year ago #34531::Hi Patrick,
according to your SQL statement, the input collection has to contain objects with the keys ‘col1’, ‘col2’, ‘col3’, ‘col4’. However, in your input JSON, these fields are called ‘value1’, ‘value2’, ‘value3’, ‘value4’.
The names of the fields in the SQL statement have to match the fields inside the object.
For example, if you want to insert one or more fruits into the table ‘Fruit’, the repeatable insert statement will be:
INSERT INTO Fruit (color, name) VALUES (:color:, :name:);
The input collection looks like this:
[ { "color": "red", "name": "Apple" }, { "color": "yellow", "name": "Banana" } ]
Jennifer HäfnerHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification1 year ago #34536::Hi Patrick,
Please execute the connector once again and then checkout the Logs section on your Simplifier instance. Next to the list item of the connector execution, there is a detail button that shows the executed SQL statement and the input values, as well as some additional infos.
Since you are using Simplifier 6.0, this section may look a little bit different than my screenshot (taken with Simplifier 7.0).
I hope the information in the detail section will help us find the issue here.Attachments:
You must be logged in to view attached files.PatrickHas successfully completed the online course Introduction1 year ago #34643::Hi Jennifer, I’ve now created a new project and only implemented the repeatableStatement function in it for testing. I also used the fruits (with struct, collection and database table). But the problem already exists when just executing the test function in the connector. The input collection is empty even with a manual JSON input. Here are some screenshots.
Attachments:
You must be logged in to view attached files.PatrickHas successfully completed the online course Introduction1 year ago #34951::Hello Jennifer, our Simplifier has received an update to version 6.5 and the connector test now successfully executes the database import via repeatable statement! So this bug has been fixed.
However, we still have a runtime exception when the connector is called via a server-side business object. The input collection (with fruit struct example) is the same as in the connector test and all names (connector, call, parameters etc.) match. Any ideas?
Attachments:
You must be logged in to view attached files.
You must be logged in to reply to this topic.