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.”

Leave a Reply