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
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.
With named parameters:
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 run time by your application. The value in this field may come from a visual component or be generated programmatically. In design time, the column's default value will be used. 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 run time.
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.
With ? JDBC parameter markers:
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 run time by your application. The value in this field may come from a visual component 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 run time. 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.
With a master-detail relationship:
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 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:
ParameterRow
object
QueryDataSet
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.
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.