Wonderware MES – Middleware to Stored Procedure Mapping

Hidden deep inside an XML document in the installation of Wonderware MES, lies some documentation I had not found before.

I capture it here, in a more readable form, for future reference.

Original content from SE (If this ever gets meaninfully documented, I will take this down) :

Below are the rules to map a custom method to a custom stored procedure

 

  1. Mapping a custom method that returns a dataset from a stored procedure:
    1. Whenever there is a request for data retrieval, by default the .NET middleware prepends the string ‘SP_SA_’ before the <actual_table_name/object_name> to obtain the stored procedure name. ie., ‘SP_SA_<object> as the stored procedure name.For eg., an xml request to get all items ‘<request><object>item</object><msgtype>getall</msgtype>’ would use ‘SP_SA_ITEM’ as the stored procedure name in this case.
    2. Also, a custom stored procedure that follows the above naming convention need not be mapped. The custom request to retrieve records from the database using a stored procedure is prepended with the string ‘SP_SA_’ before the <actual_table_name/object_name>and followed by the command name to obtain the stored procedure name.  ie., SP_SA_<object>_<cmd> as the stored proceudure name.  For eg., a stored procedure name ‘SP_SA_ITEM_GETITEMS’ would have a typical xml request as ‘<request><object>item</object><msgtype>getspec</msgtype><cmd>GetItems</cmd>’.
    3. However, a stored procedure name that does not follow the above naming convention must be mapped. For eg., a method name ‘GetJobs’ could be mapped to a stored procedure ‘SP_GET_JOBS_FROM_MY_DB’. A typical xml request for this would be ‘<request><object>job</object><msgtype>getspec</msgtype><cmd>GetJobs</cmd>’
  2. Mapping a custom method that executes a stored procedure:
    1. UpdateSpecific methods (Updatespecifics()) on custom tables need not be mapped. As long as the supplied XML request to update custom tables conforms the Wonderware MES UpdateSpecific standards, then all columns from the custom table are automatically used for updates. See Wonderware MES Middleware user manual.doc for more details about UpdateSpecific method.
    2. A request to add a new data using a stored procedure prepends the string ‘SP_I_’ before the <actual_table_name/object_name> to obtain the stored procedure name.  ie., SP_I_<object> as the stored proceudure name. For eg., an xml request ‘<request><object>job_state</object><msgtype>exec</msgtype><cmd>add</cmd>’ to insert a job_state record would use ‘SP_I_JOB_STATE’ as the procedure name in this case.
    3. A request to update data using a stored procedure prepends the string ‘SP_U_’ before the <actual_table_name/object_name> to obtain the stored procedure name. ie., SP_U_<object> as the stored proceudure name. For eg., an xml request ‘<request><object>job_state</object><msgtype>exec</msgtype><cmd>update</cmd>’ to update a job_state record would use ‘SP_U_JOB_STATE’ as the procedure name in this case.
    4. A request to delete data using a stored procedure prepends the string ‘SP_D_’ before the <actual_table_name/object_name> to obtain the stored procedure name. ie., SP_D_<object> as the stored procedure name.  For eg., an xml request ‘<request><object>job_state</object><msgtype>exec</msgtype><cmd>delete</cmd>’ to delete a job_state record would use ‘SP_D_JOB_STATE’ as the procedure name in this case.
    5. All other custom requests prepends the string ‘SP_U_’ before the <actual_table_name/object_name> and followed by the command name to obtain the stored procedure name.  ie., SP_U_<object>_<cmd> as the stored proceudure name. For eg., an xml request ‘<request><object>job_state</object><msgtype>exec</msgtype><cmd>AddOrUpdate</cmd>’ to add or update a job_state record would use ‘SP_U_JOB_STATE_ADDORUPDATE’ as the procedure name in this case.
    6. However, a stored procedure name that does not follow the above naming convention must be mapped. For eg., a method name ‘SPLIT’ could be mapped to a stored procedure ‘SP_U_ITEM_INV_SPLIT’. A typical xml request for this would be ‘<request><object>storage_exec</object><msgtype>exec</msgtype><cmd>Split</cmd>’
  3. Besides the mapping rules specified above, a stored procedure is considered as a batch stored procedure, as long as the stored procedure contains an input parameter called as ‘xml_source’ OR ‘in_xml_source’.This puts the onus on a stored procedure to accept xml as input and subsequently call other stored procedures by parsing the supplied xml. As long as the stored procedure contains ‘in_xml_source’ or ‘xml_source’ as one of its input parameter, then the .net middleware pass the input xml as is to the stored procedure.”
About the author

Brent is Principal Technology Strategist at Stone Technologies where he works with clients to develop manufacturing technology strategies that help them meet their business objectives. He believes that a cohesive technology strategy is a fundamental way to enable information to flow to the people that need it, enabling decision making backed with accurate information. Manufacturing adds some unique requirements to IT, and as a consultant, solution architect, and solution implementer, Brent works with clients to bring the capabilities of IT to the plant floor as a means to enable the manufacturing experts to be aware of manufacturing performance, and have the necessary information to make informed operational decisions.

Leave a Reply

%d bloggers like this: