Querying a database

In JBuilder, data is extracted from a server into a data set. This action is called "providing". Once the data is provided, you can view and work with the data locally in data-aware controls. When you want to save the changes back to your database, you must resolve the data. This process is discussed in more detail in JBuilder data access.

QueryDataSet components enable you to use SQL statements to access, or provide, data from your database. To query a SQL table, you need a Database component, a QueryDataSet component, and a QueryDescriptor object. You can provide this information programmatically, or by using JBuilder design tools. The Database component encapsulates a database connection through JDBC to the SQL server and also provides lightweight transaction support. A QueryDataSet component provides the functionality to run a query statement (with or without parameters) against tables in a SQL database and stores the result set from the execution of the query. The QueryDescriptor object stores the query properties, including the database to be queried, the query string to execute, and optional query parameters.

When providing data from JDBC data sources, the QueryDataSet has built-in functionality to fetch data. The following properties of the QueryDescriptor object affect query execution:
PropertyEffect

database Specifies what Database connection object to run the query against.
query A Java String representation of a SQL statement (typically a select statement).
parameterRow An optional row from which to fill in parameters, used for parameterized queries.
executeOnOpen Causes the QueryDataSet to execute the query when it is first opened. This is useful for presenting live data at design time. You may also want this enabled at run time.
asynchronousExecution Causes the fetching of DataSet rows to be performed on a separate thread. This allows the DataSet data to be accessed and displayed as the QueryDataSet is fetching rows from the database connection.

If asynchronousExecution is true, be careful not to perform operations that assume the entire data set is available until the query completes.

A QueryDataSet can be used in three different ways to fetch data.

  1. Unparameterized queries. This topic covers unparameterized queries, where the query is executed and rows are fetched into the QueryDataSet.
  2. Parameterized queries. In a parameterized query, you use variables in the SQL statement and then supply the actual parameters to fill in those values. For more information on parameterized queries, see Using parameterized queries to obtain data from your database.
  3. Dynamic fetching of detail groups. When dynamic fetching of all detail groups is used, records from a detail data set are fetched on demand and stored in the detail data set. This topic is covered in Establishing a master-detail relationship: fetching details.

These are the topics covered:

Tutorial: Querying a database using the JBuilder UI

The following tutorial shows how to provide data to an application using a QueryDataSet component. This example also demonstrates how to attach the resulting data set to a GridControl for data viewing and editing.

The finished example for this tutorial is available as a completed project in the samples\borland\samples\tutorial\dataset\QueryProvide directory of your JBuilder installation under the file name QueryProvide.jpr. The running application, including the addition of the information on resolving a query, looks like this:

To create the application and populate a data set from a table,

  1. Select File|Close all from the menu.

  2. Select File|New|Application from the menu.

  3. Add a Database component from the Data Access tab to the Component tree of the Frame file. Open the connection property editor for the Database component by selecting the connection property ellipsis in the Inspector. Set the connection properties to the Local InterBase sample employee table:
    Property name Value
    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    The code generated by the designer for this step is:

    database1.setConnection(new borland.jbcl.dataset.ConnectionDescriptor
       ("jdbc:odbc:DataSet Tutorial", "SYSDBA", "masterkey", false,
        "sun.jdbc.odbc.JdbcOdbcDriver"));

    Tip: The connection dialog includes a Test Connection button. Click this 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.

  4. Add a QueryDataSet component. Set the query properties of the QueryDataSet component from the Inspector as follows:

  5. Click Test query to ensure that the query is runnable. When the gray area beneath the button indicates Success, click OK to close the dialog.

    To view the data in your application,

  6. Add a GridControl component and set its dataSet property to queryDataSet1. You'll notice that the grid fills with data.

  7. Run the application to browse the data set.

To save changes back to the data source, you will need additional steps (see Saving changes back to your data source).

This concludes the tutorial. Next, you might want to edit and modify data, save changes back to your data source, filter data, sort data, or set various properties and methods of the resulting QueryDataSet.

Opening and closing data sets

Database and DataSet are implicitly opened when components bound to them open. When you are not using a visual component, you must explicitly open a DataSet. Open propagates up and close propagates down, so opening a DataSet implicitly opens a Database. A Database is never implicitly closed.

Ensuring that a query is updatable

When JBuilder executes a query, it attempts to make sure that the query is updatable and that it can be resolved back to the database. If JBuilder determines that the query is not updatable, it will try to modify the query to make it updatable, typically by adding columns to the SELECT clause.

If a query is found to not be updatable and JBuilder cannot make it updatable by changing the query, the resulting data set will be read-only.

To make any data set updatable, set the updateMetaData property to NONE and specify the data set's table name and unique row identifier columns (some set of columns that can uniquely identify a row, such as columns of a primary or unique index).

You can query a SQL view, but JBuilder will not indicate that the data was received from a SQL view as opposed to a SQL table, so there is a risk the data set would not be updatable. You can solve this problem by writing a custom resolver.