Stored procedure to retrieve data applying filters

Hi,

We have a scenario to extract data using database connection point in IONDesk with filters

1. Filter should be dynamic and to be extracted from BOD.

2. Can't use AnySQL modeler it allows only constant filters.

3. In Stored procedure to receive data from database there is no option to send a BOD and receive a data. It has only receive data or only send data.

Could anyone have idea on how to send a BOD to stored procedure and receive the response xml BOD.

I'm refereeing to the below document.

docs.infor.com/.../default.html

Find more posts tagged with

ION Connect

Accepted answers

All comments

Kevin Heiman

It would be helpful to better understand the use case however I can suggest a method we have used before. Create a 'control/filter' file in your database, then join this to the table you are filtering. a simple example would be something like:

Table: ControlFilter Columns: Table_name, filter_number, filter_column, filter_value

This would vary depending on the types of filtering you are doing. Now you can join your table with the control filter table.

SELECT company, ItemNumber, Price, Region, Category FROM ItemMaster AS im

INNER JOIN ControlFilter AS cfregion ON table_name = 'ItemMaster' AND filter_column = 'Region'

INNER JOIN ControlFilter AS cfcategory ON table_name = 'ItemMaster' AND filter_column = 'Category'

WHERE im.Region = cfregion.filter_value AND im.Category = cfcategory.filter_value

.....

There are many ways to approach this but with anySQL , as you currently cannot author the SQL, you have some basic limits. This technique gives you some options.

If you need something more dynamic you can update the ControlFilter just prior to running your anySQL. Create a BOD and send to a Database Connection stored procedure. Or use other techniques to update the control file..

... ps. hopefully my impromptu text version of the sql is relatively accurate....

Kevin Heiman

an additional comment on #3. again a use case would help narrow down the options. Commonly this was for an enrich scenario or for a process / acknowledge / sync scenario.

We have solved this by having the write SP finish by setting some state value, or by writing result data into a table. Then a separate Read would pick this up. If you need to correlate, have one 'synchronous' you could write the message ID into the result table.

Or we commonly have the write SP do its' processing, then write to an Outbox and the application connector picks up the new message. We have some code samples if needed.

It is important to leverage the headers, and ensure you manage the C_WAS_PROCESSED properly.

Do not use commit boundaries in your SPs.

We often use other Input SP parameters from the ION Connection point to manage soft options. If needed a Script could inject headers into the XML Body, that you can use later if that is needed.

Lots of choices.... Maybe not as simple as before, however we are working across a loosely coupled cloud to on-prem environment.

antonio-caria

As You know, A BOD is a XML file that could be converted to a string.

The Stored procedure can receive the Xml file, change it, and return the same XML changed or other xml created.

Or You can send the BOD localization for the stored procedure and return also a link to the new file created.

Legacy Contributor

@kevin-heiman

There is an event that triggers a custom BOD, on the custom BOD we used to generate a Date based on some logic in the MEC mapper.

Now using the date I need to filter out the records from a third party database using ION Database connection points.

Legacy Contributor

ION connection points do not work in the way you describe, what were you saying is a context for any technical development when it comes to Infor ION it has its own controls to play with. you can't return a link as you specified.

Legacy Contributor

Hi @sudhakaran-samadurai ,

To achieve this, we can look into three different solutions:

i) Sync the database with Infor Data Lake via ETL. Then use ION Data Fabric API's to query the database with custom filter's passed in the query body.

ii) In case data lake is not present, we can set up the database as an Infor Application. We need to install the Inbox/Outbox scripts present in ION Desk->Configuration->Downloads. Once the database is set up as an application, we can pass the BOD to the inbox table of the database. Then we will need to define a trigger to launch a stored procedure when a record is added which will read the filter values, create the necessary XML and put it into outbox table. ION will then read the XML from outbox table from where you can retrieve the data.

iii) The last solution would be to pass the custom XML BOD to a stored procedure via Database Connection Point. The store procedure would then read the XML tags, generate the necessary data and write the XML into another table (lets call this table 2) . Now you can create another record in the same connection point to call store procedure2 which can be scheduled to run. Store procedure 2 would now return the record from table2 and mark it as processed or delete the record from table2.

All the solutions would depend if you have the access to the destination database to make some changes.

Thanks,

Suriya

Kevin Heiman

My interpretation is :

assuming Cloud M3 and Infor OS...

There is an event (M3 BE activity) that triggers a BOD (IEC reaction to event creates a BOD , you have added a User Date tag and value, which is sent to OutBox).

ION data flow picks up the custom BOD.

Now using the date I need to filter out the records from a third party database using ION Database connection points.

I am unclear on this aspect. If I assume the third party Database is on premise or on another cloud, then the outline by Suriya hits most of the core points. Some of the decision rest on the environment, data options.

Most likely the Data base option (via Enterprise Connector) is the choice. We have some sample Scripts that demonstrate many DB Stored Procedure scenarios.

With a little more clarity on the assumptions, we can refine this furhter.

antonio-caria

Correct - in ION Connection no.

You must refurn XML in that case

Copyright © 2025 Infor. All rights reserved.