<dbproc>

Purpose The <dbproc> statement is used to execute a stored procedure on a database via a created connection. This first implementation of the Superx++ run-time engine for Microsoft Windows uses an ODBC connection for this. The <dbproc> statement traverses a tree beginning at a specified node. Every time it reaches a node specified in the <at> clause, the code to invoke the stored procedure executes. In this manner we are able to use XML data to drive a relational data modification. This is an hierarchical (XML) to relational data conversion. It is called traversal dumping because of the traversal of the XML nodes which drives the process.
Format <dbproc name="{proc name}" connect="{conn name}">
   <src>
      {traversal object}
   </src>
   <at node="{exec node}">
      <proc>{stored proc}</proc>
      <parm type="{parm type}" name="{parm name}" direction="{parm direction}" scale="{parm scale}" precision="{parm precision}">
         {parm value}
      </parm>
      <onfail>
         {fail statements}
      </onfail>
      <onsuccess />
         {success statements}
      </onsuccess>
   </at>
</dbproc>
{proc name} the name of the procedure object
{conn name} the name of the connection object to be used for the invocation of the stored procedure
{traversal object} the node (XML, not the name) to be traversed and which will provide the triggers for the <at> clauses
{exec node} the node, which if found in the traversal, will trigger the execution of the code within the <at> clause
{stored proc} the name of the stored procedure in the database that is to be invoked
{parm type} (optional) the datatype of the parameter to be passed to the stored procedure as its argument
{parm name} (optional) the name of the parameter to be passed to the stored procedure as its argument
{parm direction} (optional) the direction of the parameter to be passed to the stored procedure as its argument-- in, out, inout or return are the valid values
{parm scale} (optional) the scale of the parameter to be passed to the stored procedure as its argument-- only use for numeric parameters
{parm precision} (optional) the precision of the parameter to be passed to the stored procedure as its argument-- only use for numeric parameters
{parm value} (optional) the value of the parameter to be passed to the stored procedure as its argument
{upd stmt} the SQL UPDATE statement to be executed on the database
{fail statements} the Superx++ statements to be executed if the database returned an error after the execution of the stored procedure
{success statements} the Superx++ statements to be executed if the database was successful in the execution of the stored procedure
Example #1 <var type="string" name="DbErr">No</var>
<node name="MyDbXML">
   <Emps>
      <Emp var_int_Age="30">
         <EmpName>Sandr.ie</EmpName>
         <Age>25</Age>
         <Addresses>
            <Address>
               <Street>The Nice Road</Street>
               <City>Niceville</City>
               <Duration>10</Duration>
            </Address>
            <Address>
               <Street>56 Hope Road</Street>
               <City>Kingston</City>
               <Duration>20</Duration>
            </Address>
         </Addresses>
      </Emp>
      <Emp var_int_Age="77">
         <EmpName>Dedan</EmpName>
         <Age>34</Age>
         <Address>
            <Street>1 Kijabe Street</Street>
            <City>Some Place</City>
            <Duration>30</Duration>
         </Address>
      </Emp>
   </Emps>
</node>

<dbproc name="MySQLObj" connect="MyDbConn">
   <src>
      <eval object="MyDbXML" />
   </src>
   <at node="Emp">
      <proc>MyInProc</proc>
      <parm type="string" name="a" direction="in"><eval object="EmpName" /></parm>
      <parm type="int" name="b" direction="in" scale="2" precision="10"><eval object="Age" /></parm>
      <onfail>
         <eval object="DbErr">Yes</eval>
         <xout><dberr connection="MyDbConn" /></xout>
      </onfail>
      <onsuccess>
         <xout>Success for Emp: </xout>
         <xout><eval object="EmpName" /></xout>
      </onsuccess>
   </at>
   <at node="Address">
      <proc>MyOutProc</proc>
      <parm type="string" name="a"><eval object="EmpName" /></parm>
      <parm type="string" name="b"><eval object="Street" /></parm>
      <parm type="string" name="c"><eval object="City" /></parm>
      <parm type="int" name="d" direction="out" scale="2" precision="10"><eval object="Duration" /></parm>
      <onfail>
         <eval object="DbErr">Yes</eval>
         <xout><dberr connection="MyDbConn" /></xout>
      </onfail>
      <onsuccess>
         <xout>Success invoking proc for Address for Emp: </xout>
         <xout><eval object="EmpName" /></xout>
      </onsuccess>
   </at>
</dbproc>

performs a set of stored procedure calls on a database via the connection MyDbConn that was created using the <dbconnect> statement. The tree of nodes starting with MyDbXML is traversed. Every time the node Emp or Address is reached, the stored procedure call is sent to the database for processing. If the execution was successful then the <onsuccess> clause executes; else the <onfail> clause executes. In the case of the procedure MyOutProc the fourth parameter is an out parameter. This means that the stored procedure will change the value of the parameter and that change will be reflected back on the Superx++ side in the item (object, attribute, variable or array element) associated with the parameter. In this case, the <Duration> node has its value changed. So the resulting MyDbXML node will look perhaps like this (depending on what the stored procedure does, of course):
<MyDbXML>
   <Emps>
      <Emp var_int_Age="30">
         <EmpName>Sandr.ie</EmpName>
         <Age>25</Age>
         <Addresses>
            <Address>
               <Street>The Nice Road</Street>
               <City>Niceville</City>
               <Duration>5000</Duration>
            </Address>
            <Address>
               <Street>56 Hope Road</Street>
               <City>Kingston</City>
               <Duration>6000</Duration>
            </Address>
         </Addresses>
      </Emp>
      <Emp var_int_Age="77">
         <EmpName>Dedan</EmpName>
         <Age>34</Age>
         <Address>
            <Street>1 Kijabe Street</Street>
            <City>Some Place</City>
            <Duration>7000</Duration>
         </Address>
      </Emp>
   </Emps>
</MyDbXML>