Specifying MES Stored Procedures in a MiddlewareAccess XML Endpoint

Introduction

The MiddlewareAccess XML Put endpoint allows you to add data by submitting an XML request that specifies a stored procedure.

The XML request specifies the stored procedure to call, along with any necessary parameters, using the <object> and <cmd> tags. There are two ways to determine how to specify the stored procedure using these two tags.

Determining the <object> and <cmd> Tags from Middleware Object Names

Middleware objects are mapped to MES database stored procedures in the fileMWDBMappings.xml. This file is stored in the installed MES application folder pathWonderware\MES\Middleware.

The middleware object names have two parts: the MES database table name and the action being called. For example, ENT.GETSHIFTSCHED, which uses the Ent table to get an entity's shift schedule records, is mapped to the stored procedure sp_SA_Ent_GetShiftSchedules.

In the XML request, the <object> tag specifies the MES database table name and the <cmd> tag specifies the object action.

For the GetAll and GetByKey message types, the <cmd> tag value is always getall or getbykey, respectively. Note that not all available GetAll and GetByKey calls are included as objects in the MWDBMappings.xml file.

For the GetSpecifiic message type, the <cmd> tag is the action portion of the middleware object that is being called. For example, for the ENT.GETSHIFTSCHED object, the <cmd> tag would be GetShiftSched.

Determining the <object> and <cmd> Tags for Stored Procedure Names

Some stored procedures do not have a middleware object mapped to them and so are not listed in the MWDBMappings.xml file. If the stored procedure you want to call is not in the MWDBMappings.xml file, then the <object> and <cmd> tags are determined as follows.

The Get stored procedure names are in the following formats:

• For GetAll: the standard stored procedure name is sp_SA_object, where object is the table name. For example, sp_SA_Ent gets all Ent table records.
• For GetByKey: the standard stored procedure name is sp_S_object, where object is the table name. For example, sp_S_Ent gets the Ent table record for the specified entity.
• For GetSpecific: the standard stored procedure name is sp_SA_object_cmd where object is the table name and cmd is the action to perform. For example, sp_SA_Util_Exec_GetPrvShftStrt gets the start time of the first utilization event that occurred during the previous shift, from the Util_Exec table.

GetAll XML String Format and Example

Syntax

Syntax structure example

<?xml version='2.0'?>

 <request>

    <object>MESDBTableName</object>

    <msgtype>getall</msgtype>

   <cmd>getall</cmd>

   <filter>

      <parameter1>value</parameter1>

      <parameter2>value</parameter2>

      <parameter3>value</parameter3>

       ...

   </filter>

</request>

where

• The <object> is the table name included in the name of the middleware object or stored procedure you are attempting to execute.
• The filter parameter names are based on the column names in the table.

Example

The following example shows the XML string to get the Ent table records for all entities whose parent entity has an ID of 2 and that can schedule jobs.

GetAll Entities example

<?xml version='2.0'?>

<request>

   <object>ent</object>

   <msgtype>getall</msgtype>

    <cmd>getall</cmd>

   <filter>

      <parent_ent_id>2</parent_ent_id>

      <can_sched_jobs>1</can_sched_jobs>

   </filter>

</request>

GetByKey XML String Format and Example

Syntax

Syntax structure example

<?xml version='2.0'?>

 <request>

    <object>MESDBTableName</object>

    <msgtype>getbykey</msgtype>

   <cmd>getbykey</cmd>

   <MESDBTableName>

      <parameter1>value</parameter1>

      <parameter2>value</parameter2>

      <parameter3>value</parameter3>

       ...

   </MESDBTableName>

</request>

where

• <object> is the table name included in the name of the middleware object or stored procedure you are attempting to execute.
• The filter parameter names are based on the column names in the table.

Example

The following example shows the XML string to get the Ent table for the entity with an ID of 5.

GetByKey Entities example

<?xml version='2.0'?>

<request>

   <object>ent</object>

   <msgtype>getbykey</msgtype>

   <cmd>getbykey</cmd>

   <ent>

      <ent_id>5</ent_id>

   </ent>

</request>

GetSpecific XML String Format and Example

Use the GetSpecific (GetSpec message type) XML string format to call any Gets other than GetAll and GetByKey.

Syntax

Syntax structure

<?xml version='2.0'?>

<request>

   <object>MESDBTableName</object>

   <msgtype>getspec</msgtype>

   <cmd>commandname</cmd>

      <parameter1>value</parameter1>

      <parameter2>value</parameter2>

      <parameter3>value</parameter3>

       ...

</request>

where

• <object> is the table name included in the name of the middleware object or stored procedure you are attempting to execute.
• <cmd> is the Get action included in the name of the middleware object or stored procedure. For example, GetShiftSchedules would be the command entry when trying to execute the sp_SA_ent_GetShiftSchedules stored procedure.
• The filter parameter names are based on the column names in the table.

Example

The following example shows the XML string to get the sales order line item records for purchase order 90125 for Wander Industries.

GetSpecific Sales Order Line Items example

<?xml version='2.0'?>

<request>

   <object>so_line</object>

   <msgtype>getspec</msgtype>

   <cmd>GetByFilter</cmd>

      <user_id>johnsmith</user_id>

      <cust_id>Wander Industries</cust_id>

      <po_id>90125</po_id>

</request>