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
In JBuilder, you can expose a parameterized query in several ways:
When the parameter markers in the query are specified with a colon followed by an alphanumeric name, parameter name matching will be done. The column in the ParameterRow that has the same name as a parameter marker will be used to set the parameter value. For example, in the following SQL statement, values to select are passed as named parameters:
SELECT * FROM phonelist WHERE lastname LIKE :lastname
In this SQL statement, :lastname is a placeholder for an actual value supplied to the statement at run time by your application. The value in this field may come from a visual control or be generated programmatically. When a parameter is assigned a name, it can be passed to the query in any order. JBuilder will bind the parameters to the data set in the proper order at run time. See Binding parameters for more information on binding parameters.
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. In this example, two columns are added to the existing dataSet to hold minimum and maximum values. The query descriptor specifies that the query should return only values greater than the minimum value and less than the maximum value.
When the simple question mark JDBC parameter markers are used, parameter value settings are ordered strictly from left to right.
For example, in the following SQL statement, values to select are passed as parameters markers:
SELECT * FROM phonelist WHERE lastname LIKE ?
In this SQL statement, the "?" value is a placeholder for an actual value supplied to the statement at run time by your application. The value in this field may come from a visual control or be generated programmatically. When a parameter marker is used, values are passed to the data set in a strictly left to right order. JBuilder will bind the parameters to the data set in this order at run time. See Binding parameters for more information on binding parameters.
Master and detail data sets have at least one field in common, by definition. This field is used as a parameterized query. For more detail on supplying parameters in this way, see Parameterized queries in master-detail relationships.
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
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 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,
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.
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.