Getting and altering the query string
Get the text of a JDBC query in any DataSet event handler, as shown in the following example:
query = this.queryText;
You can modify a query in the DataSet beforeOpen( ) event handler by setting the value of the queryText string. To change the query, set the queryText string to a valid SQL query, as shown in the following example:
queryText = "select * from CLASSICMODELS.CUSTOMERS
WHERE CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER BETWEEN " +
params[lowval].value + " AND " + params[highval].value;
One advantage of dynamically altering the query is that you can use business logic to determine the query to run. This approach can be more flexible than using parameters. Use this technique to alter other properties of the data source. BIRT also supports modifying data set properties using the property binding feature in the data source and data set editors. One difference between these methods is that property binding applies only to a running report. Scripts also function when previewing the data set.
Use a similar technique to change the query text field for a flat file data source. In the flat file data source, the query stores the name of the flat file from which to read as well as column information. Listing 37‑15 shows an example XML snippet for a flat file query.
Listing 37‑15 A query on a flat file data source
[CDATA[
select "Age", "Male", "Female"
from CSV1.csv :
{"Age","Age",STRING;"Male","Male",STRING;"Female","Female",STRING}
]]
In this example, CSV1 is the file to read. A beforeOpen dataset script can modify the file to read using a simple string replace method, as shown in the following code:
qry = this.queryText
this.queryText = qry.replace( "CSV1.csv", "CSV2.csv");
Some data sources do not use a queryText field. To change the query for those data sources, modify other data source properties.