Using parameterized queries to obtain data from your database

A parameterized query is more specific than a simple query. To parameterize a query, you must use variables in the SQL statement and then supply the actual parameters to fill in those variables. An understanding of how data is provided to a DataSet is essential to further understanding of parameterized queries, so read the topic Querying a database if you have not already done so. This topic is specific to parameterized queries.

A parameterized SQL statement contains variables, also known as parameters, the values of which can vary at run time. A parameterized query uses these variables to replace literal data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement.

Topics discussed here include:

Exposing parameterized queries
Supplying the parameters to parameterized queries
Binding parameters
Parameterized queries in master-detail relationships

Exposing parameterized queries

In JBuilder, you can expose a parameterized query in several ways:

Supplying the parameters to parameterized queries

You can supply a new ParameterRow object to the QueryDataSet using the changeParameters() method. You then call executeQuery() to cause the query to be executed again with new parameter values. For example, to use a UI control to set the value of a parameter, you can use a SQL statement such as:

	SELECT * FROM phonelist WHERE lastname LIKE ?

In this example, the "?" parameter's value could be supplied from a UI control. To do this, your code would have to

  1. obtain the value from the control each time it changed
  2. place it into the first column of a ParameterRow object
  3. supply that object to the QueryDataSet by calling changeParameters()
  4. call executeQuery() on the QueryDataSet

If you only need to provide the parameters once, or if you need to recreate the QueryDescriptor object for other reasons, you can create a ParameterRow object in your code. Then attach it to the parameterRow property of the QueryDescriptor object that you attach to the query property of the QueryDataSet. Note that you can obtain the current ParameterRow from the QueryDataSet by calling getParameterRow (read-only parameterRow property).

Binding parameters

Binding parameters means allocating resources for the statement and its parameters locally and on the server in order to improve execution of the query.

Binding parameters to a data set can be done by constructing a QueryDescriptor with a ParameterRow (containing values). In a parameterized query, parameters from the parameterRow are used to set the parameters of the query. There is a getParameterRow method on the QueryDataSet that you can use to obtain the current parameterRow. You can add an execute method that takes a ReadRow to set the parameters.

To bind the values of a parameter to a data set,

  1. Create a ParameterRow with column names explicitly defined for named parameters (such as :fld1, for example).

  2. Get values for the columns (from a visual control, for example).

  3. Put the values in the ParameterRow (may need a formatter here, depending on the data type).

  4. Execute the query (with executeQuery, for example).

There is a sample project, Param.jpr, available as a completed project in the samples/borland/samples/tutorial/dataset/Param directory of your JBuilder installation that demonstrates this method.

Binding may also be done by using the changeParameters method and invoking executeQuery to re-execute the query with the new value in the parameter passed to changeParameters.

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 col1 = :fld1

In this example, :fld1 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. See Binding parameters for more information on binding parameters.