DbAction-Query

Here's a simple query that requires only one dynamic parameter value

<dxp:DbAction value="query" dbcon="demo" store="bids">
 <dxp:SqlStmt name="stmt">
  <dxp:Cp name="sql">select id, email, item, biddate, amount from auction where item='?'</dxp:Cp>
  <dxp:SqlParam value="${item}" default="Eternal Battery" type="CHAR"/>
 </dxp:SqlStmt>
</dxp:DbAction>

The SqlStmt tag supplies a complete sql stmt to the DbAction tag to run. The Cp tag supplies an sql template with a ? mark as a placholder for the runtime value. The SqlParam tag supplies the runtime value or substitutes a default if the runtime value is not present. Your sql template can have multiple placholders and multiple SqlParams to supply them. The query result is stored as a String[][] matrix under the value of the store attribute which in this case is "bids".

DbAction provides many different actions including

• query - return a set of records formatted as String[][] matrix
• getValue - return a single object formatted as a String
• getPosValue - used to check for int values like a valid ID
• getFirstRow - used to get a single record
• getElemValues - store matrix elements as key[row][col]
• update - run insert or update stmts
• autoid - get last auto increment value
• queryObjects - return ObjectRecordSet calss
• getObject - return raw Object not formatted as String

Display records if found

<dxp:If test="${bids} != null">
 <table border="1">
 <tr bgcolor="#CCCCFF">
  <th>UserID</th><th>Email</th><th>Item</th>
  <th>Bid Date</th><th>Amount</th>
 </tr>
 <dxp:LoopMatrix value="${bids}" cols="0,1,2,3,4">  <!-- cols specified by index -->
 <tr>
  <td>${0}</td><td>${1}</td><td>${2}</td>
  <td align="right">${3}</td><td align="right">${4}</td>
 </tr>
 </dxp:LoopMatrix>
 </table>
</dxp:If>
<dxp:Else>
 <h4>No bids for ${item}</h4>
</dxp:Else>

If you don't need runtime values you can supply a constant stmt using the dxp:Cp tag.

<dxp:DbAction value="qetFirstRow" dbcon="demo" store="record">
 <dxp:Cp name="stmt">select id, email, item, biddate, amount from auction where id=4</dxp:Cp>
</dxp:DbAction>

Now we'll load the template file and replace tokens with values from the record we found above.

<dxp:ReplaceChar token="$" value="${record}"> <!-- load template file and insert record -->
 <dxp:ReadText name="target" filename="dxp/sample/auction-form.txt"/>
</dxp:ReplaceChar>

The file looks like this

<form>
<table border="0" cellpadding="4">
<tr><td colspan="2" bgcolor="#ffcccc">Your Last Bid</td></tr>
<tr><td>UserID:</td><td>$</td></tr>
<tr><td>Email:</td><td>$</td></tr>
<tr><td>Item:</td><td>$</td></tr>
<tr><td>Bid date:</td><td>$</td></tr>
<tr><td>Amount:</td><td><input type="text" name="amount" value="$" size="16"/></td></tr>
<tr><td>New Bid:</td><td><input value="Submit" type="submit"/></td></tr>
</table>
</form>

Here is the result

If you want to store individual elements of a record matrix you can do this

<dxp:DbAction value="getElemValues" dbcon="demo" store="elem">
 <dxp:Cp name="stmt">select id, email, item from auction where id=4</dxp:Cp>
</dxp:DbAction>
<ul>
<li>${elem[0][0]}</li>
<li>${elem[0][1]}</li>
<li>${elem[0][2]}</li>
<li>rowcount: ${elem.rowcount}</li>
<li>colcount: ${elem.colcount}</li>
</ul>

Element values

• 4
• johndoe@msn.not
• Eternal Battery
• rowcount: 1
• colcount: 3

You can also query for a single value. In the stmt below we'll find the max bid and store it.
We'll supply a value to the "where" clause using an element value we found above.

<dxp:DbAction value="getValue" dbcon="demo" store="maxbid">
 <dxp:SqlStmt name="sql"> 
  <dxp:Cp name="sql">select max(amount) from auction where item='?'</dxp:Cp>
  <dxp:SqlParam value="${elem[0][2]}" type="CHAR"/>
 </dxp:SqlStmt>
</dxp:DbAction>

<p><b>Max bid for ${elem[0][2]}: ${maxbid}</b></p>

Max bid for Eternal Battery: 100.00

Footnote

DbAction will only accept a stmt from SqlStmt, SqlStmtArray or Cp tags that have a name="stmt" attribute. The Cp tag supplies a const stmt with no runtime values. The SqlStmt and SqlStmtArray process runtime values using SqlParam, SqlFragment and SqlParamArray children which attempt to convert input values into an appropriate format and protect the integrity of the stmt.