Constructs a query of an SQL database and renders its output parameter once for each row returned by the database query.

Class Name




Required Input Parameters

You must specify one of the following sets of input parameters:


A component of class atg.service.util.SQLQuery that encapsulates the query and data source. For example:

<dsp:param name="queryBean" bean="/mySite/MySQLQuery"/>


The SQL statement used to query the database. For example:

<dsp:param name="querySQL" value="select * from person
  where age=:myAge(INTEGER)"/>

When you specify an SQL parameter that should not be treated as a string or VARCHAR, specify the parameter’s SQL data type in parentheses, following the parameter name, as in the previous example.

If you specify the querySQL parameter, you must also supply either the connectionURL parameter or the dataSource parameter.


Identifies the database to query. For example:

<dsp:param name="connectionURL" value="jdbc:atgpool:ConnectionPool"/>


Identifies the Java Transaction DataSource to query. For example:

<dsp:param name="dataSource"

If you specify the dataSource parameter, you must also supply the transactionManager parameter.


Identifies the Transaction Manager for the DataSource. For example:

<dsp:param name="transactionManager"

Optional Input Parameters


A string that specifies how to sort the list of items in the output array. Sorting can be performed on properties of JavaBeans, Dynamic Beans, or on Dates, Numbers, or Strings.

To sort JavaBeans, specify the value of sortProperties as a comma-separated list of property names. The first name specifies the primary sort, the second specifies the secondary sort, and so on. If the first character of each keyword is a +, this sort is performed in ascending order. If it has a , it is performed in descending order.

The following example specifies to sort an output array of JavaBeans alphabetically by title property and in descending order of the size property:

<dsp:param name="sortProperties" value="+title,size"/>

To sort Dates, Numbers, or Strings, prepend the sortProperties value with a plus + or minus sign to specify ascending or descending sort order. The following example sorts an output array of Strings in alphabetical order:

<dsp:param name="sortProperties" value="+"/>

When the items to sort are stored in a Map or Dictionary, use the following syntax:


where name is the key or Dictionary name.

For example:

<dsp:param name="sortProperties" value="_id"/>

If this example involves a Map property, id is a key of the Map indicated in the array attribute. All IDs associated with the id key are returned in descending order.

A nested servlet bean inherits the parent servlet bean’s sortProperties setting, unless the nested servlet bean has its own sortProperties setting. For example, the following setting negates any parent sortProperties setting:

<dsp:param name="sortProperties" value=""/>

Output Parameters


The zero-based index of the returned row.


The one-based number of the returned row.


A dynamic bean that has properties for accessing the values returned in the result set. You can access the values either by name or by column index. The name refers to either the column name or the column alias if one is used in the query. To access values by name, use To access values by column index, use element.column[i] where i is the zero-based index of the returned column.

Open Parameters

The following open parameters control the formatting for the returned results:


Rendered before any output tags if the array is not empty.


Rendered after all output tags if the array is not empty.


Rendered once for each array element.


Rendered if the array contains no elements.


Rendered if there is an error when the query executes. Errors are accessed by this parameter as SQLException objects.

Usage Notes

SQLQueryForEach executes an SQL query and renders its output open parameter once for each element returned by the query. You can specify a query in several ways:

You might prefer to use the servlet bean RQLQueryForEach, as it provides enhanced performance and scalability by using ATG’s caching capabilities.


The following example displays the number of votes each mountain received. This is done by counting the number of rows in the MOUNTAINVOTE table where the mountain column is the same as the parameter.

<dsp:droplet name="/atg/dynamo/droplet/SQLQueryForEach">
  <dsp:param name="dataSource" bean="/atg/dynamo/service/jdbc/JTDataSource"/>
  <dsp:param name="transactionManager"
  <dsp:param name="querySQL"
   value="select count(*) from MOUNTAINVOTE where"/>

  <dsp:oparam name="output">
    <!-- number="#" prevents us from seeing the decimal of count since
         some DB's return floating point from the query -->
    <dsp:valueof param="element.column[0]" number="#"></dsp:valueof>

Copyright © 1997, 2013 Oracle and/or its affiliates. All rights reserved. Legal Notices