Retrieve Data from a Database (XML)

You can use a business rule to perform data lookup, retrieving and incorporating information that was not available in the original print request. For example, you could compute a product number based on a serial number or retrieve an origin and other values based on a lot number.

Important! Content in a business rule is case sensitive. This includes business rule component names, attributes, and values.

The following examples use a lot number supplied with the print request to query a database table. Data returned from the query is used to populate the MAP fields /Body/origin and /Body/location, which are used by the shipping001 label template. The MTL_LOT_NUMBERS table in the database includes columns named PLACE_OF_ORIGIN, LOCATION, and LOT_NUMBER. This business rule can be run by a simple process.

Note: This business rule requires the existence of a JDBC data service that establishes a connection to a database. In the data service, Is Parameter is selected for the SQL Query. For more information, see Managing Data Services.

Retrieve data without merging into the data map

In this example, after this business rule is run, the data retrieved from the database is not available in the data map.

<?xml version="1.0" encoding="UTF-8"?>

<businessRules configuratorSupport="false">

   <businessRule>

 

      <dataService name="DATA_LOOKUP" namespace="Body" sticky="true" failOnNoRows="false" mergeRowToJobData="false">

 

         <!-- Triggered by ENQUEUE event. -->

         <triggers>

            <trigger eventTrigger="ENQUEUE" priority="0"/>

         </triggers>

 

         <!-- Query the database by using the lot number to find other fields. -->

         <parameters>

            <parameter parameterKey="query" resolveValue="true">

               <parameterValue>SELECT * FROM LW_QA.MTL_LOT_NUMBERS WHERE LOT_NUMBER=${/Body/lot_number}</parameterValue>

            </parameter>

         </parameters>

 

         <children>

            <mapOperations name="UPDATEMAP" namespace="Body">

 

               <!-- The mapOperations business rule component is triggered by the SET_MAP event, which occurs once for every row returned by the query. -->

               <triggers>

                  <trigger eventTrigger="SET_MAP" priority="0"/>

               </triggers>

 

               <!-- Set print attribute values to row data. -->

               <operations>

                  <operation action="OVERRIDE" dataType="STRING" literalValue="false" operation="PUT">

                     <!-- Name (key) for the origin -->

                     <targetKey>/Body/origin</targetKey>

                     <!-- Value for the origin returned from row in the database. @{} is row data. -->

                     <value>@{PLACE_OF_ORIGIN}</value>

                  </operation>

                  <operation action="OVERRIDE" dataType="STRING" literalValue="false" operation="PUT">

                     <!-- Key (name) for the location -->

                     <targetKey>/Body/location</targetKey>

                     <!-- Value for the location returned from the current row of the database. @{} is row data-->

                     <value>@{LOCATION}</value>

                  </operation>

               </operations>

 

            </mapOperations>

         </children>

 

         <!-- Path to a data service in Loftware Enterprise SP -->

         <dataServiceUrl>/Default/BRE/LPS Translation Data Service</dataServiceUrl>

 

         <!-- Create a custom event to occur once for each row returned by the database query. Mapping tables should only return one row of data for the query. If more are returned, only the last row returned is used. -->

         <perRowEvents>

            <rowEvent eventName="SET_MAP" includeSiblings="false"/>

         </perRowEvents>

 

      </dataService>

 

   </businessRule>

</businessRules>

Retrieve data and merge into the data map

In this example, after this business rule is run, the data retrieved from the database is available in the data map in /Body/dbData/{COLUMN_NAME}, which you can use as a data ref when designing label templates.

<?xml version="1.0" encoding="UTF-8"?>

<businessRules configuratorSupport="false">

   <businessRule>

 

      <dataService name="dbData" namespace="Body" sticky="true" failOnNoRows="false" mergeRowToJobData="true">

 

         <!-- Triggered by ENQUEUE event. -->

         <triggers>

            <trigger eventTrigger="ENQUEUE" priority="0"/>

         </triggers>

 

         <!-- Query the database by using the lot number to find other fields. -->

         <parameters>

            <parameter parameterKey="query" resolveValue="true">

               <parameterValue>SELECT * FROM LW_QA.MTL_LOT_NUMBERS WHERE LOT_NUMBER=${/Body/lot_number}</parameterValue>

            </parameter>

         </parameters>

 

         <!-- Path to a data service in Loftware Enterprise SP -->

         <dataServiceUrl>/Loftware Inc/BRE/dsvc1</dataServiceUrl>

 

      </dataService>

 

   </businessRule>

</businessRules>