Database administration tasks

This chapter provides information on how to accomplish common database administrator tasks with JBuilder. The following subjects are covered:

Using JBuilder for Database Administration tasks

This section provides an introduction to creating, populating, and deleting tables in a SQL-oriented manner. These tasks are usually reserved for a Database Administrator, but can easily be accomplished using JBuilder.

Creating the SQL data source

JBuilder is an application development environment in which you can create applications that access database data, but it does not include menu options for features that create SQL server tables. Typically, this is an operation reserved for a Database Administrator (DBA). However, creating tables can easily be done using SQL and JBuilder visual design tools.

This topic is not intended to be a SQL language tutorial but to show you how you can use SQL statements in JBuilder. For more information about the SQL syntax, refer to any book on the subject. One commonly used reference is A Guide to the SQL Standard by C.J. Date.

Note: On many systems, the DBA restricts table create rights to authorized users only. If you have any difficulties with creating a table, contact your DBA to verify whether your access rights are sufficient to perform such an operation.

To create a simple table:

  1. Follow the steps in Connecting to a database using JDBC, using the suggested values for the connection properties. By default, the Database component is added as database1.
  2. Add a QueryDataSet component.
  3. Edit the query property of the QueryDataSet, by default, queryDataSet1.
  4. Choose database1 from the Database drop-down list.
Now you are ready to specify and run a query in the SQL statement field. For example, in the Query property editor,
  1. Enter the following in the SQL statement field:
    create table mytable (
         lastName char(20),   
         firstName char(20),   
         salary numeric(10,2)  )
  2. Click the Test Query button. The query executes and generates an error dialog, warning you that the query did not return a result set. Ignore the warning and close the dialog.

    JBuilder issues this warning because it expects the SQL server to return a result set from the query execution. JBuilder then stores the result set in the StorageDataSet. Since this SQL statement did not return a result set, JBuilder displays the warning message. No data is expected to be returned from this query, so ignore the warning message in this case.

These steps create an empty table which can be used in a query. Click the Browse Tables button to verify that the table was created correctly. In the dialog that displays, you see: Close the Browse tables dialog to return to the Query property editor dialog. In the SQL statement field, you can change the table name and the list of columns then use the Test Query button repeatedly until you have created all your tables.

Populating a SQL table with data using JBuilder

Once you've created an empty table, you can easily fill it with data. The following example uses a GridControl to present a UI for data entry. The new data is then saved to the database.
  1. Follow the steps for Creating the SQL data source, but substitute the following SQL statement:
    select * from mytable
  2. Add a GridControl component from the JBCL tab, and set its dataSet property to the QueryDataSet component (by default, queryDataSet1).

  3. Add a NavigatorControl component from the JBCL tab, and set its dataSet property to the QueryDataSet component (by default, queryDataSet1).
Now, run the application. In the running application,
  1. Enter data into the grid.
  2. Click the Save Changes button on the navigator when you've completed data entry.

When you press the button, all your changes are written to the table. The next time you run this application, the data you just entered will display in the grid and you can use this application to modify and save data again.

You can also use SQL Explorer as a method for data entry. See Using SQL Explorer for database administration tasks for the steps for data entry.

Deleting tables in JBuilder

Now that you've created one or more test tables, you'll need to know how to clean up and remove all the test tables. Follow the steps for Creating the SQL data source but substitute the following SQL statement:
drop table mytable
You can verify the success of this operation by using the Browse Tables button of the Query property editor to see that the table does not appear in the tables list.

Using SQL Explorer for database administration tasks

The SQL Explorer is a hierarchical database browser with editing capabilities as described below. Through a persistent connection to the database, the SQL Explorer enables you to:

The following example uses SQL Explorer as a method for data entry. You can use the SQL Explorer to view, edit, insert, and delete data in tables.

  1. Select Tools|SQL Explorer to launch SQL Explorer.

  2. Select a table to view in the left pane.

  3. Click the Data page tab in the right pane to view a scrollable grid of all data in the table.

  4. Place the cursor on the row before which you wish to insert another row to insert a new record.

  5. Click the navigator's Insert button in the toolbar. A blank row appears.

  6. Enter data for each column. Move between columns with the mouse, or by tabbing to the next field.

  7. Select a different record in the grid or click the navigator's Post button in the toolbar to post the insert to the database.

  8. Click the navigator's Cancel button in the toolbar or press ESC to cancel an insert before moving to another record.

For more information on how to accomplish these and other tasks, launch the SQL Explorer by selecting Tools|SQL Explorer from the menu, then refer to its online help. SQL Explorer is available in the Client/Server edition of JBuilder when DataGateway is installed.