<dbinsert>

Purpose The <dbinsert> statement is used to execute SQL INSERT statements 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 <dbinsert> statement traverses a tree beginning at a specified node. Every time it reaches a node specified in the <at> clause, the code to perform the SQL INSERT 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 <dbinsert name="{ins name}" connect="{conn name}">
   <src>
      {traversal object}
   </src>
   <at node="{exec node}">
      <parm type="{parm type}" name="{parm name}">
         {parm value}
      </parm>
      <sql>
         {ins stmt}
      </sql>
      <onfail>
         {fail statements}
      </onfail>
      <onsuccess />
         {success statements}
      </onsuccess>
   </at>
</dbinsert>

{ins name} the name of the insert object
{conn name} the name of the connection object to be used for the execution of the SQL INSERT
{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
{parm type} (optional) the datatype of the parameter to be substituted into {ins stmt}
{parm name} (optional) the name of the parameter to be substituted into {ins stmt}
{parm value} (optional) the value of the parameter to be substituted into {ins stmt}
{ins stmt} the SQL INSERT 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 {ins stmt}
{success statements} the Superx++ statements to be executed if the database was successful in the execution of {ins stmt}
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>

<dbinsert name="MySQLObj" connect="MyDbConn">
   <src>
      <eval object="MyDbXML" />
   </src>
   <at node="Emp">
      <parm type="string" name="a"><eval object="EmpName" /></parm>
      <parm type="int" name="b"><eval object="Age" /></parm>
      <sql>
         insert into xppTbl (EmpName, Age)
         values (a, b);
      </sql>
      <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">
      <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>
      <sql>
         insert into xppChildTbl (EmpName, Street, City)
         values (a, b, c);
      </sql>
      <onfail>
         <eval object="DbErr">Yes</eval>
         <xout><dberr connection="MyDbConn" /></xout>
      </onfail>
      <onsuccess>
         <xout>Success inserting Address for Emp: </xout>
         <xout><eval object="EmpName" /></xout>
      </onsuccess>
   </at>
</dbinsert>

performs a set of SQL INSERT statements 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 INSERT statement is sent to the database for processing. If the execution was successful then the <onsuccess> clause executes; else the <onfail> clause executes.