A parameterized SQL statement contains variables, also known as parameters, the values of which can vary at runtime. A parameterized query uses these variables to replace literal data values, such as those used in a WHERE clause for comparisons, that appear in a SQL statement. These variables are called parameters. Ordinarily, parameters stand in for data values passed to the statement. You provide the values for the parameters before running the query. By providing different sets of values and running the query for each set, you cause one query to return different data sets.
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.
In addition to the "Tutorial: Parameterizing a query", the following parameterized query topics are discussed:
"Using parameters"
"Re-executing the parameterized query with new parameters"
"Binding parameters"
"Parameterized queries in master-detail relationships"
The following tutorial shows how to provide data to an application using a QueryDataSet component. This example adds a ParameterRow with low and high values that can be changed at runtime. When the values in the ParameterRow are changed, the grid will automatically refresh its display to reflect only the records that meet the criteria specified with the parameters.
A sample project ParamQuery.jpr, located in the samples\borland\samples\tutorial\dataset\ParamQuery directory of your JBuilder installation, contains the completed tutorial.
To create the application,
Select File|Close All. Select File|New. Double-click Application to start the Application Wizard and create a new application. Accept all defaults.
To do this, select the Frame file in the Navigation pane. Select the Design tab in the UI Designer. Add a Database component from the Data Express tab to the Component tree. Open the connection property editor for the Database component by selecting the connection property ellipsis in the Inspector. Set the connection properties as follows:
Property name | Value |
Connection URL | jdbc:odbc:DataSet Tutorial |
Username | SYSDBA |
Password | masterkey |
Click the Test Connection button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the gray area below the button. When the gray area beneath the button indicates Success, click OK to close the dialog.
The following code is generated in the class definition:
ParameterRow parameterRow1 = new ParameterRow();
Next, you will add two columns to the ParameterRow, low_no and high_no. After binding the ParameterRow to a QueryDataSet, a TextField components is used to change the value in the ParameterRow so that the query can be refreshed using these new values.
Property name | Value |
columnName | low_no |
dataType | INT |
default | 15 |
The following code is generated in the class definition:
Column column1 = new Column();
The following code is generated in jbInit():
column1.setColumnName("low_no"); column1.setDataType(borland.jbcl.util.Variant.INT); column1.setDefault("15"); parameterRow1.setColumns(new Column[] {column1});
Property name | Value |
columnName | high_no |
dataType | INT |
default | 50 |
The following code is generated in the class definition:
Column column2 = new Column();
The following code is generated in jbInit():
column2.setColumnName("high_no"); column2.setDataType(borland.jbcl.util.Variant.INT); column2.setDefault("50");
The column definition for the parameterRow object is modified as follows:
parameterRow1.setColumns(new Column[] {column1, column2});
Property name | Value |
Database | database1 |
SQL Statement | select * from employee where emp_no >= :low_no and emp_no <= :high_no |
Place SQL Text In Resource Bundle | uncheck |
On the Parameters page of the query dialog. Select parameterRow1 in the drop-down list box to bind the data set to the ParameterRow.
On the Query page, click the Test Query button to ensure that the query is runnable. When the gray area beneath the button indicates Success, click OK to close the dialog.
The following code for the queryDescriptor is added to jbInit():
queryDataSet1.setQuery(new borland.sql.dataset.QueryDescriptor(database1, "select * from employee where emp_no <= :low_no and emp_no >= :high_no", parameterRow1, true, Load.ALL));
To view and manipulate the data in your application,
//void textField1_keyPressed(KeyEvent e) { //when the Enter key is pressed if (e.getKeyCode() == KeyEvent.VK_ENTER) { try { //change the value in the parameter row and refresh the display parameterRow1.setInt("low_no", Integer.parseInt(textField1.getText())); queryDataSet1.refresh(); } catch (Exception ex) { ex.printStackTrace(); } } // }
The following code is automatically entered in jbInit():
textField1.addKeyListener(new java.awt.event.KeyAdapter() {A new class with the same name is added to the Frame file as well.
You can add a LabelControl to identify this field as the minimum field.
//void textField2_keyPressed(KeyEvent e) { //when the Enter key is pressed if (e.getKeyCode() == KeyEvent.VK_ENTER) { try { //change the value in the parameter row and refresh the display parameterRow1.setInt("high_no", Integer.parseInt(textField2.getText())); queryDataSet1.refresh(); } catch (Exception ex) { ex.printStackTrace(); } } //}
The following code is automatically entered in jbInit():
textField2.addKeyListener(new java.awt.event.KeyAdapter() {A new class with the same name is added to the Frame file as well.
You can add a LabelControl to identify this field as the maximum field.
To save changes back to the data source, click the Save Changes button on the Navigator.
To test the example, enter a new value in the TextField that holds the minimum value, then press Enter. The grid displays only those values above the new minimum value. Enter a new value in the TextField that holds the maximum value, then press Enter. The grid displays only those values below the new maximum value. To enter new values for both the minimum and maximum fields, enter one, then press Tab to move to the other field. Enter the new value in the other field, then press Enter to refresh the display for both new values.
To assign parameter values in a parameterized query, you must first create a ParameterRow and add named columns that will be the placeholders for the values to be passed to the query.
ParameterRow is a class that can be instantiated. It contains one row of storage, and is primarily used for parameterized queries and procedures. Unlike DataRow, it gets its columns from add/setColumns() methods. The reason that DataRow cannot be used for query/procedure parameters is that there may be multiple parameters for the same column. For example, if there is a WHERE clause that compares a column value to a start and end parameter, two parameter columns are needed for one data column.
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 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.
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 employee where emp_no > :low_no and emp_no < :high_no
In this SQL statement, :low_no and :high_no are parameter markers that are placeholders for actual values supplied to the statement at runtime by your application. The value in this field may come from a visual control or be generated programmatically. When parameters are assigned a name, they can be passed to the query in any order. JBuilder will bind the parameters to the data set in the proper order at runtime.
In the "Tutorial: Parameterizing a query", two columns are added to the ParameterRow 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 ? JDBC parameters markers:
SELECT * FROM employee WHERE emp_no > ?
In this SQL statement, the "?" value is a placeholder for an actual value supplied to the statement at runtime by your application. The value in this field may come from a visual control or be generated programmatically. When a ? JDBC parameter marker is used, values are passed to the query in a strictly left to right order. JBuilder will bind the parameters to the source of the values (a ReadWriteRow) in this order at runtime. 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".
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 control 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 control. To do this, your code would have to:
See "Tutorial: Parameterizing a query" for an example of how to do this with JBuilder sample files.
Binding parameters means allocating resources for the statement and its parameters both 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,
An example of binding parameters is included in "Tutorial: Parameterizing a query".
Binding may also be done by invoking refresh() to re-execute the query with the new value in the parameter as discussed in "Re-executing the parameterized query with new parameters".
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. See "Binding parameters" for more information on binding parameters.