[Patrick Has successfully completed the online course Introduction]

2 years 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!

Documentation:
https://community.simplifier.io/doc/current-release/integrate/connectors/sql-connector-details/sql-connector-calls/#1627396667830-592fc653-04a6

Example:
https://community.simplifier.io/marketplace/example-shoppinglist-with-dynamic-sql/

Here’s a simple, anonymized example of our implementation:

[Jennifer Häfner Has 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 Certification]

2 years ago #34487

Up

2

Down

::

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.

[Patrick Has successfully completed the online course Introduction]

1 year ago #34954

Up

2

Down

::

You’re right! The assignment of the connector was still missing for the new business object. Now everything works fine! Thanks for the good support!

[Jennifer Häfner Has 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 Certification]

1 year ago #34691

Up

1

Down

::

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äfner Has 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 Certification]

1 year ago #34953

Up

1

Down

::

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.

 

[Patrick Has successfully completed the online course Introduction]

2 years ago #34525

Up

0

Down

::

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äfner Has 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 Certification]

2 years ago #34531

Up

0

Down

::

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"
     }
]

[Patrick Has successfully completed the online course Introduction]

2 years ago #34532

Up

0

Down

::

Hi Jennifer, that’s exactly what I did. I only anonymized the values for this forum, sorry for that! In the real project, the values are of course the same. What else could be the reason?

[Jennifer Häfner Has 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 Certification]

2 years ago #34536

Up

0

Down

::

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.

[Patrick Has successfully completed the online course Introduction]

2 years ago #34643

Up

0

Down

::

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.

[Patrick Has successfully completed the online course Introduction]

1 year ago #34951

Up

0

Down

::

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.