Customize Object Schema (BOD, JSON, ANY)

Business Problem

To integrate different systems, usually, you have a default set of messages you can use to exchange data. Sometimes this default setting doesn’t fit the requirement and you need to create new messages

---

---

Tutorial

The integration platform is the application you use to let different systems talk to each other. to achieve this goal the integration platform uses several options, like reading files, reading and writing databases, and calling APIs, and all of this is done using a default set of messages. In the ION world, this set of default messages are BODs, JSON, CSV, DSV, and ANY, known as object schema and sometimes they are not enough to serve all the requirements and for this, you have to create custom messages

please watch the video below for an introduction to the BOD (Business Object Document) message system

In this tutorial, we will explore how you can create custom messages (BODs and JSON), in some cases directly from the File Template, Connection Points, and in other cases from the Data Catalog

• File Template
• DB Connection point
  • Stored procedure
  • AnySQL
• API Connection point
• Object Schemas
  • from the Connection point (DB Connector)
  • from sample data
    • example file
    • manually

---

1. File Template

Login to your Infor Cloudsuite. After you have logged in, click the ION Desk icon.

Click the menu icon then expand the Connect option and click the file template item, in the main page click the Add option.

This page is composed of the configuration part on the left and 2 tabs on the right (settings, field).

In the configuration part, you need to provide a name for the file template, you can add also a description, and the most important part you have to define the file type (text, binary), file encoding (UTF-8, ISO8859-1), and the format type (Delimited, Fixed-Length, Fixed-Length & Delimited, Full BOD and XML).

For more details on the meaning of these settings please refer to the official documentation.

In the settings tab, you have to provide the BOD name as Document, the field separator, and the line separator of your file. There is another property named Generated document that can be (single, or multiple).

Single means that you have one BOD for each line, and Multiple means that you have one BOD for each line of the file.

In the fields tab, you have to define the structure of the BOD giving the name of the fields that will be the element tag of the Document (BOD Noun). Notice that the FieldName can be optional, this means that in the file you can skip the value for the fields you have selected as optional.

After saving the configuration the Generated Metadata option will be available and you can click on that to create the custom BOD, selecting the DocumentID and providing an example template of the actual file.

You should get a “Document metadata generation is successful” message.

At this stage, you have generated a new custom BOD and you can check the object schema going to the data catalog.

---

2. DB Connection Point

2.1 Stored Procedure

Let’s start with the creation of the DB connector connection point.

From the ION Desk page, click on the connection point option and then in the + Add dotted box.

From the list, select the option Database

On this page, you have the left side where you have to provide the name and description of the connection point, plus you have to select the location you want to use to connect to the database.

NOTE: the location is the name that identifies the enterprise connector you have installed locally on your server.

After selecting the location, you have to select the type for the DB connection point between (Stored Procedure, AnySQL Modeler, Data Loader), in this case, select Stored Procedure.

On the right side, you have 2 tabs, one for the connection details and one for the document details.

For a complete list of details and meanings, please refer to the official documentation.

Select the DB driver from the list drop box, choose if you want to use basic settings or advanced settings, and based on your choice, provide the hostname, port number, and schema name for basic settings or the complete URL for the advanced one, then specify user name and password for connecting to the database.

Once done, click the test button to check the connection with the database and if everything is okay you get back “Test succeeded”

In the document tab, you have to create a scenario to read table data from the database using the Stored procedure. Move to the document tab and click the ( + ) button, the read scenario is selected by default.

Give the name of the Procedure and provide the SQL statement to execute the Stored Procedure in the text area and click save.

Let’s move now to the SQL server and create the stored procedure. With SQL Management Studio, connect to the database we have specified in the connection tab and expand the database then Programmability, and select Stored Procedures

Right-click and select Stored Procedure…

This is the code for the SP for SQL server

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[ReadItems]    Script Date: 11/25/2022 5:18:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Giampaolo Spagoni
-- Create date: 2022-11-25
-- Description:	Read Item
--
--           _____________________________
--          | STATUS |   DESCRIPTION      |
--          |________|____________________|
--          |   0    |  new record        |
--          |   1    |  elaborated        |
--          |   2    |  waiting the ACK   |
--          |   3    |  error             |
--          |________|____________________|
--
-- =============================================
CREATE PROCEDURE [dbo].[ReadItems]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRAN

    -- Insert statements for procedure here
	SELECT(SELECT DB_ITEM.ITEM, DB_ITEM.DESCRIPTION, 
				  DB_ITEM.UOMA, DB_ITEM.TYPES,
				  DB_ITEM.GROUPS, DB_ITEM.COLOR,
				  DB_ITEM.HEIGHT, DB_ITEM.WIDTH,
				  DB_ITEM.DEPTH, DB_ITEM.UOMB
			FROM ITEM_EXT as DB_ITEM
			WHERE DB_ITEM.STATUS = 0
	FOR XML AUTO,ELEMENTS, type).query('for $i in /DB_ITEM return <DataArea>{$i}</DataArea>');

	UPDATE ITEM_EXT
	SET STATUS = 2 -- waiting a response
	WHERE STATUS = 0

	COMMIT TRAN
END

Click the Execute button to create the Stored Procedure and you receive a message with the success of the command

Let’s insert now some rows into the table

USE [TEST]
GO

INSERT INTO [dbo].[ITEM_EXT]
           ([ITEM],[DESCRIPTION],[UOMA],[TYPES],[GROUPS],[COLOR],[HEIGHT],[WIDTH],[DEPTH],[UOMB])
     VALUES
           ('CAR001','car 1','EA','Product','USF100','red',140,200,450,'mm')

INSERT INTO [dbo].[ITEM_EXT]
           ([ITEM],[DESCRIPTION],[UOMA],[TYPES],[GROUPS],[COLOR],[HEIGHT],[WIDTH],[DEPTH],[UOMB])
     VALUES
           ('CAR002','car 2','EA','Product','USF100','white',135,195,420,'mm')

INSERT INTO [dbo].[ITEM_EXT]
           ([ITEM],[DESCRIPTION],[UOMA],[TYPES],[GROUPS],[COLOR],[HEIGHT],[WIDTH],[DEPTH],[UOMB])
     VALUES
           ('CAR003','car 3','EA','Product','USF100','white',400,400,400,'mm')


GO

Move back to infor OS, and from the connection point you were creating click the TEST button and you will receive as output an XML with the BOD structure

This proves that the connection point works properly and you are able to read the table data and generate an XML. Now let’s create the custom BOD that generates the object schema in the Data Catalog, but before doing this you have to reset the status of the records in the table because the Stored Procedure has updated the status for not reading the same records over and over (see the code above)

So go to the SQL management studio, open a new Query editor and run the below command

Update [dbo].[ITEM_EXT] set [ITEM_EXT].[STATUS]=0 where [ITEM_EXT].[STATUS]=2

Now you are ready to generate the schema for the custom BOD

Back again to Infor OS and click the Generate button

As in the file template, you get a form where you have to select the DocumentID and when you click the “OK” button you will see the custom BOD name in the document text box

Go to the data catalog to see the newly created object schema

2.2 AnySQL

Let’s start with the creation of the DB connector connection point.

Follow the same steps as above for the creation of the connection point, the only difference is the type that now is AnySQL Modeler. Also, the connection tab is the same

Move to the document tab, click the ( + ) button, and then the EDIT button

From this page, give the name to the model, give the name to the Document Name and click the (+) button to retrieve the list of tables of the database

Drag the table into the canvas and click on the select columns icon

Select the columns you are interested in and click the arrow, in the ITEM row from the dropdown list of the property column select identifier, then click the UPDATE button

You will see the result in table format

Click the settings icon as described above and in the settings form, select output format as JSON, flag the Enablement Improved Data Type Normalization, click twice the Add Incremental Key button and confgure as below, then click UPDATE

Save the connection point and click the GENERATE METADATA button to create the JSON Object Schema, you will get a message that the schema has been generated successfully

Go to the data catalog to see the newly created object schema

---

3. API Connection Point

Even for the API Connector, you have to create a connection point.

From the ION Desk page, click on the connection point option and then in the + Add dotted box.

From the list, select the option ION API

On the left side of the page, give the name of the connection point, on the right side there are 2 tabs.

in the connection tab, click the IMPORT button and select the CSV file of the service account. Watch the video below on how to create a service account

Click on the document tab, then the (+) button, By default, the Get from API scenario is selected, give a name to the API Call and click the SELECT button to choose an endpoint from the registered API suite in the gateway

In the middle of the page, click the Output Document tab, then the GENERATE METADATA button, the pop-up form select Document Type ANY and give it a name to the new custom schema and click OK.

To check that everything is OK, click the TEST button below the document tab. A pop-up form comes up, click again the TEST button and you receive the output of the API call

Go to the data catalog to see the newly created object schema.

---

4. Object Schema

4.1 From the Connection point (DB Connector)

Go To the data catalog and click the (+) button and select the option Generate from Connection Point.

A wizard with 5 steps starts, in the first step you have to select the connection point of type DB connector.

In the second step, from the available tables, you have to choose which table you want to use to create your custom schema.

The next step is to rename the table name if you want to give a specific name to the custom schema, plus you can set the primary key of the table as an identifier. keep in mind that we are generating a JSON custom schema and for this reason, during the wizard, you are asked to provide an identifier, variation, timestamp, and delete indicator.

Now you can check your choices and you are ready to generate the new custom object schema by clicking the Generate Metadata button at the right bottom.

The last step is the summary where you can see the result of the operation.

Click the close button to go back to the data catalog object schema and check the newly created custom schema.

---

4.2 From Sample Data

Go To the data catalog and click the (+) button and select the option Generate from Sample Data.

At this stage, you have 2 options, Generate from sample data (default), or Build manually. Let’s continue with the default option. Now you have to drag and drop or select and upload a file that contains the structure for the new schema. Based on the file extension you provide, the system understands if you want to create a custom BOD or a custom JSON schema.

Let’s make a test with an XML and a JSON file and let’s see the differences

Let’s create now a custom schema manually. Go to Object Schemas below Data Catalog, click the (+) button and select Generate from Sample Data

From the current page, select Build manually and click next.

From here give a name to the custom Object, type a title and description (optional), select the Object type, and click NEXT

As you have selected type BOD, on the next page you have to provide the properties definition to define a BOD in XML format, so for each property, give a name and select the property type and the data type plus other configurations and click NEXT.

From the Additional Metadata steps, select the Identifier Path and the supported Verbs of the BOD and click NEXT.

Review the configuration and click FINISH to generate the new custom BOD schema.

---

Downloads

Custom Object Schemas