Database administration tasks

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

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


Exploring database tables and metadata using the JDBC Explorer

The JDBC Explorer is a hierarchical database browser that also allows you to edit data. It presents JDBC-based meta-database information in a two-paned window. The left pane contains a tree that hierarchically displays a set of databases and its associated tables, views, stored procedures, and metadata. The right pane is a multi-page display of descriptive information for each node of the tree. In certain cases, you can edit data in the right pane as well.

To display the JDBC Explorer, select Tools|JDBC Explorer from the JBuilder menu.

JDBC Explorer

Through a persistent connection to a database, the JDBC Explorer enables you to:

Browsing database schema objects

The JDBC Explorer window contains a menu, a toolbar, a status label, and two panes of database information.

For more information, launch the JDBC Explorer by selecting Tools|JDBC Explorer from the menu, then refer to its online book, JDBC Explorer.

Setting up drivers to access remote and local databases

The JDBC Explorer browses databases listed in the Connection URL History List section of the <home>/.jdatastore/jdbcExplorer.properties file. Additions are made to this list when you connect to a database using the connection property editor of a Database component.

You can use the JDBC Explorer to view, create, and modify database URLs. The following steps assume the URL is closed, and lists each task, briefly describing the steps needed to accomplish it:

Note: If you're creating a new ODBC URL and you are running Windows NT, you must define its ODBC Data Source though the Windows Control Panel before you can connect to that database.

Executing SQL statements

The Enter SQL page displays a window in which you can enter SQL statements, or specify and execute an existing .SQL file. The main part of the screen is an edit box where you can enter SQL statements. To the right of the edit box are three buttons, the Execute button, the Next button, and the Previous button. When an SQL SELECT statement is executed, the results of the query are displayed in an editable table, which is located below the edit box. This screen may need to be resized to view all its components. The page looks like this:

Enter SQL page

To query a database using SQL:

  1. Open a database by selecting its URL in the left pane and entering user name and password if applicable.
  2. Select the database or one of its child nodes in the left pane.
  3. Click the Enter SQL tab in the right pane to display an edit box where you can enter or select an SQL statement.
  4. Enter (or paste) an SQL statement in the edit box, or click the Load SQL button and enter a SQL file name. If you enter non-SELECT statements, the statement is executed, but no result set is returned.
  5. Click the Execute button to execute the query.

You can copy SQL statements from text files, a Help window, or other applications and paste them into the edit box. Some SQL servers require that the table name be entered in quotation marks, some do not require this.

Note: If the SQL syntax you enter is incorrect, an error message is generated. You can freely edit the Enter SQL field to correct syntax errors.

Using the Explorer to view and edit table data

Select the Data page to display the data in a selected table, view, or synonym. You can enter and edit records in a table on the Data page if the table permits write access, and if the Request Live Queries box of the Query page of the View|Options menu is checked. The Data page displays a table populated with the data from the selected table. A toolbar control is displayed across the top of the table for navigation and data modification. The Data page looks like this:

Data page

You can use the JDBC Explorer to view, edit, insert, and delete data in tables. The following list of tasks briefly describes the steps needed to accomplish each.

Edits only take effect when they are applied. To apply edits and make changes permanent:

  1. Click the Post button on the toolbar. This posts the changes to the local data set only (not the database).
  2. Click the Save changes button to commit the edits to the database.