<dbselect>

Purpose The <dbselect> statement is used to execute SQL SELECT 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 <dbselect> statement creates a recordset that becomes associated with the SQL SELECT statement that created it. This recordset is used instead of a return visit to the database upon subsequent queries using the same SQL SELECT statement. This caching method (i.e. recordset persistence) is an useful feature in raising the efficiency of programs. The recordset is automatically converted by the <dbselect> statement into an XML tree. Since the <dbselect> statement exists as part of an existing XML document, it is a way of filling in the blanks in an XML document with data that came from a database. In this manner we are able to use relational data to drive XML modification. This is a relational to hierarchical (XML) data conversion. It is called node growth because of the growth or generation of the XML nodes from relational data.
Format <dbselect name="{sel name}" replicate="{repl bool}" connect="{conn name}">
   <docnode>{XML document node}</docnode>
   <recordnode>{XML record node}</recordnode>
   <fieldplace dbfield="{column name}" node="{item name}" type="{item type}" index="{item index}" />
   <parm type="{parm type}" name="{parm name}">
      {parm value}
   </parm>
   <sql>{sel stmt}</sql>
</dbselect>
{sel name} the name of the select object
{repl bool} true means get all the records from the recordset and convert them into the XML nodes; false means get only the first record
{conn name} the name of the connection object to be used for the execution of the SQL SELECT
{XML document node} the document node with attributes (XML attributes, Superx++ variables or arrays) to be created to wrap the records from the recordset-- optional in all nested <dbselect> statements
{XML record node} (optional) the record node with attributes (XML attributes, Superx++ variables or arrays) to be created to wrap each record from the recordset
{column name} (optional) the name of the column to be placed in an attribute, variable or array
{item name} (optional) the name of the entity (attribute, variable or array) that is to have column data placed in it
{item type} (optional) the datatype of the entity (must be either a variable or array) that is to have column data placed in it
{item index} (optional) the index of the entity (must be an array) that is to have column data placed in it
{parm type} (optional) the datatype of the parameter to be substituted into {sel stmt}
{parm name} (optional) the name of the parameter to be substituted into {sel stmt}
{parm value} (optional) the value of the parameter to be substituted into {sel stmt}
{sel stmt} the SQL SELECT statement to be executed on the database
Example #1 <node name="MySelect" processcode="true">
   <Cat>5</Cat>
   <Stuff>
      <dbselect name="Query1" replicate="true" connect="MyDbConn">
         <docnode><Emps style="cool" /></docnode>
         <recordnode><Emp status="current" /></recordnode>
         <fieldplace dbfield="Age" node="EmpName" type="attr" />
         <parm type="string" name="p1">Kim</parm>
         <sql><file>C:\\Kimmie\\CPP\\CalcTester\\test7a.sql</file></sql>
         <dbselect name="Query2" replicate="true" connect="MyDbConn">
            <docnode><Addresses /></docnode>
            <recordnode><Address status="liveable" arr_string_Street.1.="{OldStreet}" arr_string_City.1.="{OldTown}" /></recordnode>
            <fieldplace dbfield="Street" node="Address" type="string" index="1" />
            <fieldplace dbfield="City" node="Address" type="string" index="1" />
            <parm type="string" name="p1"><eval object="Query1/EmpName" /></parm>
            <sql>select Street, City from xppChildTbl where EmpName = p1;</sql>
         </dbselect>
         <dbselect name="Query3" docnode="Negatives" recordnode="Negative" replicate="true" connect="MyDbConn">
            <fieldplace dbfield="Street" node="Negative" type="string" />
            <fieldplace dbfield="City" node="Negative" type="string" />
            <parm type="string" name="p1"><eval object="Query1/EmpName" /></parm>
            <sql>select Street, City from xppChildTbl where EmpName <> p1;</sql>
         </dbselect>
         <dbselect name="Query4" docnode="OtherNegatives" recordnode="OtherNegative" replicate="true" connect="MyDbConn">
            <parm type="string" name="p1"><eval object="Query1/EmpName" /></parm>
            <sql>select Street, City from xppChildTbl where EmpName <> p1;</sql>
         </dbselect>
      </dbselect>
   </Stuff>
</node>

performs a set of SQL SELECT statements on a database via the connection MyDbConn that was created using the <dbconnect> statement. Notice how there are nested <dbselect> statements within the Query1 <dbselect> statement. These nested <dbselect> statements can refer to the record just selected in the parent <dbselect> statement. This is because they are executed every time a record is selected in the parent <dbselect> statement. They, in turn, can have children of their own. Thus many levels can be achieved of nesting to get the parent-child relationship that you need. The result of the above code snippet is that the MySelect node looks as follows:
<MySelect>
   <Cat>5</Cat>
   <Stuff>
      <Emps style="cool">
         <Emp status="current">
            <EmpName Age="57">Sue</EmpName>
            <Addresses>
               <Address status="liveable" arr_string_Street.2.="{OldStreet,Bagshot Row}" arr_string_City.2.="{OldTown,The Shire}"></Address>
            </Addresses>
            <Negatives>
               <Negative var_string_Street="40 MyStreet" var_string_City="MyTown"></Negative>
               <Negative var_string_Street="54 There" var_string_City="Wherever"></Negative>
               <Negative var_string_Street="Bag End" var_string_City="The Shire"></Negative>
            </Negatives>
            <OtherNegatives>
               <OtherNegative>
                  <Street>40 MyStreet</Street>
                  <City>MyTown</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>54 There</Street>
                  <City>Wherever</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bag End</Street>
                  <City>The Shire</City>
               </OtherNegative>
            </OtherNegatives>
         </Emp>
         <Emp status="current">
            <EmpName Age="56">Sam</EmpName>
            <Addresses></Addresses>
            <Negatives>
               <Negative var_string_Street="40 MyStreet" var_string_City="MyTown"></Negative>
               <Negative var_string_Street="54 There" var_string_City="Wherever"></Negative>
               <Negative var_string_Street="Bagshot Row" var_string_City="The Shire"></Negative>
               <Negative var_string_Street="Bag End" var_string_City="The Shire"></Negative>
            </Negatives>
            <OtherNegatives>
               <OtherNegative>
                  <Street>40 MyStreet</Street>
                  <City>MyTown</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>54 There</Street>
                  <City>Wherever</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bagshot Row</Street>
                  <City>The Shire</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bag End</Street>
                  <City>The Shire</City>
               </OtherNegative>
            </OtherNegatives>
         </Emp>
         <Emp status="current">
            <EmpName Age="45">Harry</EmpName>
            <Addresses>
               <Address status="liveable" arr_string_Street.2.="{OldStreet,40 MyStreet}" arr_string_City.2.="{OldTown,MyTown}"></Address>
               <Address status="liveable" arr_string_Street.2.="{OldStreet,54 There}" arr_string_City.2.="{OldTown,Wherever}"></Address>
            </Addresses>
            <Negatives>
               <Negative var_string_Street="Bagshot Row" var_string_City="The Shire"></Negative>
               <Negative var_string_Street="Bag End" var_string_City="The Shire"></Negative>
            </Negatives>
            <OtherNegatives>
               <OtherNegative>
                  <Street>Bagshot Row</Street>
                  <City>The Shire</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bag End</Street>
                  <City>The Shire</City>
               </OtherNegative>
            </OtherNegatives>
         </Emp>
         <Emp status="current">
            <EmpName Age="30">Sandr.ie</EmpName>
            <Addresses></Addresses>
            <Negatives>
               <Negative var_string_Street="40 MyStreet" var_string_City="MyTown"></Negative>
               <Negative var_string_Street="54 There" var_string_City="Wherever"></Negative>
               <Negative var_string_Street="Bagshot Row" var_string_City="The Shire"></Negative>
               <Negative var_string_Street="Bag End" var_string_City="The Shire"></Negative>
            </Negatives>
            <OtherNegatives>
               <OtherNegative>
                  <Street>40 MyStreet</Street>
                  <City>MyTown</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>54 There</Street>
                  <City>Wherever</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bagshot Row</Street>
                  <City>The Shire</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bag End</Street>
                  <City>The Shire</City>
               </OtherNegative>
            </OtherNegatives>
         </Emp>
         <Emp status="current">
            <EmpName Age="77">Dedan</EmpName>
            <Addresses></Addresses>
            <Negatives>
               <Negative var_string_Street="40 MyStreet" var_string_City="MyTown"></Negative>
               <Negative var_string_Street="54 There" var_string_City="Wherever"></Negative>
               <Negative var_string_Street="Bagshot Row" var_string_City="The Shire"></Negative>
               <Negative var_string_Street="Bag End" var_string_City="The Shire"></Negative>
            </Negatives>
            <OtherNegatives>
               <OtherNegative>
                  <Street>40 MyStreet</Street>
                  <City>MyTown</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>54 There</Street>
                  <City>Wherever</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bagshot Row</Street>
                  <City>The Shire</City>
               </OtherNegative>
               <OtherNegative>
                  <Street>Bag End</Street>
                  <City>The Shire</City>
               </OtherNegative>
            </OtherNegatives>
         </Emp>
      </Emps>
   </Stuff>
</MySelect>