The query property editor displays when you choose the query property of a QueryDataSet. This editor can be used to set the properties of the QueryDescriptor visually, but it also has several other uses. The dialog is displayed below. Each of the dialog options is explained in further detail as well.
On the Query tab, the following options are available:
Selecting a Database object enables the Browse Tables button. If the Database is connected to a Borland DataGateway URL, the SQL Builder button is also enabled.
An example of a few simple SQL statements that are used throughout this text are
SELECT emp_no, last_name, salary FROM employee
This SQL statement selects three fields from the EMPLOYEE table.
SELECT * FROM employee
This SQL statement selects all fields from the same table.
This button is dimmed and unavailable while the Database field displays the value "<none>". Specify a Database that is connected to a Borland DataGateway URL to enable this button.
This button is dimmed and unavailable while the Database field displays the value "<none>". Select a Database object in the Database field to enable this button.
If unchecked, the SQL string is written to the QueryDescriptor as a String embedded in the source code.
On the Parameters tab, you can select an optional ReadWriteRow or DataSet from which to fill in parameters, used for parameterized queries. Parameter values are specified through an instantiated ReadWriteRow object (or an instance of any of its subclasses), for example, a DataSet object. Select the ReadWriteRow object (or the ReadWriteRow subclass) that contains the values for your query parameters from the drop-down list. See Parameterizing a query for an example of this.
The visual SQL Builder is a tool for helping you write an appropriate SQL statement. In order to use the SQL Builder, the Database component must be connected to a local Borland DataGateway alias. It is perfectly acceptable for that database and its tables to reside on another machine, but the alias describing that connection must reside on the client machine. See the Borland Database Engine (BDE) Administrator tool to create new aliases on the client machine.
The SQL Builder is displayed by clicking the SQL Builder button in the query property dialog of a QueryDataSet component. This button is dimmed and unavailable while the Database field displays the value <none>, or when the database that is selected is not a DataGateway database with a local alias.
If a request is made to use the SQL Builder against a DataGateway remote table, JBuilder will attempt to open an alias of the same name on the client machine. Since this step is needed only to acquire the necessary low level metadata for the graphical SQL Builder, the SQL code generated will still run against the remote DataGateway database when the query is actually executed. It is only during design, and only within the SQL Builder tool, that this remote-to-local mapping occurs.
Therefore, if you anticipate using the SQL Builder against remote Borland DataGateway tables when designing JBuilder applications, it is a good idea to maintain BDE aliases (using the BDE Administrator tool) on the client machine which are exact duplicates of those residing on the respective remote machine(s). This will allow you to design locally, but deploy against the actual remote tables without changing your Java code.
SQL Builder enables you to visually and interactively create and execute SQL queries. An expert knowledge of SQL isn't necessary to create complex SQL queries. SQL Builder can even be a tool for learning SQL. When you use SQL Builder, you can view and edit the resulting SQL. You can create a full range of SQL queries, from the simplest SELECT to a complicated multi-table join with calculated fields and expression based joins. The SQL Builder user interface is composed of the following elements:
The Table Pane is a scrollable, sizable window that displays the tables that have been added to the pane.
The Query notebook is a tabbed notebook of query parameter grids that allow you to specify different options for the query. Each page has options that will affect the query in a particular way.
The SQL Query Text Entry window enables you to view and edit the SQL query as text. As you build your query graphically in SQL Builder, the query appears as a SQL statement in this window. You can toggle between the text entry window and SQL Builder at will. Changes that you make here will be reflected in SQL Builder graphically when you toggle back to SQL Builder.
The Query Results window appears when you execute the SQL query. The grid, that you can scroll using the browse buttons above the grid, contains results from the query.
Tables are added to a query by simply adding them to the table pane. In the table pane you can select some or all fields of one or more tables to be included in the result set. You can also graphically join one table to another. A table may be added more than once.
Each table window in the table pane has a table and field name with a check box that allows you to select some or all fields to be included in the result set.
Selection criteria in a query specifies which rows of data are included in the query results. You enter selection criteria into the Criteria page of the Query notebook.
The Grouping page enables you to create a grouped query. A grouped query groups the data from the source tables and produces a single summary row for each row group.
Fields can be linked by dragging one or more fields from one table to the fields on another table. Graphically, a join is indicated by a single line that connects the two table windows at their table name.
Each linked field pair in the join is added as a separate row to the Joins page grid. When a join line is selected in the table pane, the join list box (above the Join page grid) will contain the two joined tables.
The SQL Query Text Entry window enables you to view and edit the SQL query as text. As you build your query graphically in SQL Builder, the query appears as a SQL statement in this window. You can toggle between the text entry window and SQL Builder at will. Changes that you make here will be reflected in SQL Builder graphically when you toggle back to SQL Builder.
The Query Results window appears when you execute the SQL query. The grid, that you can scroll using the browse buttons above the grid, contains results from the query.
For more information on the SQL Builder, see its online help.
A java.util.ResourceBundle contains locale-specific objects. When your program needs a locale-specific resource, your program can load it from the resource bundle that is appropriate for the current user's locale. In this way, you can write program code that is largely independent of the user's locale isolating most, if not all, of the locale-specific information in resource bundles.
The Create ResourceBundle dialog appears when the query editor is closing, if a SQL statement has been defined in the query editor and the "Place SQL text in resource bundle" option has been checked. The resource bundle dialog looks like this:
To use a resource bundle in your application,
package myPackage; public class myResource extends ListResourceBundle { Object[][] contents = { {"Hello_Messsage", "Howdy mate"} } public Object[][] getContents() { return contents; } }
# comments Hello_message=Howdy mate
queryDataSet1.setQuery(new borland.sql.dataset.QueryDescriptor(database1, "select * from employee", null, true, DataSetLoadOption.LOAD_ALL_ONCE));
queryDataSet1.setQuery(new borland.sql.dataset.QueryDescriptor(database1, sqlRes.getString("employee"), null, true, DataSetLoadOption.LOAD_ALL_ONCE));Whenever you save the SQL text in the QueryDescriptor dialog, JBuilder automatically creates a new file called "SqlRes.java". It places the text for the SQL string inside SqlRes.java and creates a unique string "tag" which it inserts into the text. For example, for the select statement "SELECT * FROM employee", as entered above, the moment the OK is pressed, the file SqlRes.java would be created, looking something like this:
public class SqlRes extends java.util.ListResourceBundle { static final Object[][] contents = { { "employee", "select * from employee" }}; static final java.util.ResourceBundle res = getBundle("untitled3.SqlRes"); public static final String getStringResource(String key) { return res.getString(key); } public Object[][] getContents() { return contents; } }
If the SQL statement is changed, the changes would be saved into SqlRes.java. No changes will be necessary to the code inside jbInit(), because the "tag" string is invariant.
For more information on resource bundles, see the JavaDoc for java.util.ResourceBundle, found from JBuilder help by selecting Help|Java Reference. Then select the java.util package, and the ResourceBundle class.