Parameterized queries: Hints & tips

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

This set of topics includes tips to help you

Using parameters

To assign parameter values in a parameterized query, you must first create a ParameterRow and add named columns that will hold the values to be passed to the query.

Any ReadWriteRow, such as ParameterRow, DataSet, and DataRow may be used as query or procedure parameters. In a ParameterRow, columns can simply be set up with the addColumns and setColumns methods. DataSet and DataRow should only be used if they already contain the columns with the wanted data.

The Row classes are used extensively in the DataExpress APIs. The ReadRow and ReadWriteRow are used much like interfaces that indicate the usage intent. By using a class hierarchy, implementation is shared, and there is a slight performance advantage over using interfaces.

The class hierarchy associated with the DataSet methods is as follows:

java.lang.Object
   +----com.borland.dx.dataset.ReadRow
           +----com.borland.dx.dataset.ReadWriteRow
                   +----com.borland.dx.dataset.DataSet
                           +----com.borland.dx.dataset.StorageDataSet
                                   +----com.borland.dx.sql.dataset.QueryDataSet

The Row classes provide access to column values by ordinal and column name. Specifying columns by name is a more robust and readable way to write your code. Accessing columns by name is not quite as quick as by ordinal, but it is still quite fast if the number of columns in your DataSet is less than twenty, due to some patented high-speed name/ordinal matching algorithms. It is also a good practice to use the same strings for all access to the same column. This saves memory and is easier to enter if there are many references to the same column.

The ParameterRow is passed in the QueryDescriptor. The query property editor allows you to select a parameter row. Editing of ParameterRow, such as adding a column and changing its properties, can be done in the Inspector or in code.

For example, you create a ParameterRow with two fields, low_no and high_no. You can refer to low_no and high_no in your parameterized query, and compare them to any field in the table. See the examples below for how to use these values in different ways.

In JBuilder, parameterized queries can be run with named parameters, with parameter markers, or with a master-detail relationship. The following sections give a brief explanation of each.

Re-executing the parameterized query with new parameters

To re-execute the query with new parameters, set new values in the ParameterRow and then call QueryDataSet.refresh() to cause the query to be executed again with new parameter values. For example, to use a UI component to set the value of a parameter, you can use a SQL statement such as:

	SELECT * FROM phonelist WHERE lastname LIKE :searchname

In this example, the :searchname parameter's value could be supplied from a UI component. To do this, your code would have to:

  1. obtain the value from the component each time it changes
  2. place it into the ParameterRow object
  3. supply that object to the QueryDataSet
  4. call refresh() on the QueryDataSet

See "Tutorial: Parameterizing a query" for an example of how to do this with JBuilder sample files.

If the values you want to assign to the query parameter exist in a column of a data set, you can use that data set as your ReadWriteRow in the QueryDescriptor, navigate through the data set, and rerun the query for each value.

Parameterized queries in master-detail relationships

In a master-detail relationship with DelayedDetailFetch set to true (to fetch details when needed), you can specify a SQL statement such as:

SELECT * FROM employee WHERE country = :job_country

In this example, :job_country would be the field that this detail data set is using to link to a master data set. You can specify as many parameters and master link fields as is necessary. In a master-detail relationship, the parameter must always be assigned a name that matches the name of the column. For more information about master-detail relationships and the DelayedDetailFetch parameter, see "Establishing a master-detail relationship".

In a master-detail descriptor, binding is done implicitly. Implicit binding means that the data values are not actually supplied by the programmer, they are retrieved from the master row and implicitly bound when the detail query is executed. Binding parameters means allocating resources for the statement and its parameters both locally and on the server in order to improve performance when a query is executed.

If the values you want to assign to the query parameter exist in a column of a data set (the master data set), you can use that data set as your ReadWriteRow in the QueryDescriptor, navigate through the data set, and rerun the query for each value to display in the detail data set.