Data management
      Back to home
      1. Knowledge Base
      2. Martini
      3. Data management

      How to write the contents of an EDI document to a SQL database

      Converting an EDI document to XML and then writing the contents to a SQL database

      Whilst many organizations are moving towards more modern RESTful APIs that process data in a data format such as JSON/YAML/XML the reality is that many organizations continue to use systems that process documents using EDI.

      Sometimes it may even be a prerequisite to doing business with a major supplier or customer that you can demonstrate the capability to communicate electronically with them via EDI.

      This demo will show you how to process an EDI document and write the contents of it to a SQL database. The demo will demonstrate a use case for processing a purchase order that was created using the Edifact EDI standard. However, it can equally apply to processing other EDI documents including those that use the X12 standard.

      How to download & RuN this "EDI document to SQL database" demo

      • If you haven't already done so, get yourself a Martini.
      • Within either Martini Desktop or Martini Online select the "Data Files to and from SQL" demo from the Welcome Screen and click Install:
        screenshot-welcome-screen
      • The Package demo001-edi-to-sql will be automatically downloaded. Right-click the Package and click Start. A green icon will indicate it has started:
        screenshot-package
      • Documentation containing a Test Procedure to run the demo is included in the Package readme file \resources\readme\readme.md. The readme file is automatically opened when the Package is started.

      screenshot-documentation

      • The Test Procedure will guide you through the process to convert a sample EDI document to XML and then write the contents to a SQL database.

      Line by line: How this "EDI document to SQL database" demo works

      Startup services:

      Startup services can be viewed by right-clicking the package \\demo001-edi-to-sql and selecting Configure.

      Upon startup the following services will initialize the database and create the endpoints:

          • \\demo001-edi-to-sql\code\demo001\config\InitializeDatabase
            Creates a database connection pool.
          • \\demo001-edi-to-sql\code\demo001\config\CreateDirWatcher
            Identifies the installation directory of the embedded Martini Runtime instance and sets the location of the folders \\demo001-edi-to-sql\code\demo001\services\ConsumeEdi and \\demo001-edi-to-sql\code\demo001\services\SaveToDatabase that are to be monitored by the Directory Watcher endpoints \\demo001-edi-to-sql\Endpoints\edi-dropbox-directory-watcher and \\demo001-edi-to-sql\Endpoints\processed-edi-docs-directory-watcher.
      Martini Online users: Directory Watcher is not available in the Martini Online version. Refer to the _Martini Online users _section in preceding application flow steps.

      Processing the EDI document:


      The Directory Watcher endpoint \\demo001-edi-to-sql\Endpoints\edi-dropbox-directory-watcher is configured to listen for new files written to the folder \\demo001-edi-to-sql\resources\edi-dropbox. When a new file is detected in this folder it will run the service located at \\demo001-edi-to-sql\code\demo001\services\ConsumeEdi.

      When the \\demo001-edi-to-sql\code\demo001\services\ConsumeEdi service is triggered by the Directory Watcher endpoint, an input stream containing the contents of the EDI document will be passed to it.

      The service \\demo001-edi-to-sql\code\demo001\services\ConsumeEdi uses two Functions to convert the EDI document to XML:

          • Line 8: File.outputStream
            Double click the function to see its inputs and outputs. The file input of this Function creates an empty XML file to be saved in \\demo001-edi-to-sql\resources\processed-edi-docs. The output of the function is mapped to a fileToSave and is passed to the next line.
          • Line 14: EdiToXml.convertToXmlFile
            This is a custom Function written in Groovy. The Groovy class is located at \\demo001-edi-to-sql\code\demo001\groovy\EdiToXml. The Groovy class uses a Java library located at \\demo001-edi-to-sql\lib\staedi-1.16.2.jar. The Jar contains resources that convert an EDI document to XML. The custom Function EdiToXml.convertToXmlFile from the Groovy class EdiToXml is then used in our service to write the contents of the EDI document to the XML file that was created by the File.outputStream function from Line 3.

      As a result of the service above an XML representation of the EDI document will be created in \\demo001-edi-to-sql\resources\processed-edi-docs.

      Martini Online users: The service \\demo001-edi-to-sql\code\demo001\services\ProcessEdiDropbox combines all the Functions from the services being triggered by the Directory Watcher into a single service. The difference between this service and the services used by the Directory Watcher endpoint is instead of creating an XML file representing the EDI Document, this service uses an XML string instead and passes it directly to a Function that converts the XML to an object, which is then mapped to a model. The following Functions are as follows:

      Line 3: FileMethods.listFiles
      Double click the function to see its inputs and outputs. This function accepts 3 inputs. directory, extensions, and recursive. These inputs refer to the location of the directory that contains the list of files we will process( in this case, the edi-dropbox ), the types of file to filter, and to make the function list the files of a subdirectory if it has one, respectively. The output of this function is a collection of files that matches the given filter which is then mapped to a property called fileList

      Line 4: Iterate Step
      In this line, we are telling our service to iterate through the fileList and with each iteration, process the conversion of the EDI documents, converting them to an XML string, and finally, saving them to the database. The child functions under the Iterate Step are as follows:

      Line 5: EdiToXml.convertToXmlString
      This is a custom Function written in Groovy. The Groovy class is located at \\demo001-edi-to-sql\code\demo001\groovy\EdiToXml. The Groovy class uses a Java library located at \\demo001-edi-to-sql\lib\staedi-1.16.2.jar. The Jar contains resources that convert an EDI document to XML string. The Function EdiToXml.convertToXmlString from the Groovy class EdiToXml returns an XML string representation of the EDI document processed from the resources\edi-dropbox folder which is then converted to an object and finally mapping it to a model so we can pick and map the data we want to save in the database.

      Line 6: XmlMethods.streamToGloopObject
      This function is used to create an object which can be mapped to a model. This service accepts an XML string and returns an object which is then mapped to purchaseOrder model.

      Line 7: InsertPurchaseOrder
      Invokes the SQL Service located at \\demo001-edi-to-sql\code\demo001\services_sql\InsertPurchaseOrder that allows you to execute SQL queries for manipulating the database it is connected to.

      If this service is invoked, it will pull all the EDI Documents with a txt file extension under the resources\edi-dropbox folder. It will then iterate on each file, converting them to an XML string, then to a model, and finally, saved to the database.

      Saving the EDI data to the database

      When the EDI document has been converted to XML and then written to the folder \\demo001-edi-to-sql\resources\processed-edi-docs, the Directory Watcher endpoint \\demo001-edi-to-sql\Endpoints\processed-edi-docs-directory-watcher will detect the file and send it as an inputStream to the service \\demo001-edi-to-sql\code\demo001\services\SaveToDatabase.

      The service SaveToDatabase reads the XML and writes it to a database:

      • Line 8: Xml.streamToGloopObject \ This Function will receive the inputStream sent by the Directory Watcher endpoint \\demo001-edi-to-sql\Endpoints\processed-edi-docs-directory-watcher.
      • Line 14: InsertPurchaseOrder \ Invokes the SQL Service located at \\demo001-edi-to-sql\code\demo001\services_sql\InsertPurchaseOrder that allows you to execute SQL queries for manipulating the database it is connected to.

      When SaveToDatabase service is triggered, the inputStream will be mapped to XmlMethods.streamToGloopObject this Function will convert a Java InputStream to an object.

      The object will be then mapped to a model representation of the XML data from the EDI document that was processed previously called purchaseOrders.

      In the next step, parts of the XML representation of the EDI Document will be mapped to the inputs of sql.InsertPurchaseOrder. In this demo, we are going to get the shipment details of a purchase order, and save it to the database.

      When the service finishes executing this function, the shipment data for the purchase order received will be saved to the database.

      Martini Online users: Refer to the Martini Online section under Processing The EDI Document step above.

      Using Tracker to log each transaction

      Logging endpoint transactions are added to the services used for this demo by enabling the Log to Tracker checkbox in the Directory Watcher endpoint configuration in this package.

      These logs can be used for auditing what took place during the execution of the service triggered by the Directory Watcher endpoint and can be found in the Tracker UI.

      To access the Tracker UI, look for a magnifying glass icon in the menu bar on the top-right corner of the Martini Desktop UI.

      When enabling the Tracker logging for a Martini Endpoint, only the initial invocation, and service termination are being logged. In order to give us a better, more meaningful view of what took place during service execution, several functions were added to log every step of what’s happening when one of the Directory Watcher endpoints of this demo is triggered.

        • Tracker.addDocumentState - This function is used for adding States in a Tracker document.
        • Tracker.addDocumentLog - This function is used to add log messages to a Tracker document. This serves as additional information to the transaction being added in the Tracker document.
      Sample usage:
        • Open the ConsumeEdi service located in \\code\demo001\services\directory_watcher\.
        • In the ConsumeEdi service, you will see three Fork steps that check if the EDI Tracker document exists. Under these fork steps, there are Block steps added to group one service logic together.
        • These blocks contain the same service logic group, that is logging what took place during this service’s execution.
        • Take note that these blocks are only executed if the Log to Tracker option is enabled in the Directory Watcher endpoint configuration for this package. The input $trackerId will be the reference if the Tracker logging is enabled or not. If the Tracker logging is enabled, the Directory Watcher will supply the id of the Tracker document that was created for the current transaction.
        • In order to have a visualization of what took place during the service execution, these blocks are placed before and after the main functions described in the application flow for this service.