<tsx:dbquery>

Use the <tsx:dbquery> syntax to establish a connection to a database, submit database queries, and return the results set.

The <tsx:dbquery> tag:

• Refers to a <tsx:dbconnect> in the same JSP file and uses the information it provides to determine the database URL and driver. The user ID and password are also obtained from the <tsx:dbconnect> if those values are provided in the <tsx:dbconnect>.
• Establishes a new connection.
• Retrieves and caches data in the results object.
• Closes the connection (releases the connection resource).

The <tsx:dbquery> syntax is:

<tsx:dbquery id="query_id" connection="connection_id" limit="value" >

  <%-- SELECT commands and (optional) JSP syntax can be --%>
  <%-- placed within the tsx:dbquery tag. Any other     --%>
  <%-- syntax, including HTML comments, are not valid.  --%>

</tsx:dbquery>

The following list describes the attributes and their values:

• id
  The identifier of this query. The scope is the JSP file. This identifier is used to reference the query, for example, from the <tsx:getProperty> tag to display query results.

  The id is a tsx reference to the bean and can be used to retrieve the bean from the page context. For example, if id is named mySingleDBBean, replace this:

  if (mySingleDBBean.getValue("UISEAM",0).startsWith("N"))

  with this:

  com.ibm.ws.webcontainer.jsp.tsx.db.QueryResults bean = 
    (com.ibm.ws.webcontainer.jsp.tsx.db.QueryResults)pageContext. findAttribute("mySingleDBBean"); 
    if (bean.getValue("UISEAM",0).startsWith("N")). . . 

  The bean properties are dynamic and the property names are the names of the columns in the results set. If you want different column names, use the SQL keyword for specifying an alias on the SELECT command. In the following example, the database table contains columns named FNAME and LNAME, but the SELECT statement uses the AS keyword to map those column names to FirstName and LastName in the results set:

  Select FNAME, LNAME AS FirstName, LastName from Employee where FNAME='Jim'

• connection
  The identifier of a <tsx:dbconnect> in this JSP file. That <tsx:dbconnect> provides the database URL, driver name, and (optionally) the user ID and password for the connection.

• limit
  An optional attribute that constrains the maximum number of records returned by a query. If the attribute is not specified, no limit is used and the effective limit is determined by the number of records and the system caching capability.

• SELECT command and JSP syntax
  Because the <tsx:dbquery> must return a results set, the only valid SQL command is SELECT. For more information about the SELECT command, see this resource:

  • DB2 Universal Database for iSeries SQL Reference (V5R1)
  • DB2 Universal Database for iSeries SQL Reference (V5R2)

In the following example, a database is queried for data about employees in a specified department. The department is specified using the <tsx:getProperty> to embed a variable data field. The value of that field is based on user input.

<% String   workdept = request.getParameter("WORKDEPT"); %>

<tsx:dbquery id="qs2" connection="conn" >
   select * from WSDEMO.EMPLOYEE  where  WORKDEPT= '<%=workdept%>'
</tsx:dbquery>

Displaying query results

To display the query results, use the <tsx:repeat> and <tsx:getProperty> syntax. The <tsx:repeat> loops through each of the rows in the query results. The <tsx:getProperty> uses the query results object (for the <tsx:dbquery> syntax whose identifier is specified by the <tsx:getProperty> bean attribute) and the appropriate column name (specified by the <tsx:getProperty> property attribute) to retrieve the value. For example:

<tsx:repeat>
<tr>
<td>
  <tsx:getProperty name="empqs" property="EMPNO" />
  <tsx:getProperty name="empqs" property="FIRSTNME" />
  <tsx:getProperty name="empqs" property="WORKDEPT" />
  <tsx:getProperty name="empqs" property="EDLEVEL" />
</td>
</tr>
</tsx:repeat>

The JSP10employeeRepeatResults.jsp example illustrates the syntax of the <tsx:getProperty> tag.