JDBC Explorer: Overview

This is a feature of JBuilder Professional and Enterprise.

The JDBC Explorer is a hierarchical database browser that also allows you to edit data.

The JDBC Explorer 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.

Exploring database objects

The database objects that appear on the left side are dependent on the type of database driver selected for the URL. Some of the database drivers are based on Java Database Connectivity (JDBC). JDBC is an all-Java, thus cross-platform, API.

The left pane contains the categories of database objects supported by the selected driver. These may include:

Database URLs

The JDBC connection URL is the JDBC method for specifying the location of a JDBC data provider (i.e., SQL server).

Setting up drivers to access remote and local databases

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 under Windows NT, you must define its ODBC Data Source though the Windows Control Panel before you can connect to that database.

Tables

When a table is selected in the left pane, you can enter and edit records on the Data page if the table permits write access, and if Request Live Queries is checked in the View|Options dialog box. Refer to the Using the Explorer to view and edit table data topic for more information on entering and editing data in tables. The Data page displays a grid populated with the data from the selected table. A navigation tool is displayed across the top of the grid for navigation and data modification.

Click the expand glyph beside a table in the left pane to display the child objects for the table. Child objects for the table object include:

Views

Click the expand glyph beside the Views object to display the list of custom views for this table. A custom view is a predefined query that returns a subset of data.

To create a custom view of a database table or query in SQL, enter an SQL statement on the Enter SQL page, such as

create view CaliforniaEmployees as select * from employee where state = "CA"

When you execute the SQL statement, a view will be created so you can easily see all California employees whenever you wish.

System Tables

System tables get created with the database; they are not created by the user. Most SQL servers are self-descriptive; that is, they store information about the database in system tables. For example, there might be one system table that has as its contents the names of all the tables in the database, another that describes all indexes in the database, and so on.

Procedures

You can use the JDBC Explorer to browse database server-specific schema objects, including stored procedure definitions, source (for some drivers), and parameters. With a stored procedure, one or more SQL statements are encapsulated in a single location on your server and can be run as a batch. For information on running stored procedures, see the Obtaining data through stored procedures topic in Database Application Developer's Guide.

In the Enter SQL page, you can enter a procedure name or server-specific syntax for procedure calls. When a server allows a separate syntax for procedure calls, you can enter that syntax instead of an existing stored procedure or JDBC procedure escape sequence. For example, server-specific syntax may look like this, where the ? character is a placeholder for a parameter:

execute procedure PROCEDURENAME ?,?,?

To browse stored procedures and their parameters,

Working with database objects

The pane on the right side contains tabbed pages that change depending on which type of database object is selected. The options include:

The Definition page

The Definition page displays the parameters, or properties, of the object highlighted in the left pane.

Enter SQL page

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 four buttons, the Execute button, the Next button, the Previous button, and the Load SQL 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:

JDBC Explorer with Enter SQL tab displayed

Executing SQL statements

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 the name of an SQL file. 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.

Data page

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 navigation tool is displayed across the top of the table for navigation and data modification. The Data page looks like this:

Data page

Text page

Select the Text page to view the source code for stored procedures.

Using the Explorer to view and edit table data

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:

The Summary page

The Summary page displays the objects contained within the Table or Procedure highlighted in the left pane. Double-click an item in the Summary list to display its Definition.

Understanding the JDBC Explorer environment

Although you will work primarily with database URLs and data in the left and right pane of the Explorer, understanding the JDBC environment is essential to taking full advantage of its benefits.

Menu options

The following topics define the commands available from each menu option on the JDBC Explorer.

File menu commands

These commands appear on the File menu. Most of these commands are also available from the context menu that appears when you right-click an object in the left pane of the JDBC Explorer.

New Opens the New URL dialog. See New URL dialog for a description of its options.
Open Opens the highlighted URL. You will be prompted for a user name and/or password.

If you are using the InterBase tables used in some of the database tutorials, the password is SYSDBA and the password is masterkey.

If you are using the sample JDataStore tables, enter your user name. Leave the password blank.

Close Closes the highlighted URL.
Create Table... Opens the Create Table dialog box which enables you to create a database table and define its columns visually.
Apply Commits modifications made on the Definition page of a selected URL to the database. The Apply option will be available when you have added or edited a URL.
Cancel Abandons modifications made on the Definition page of a selected URL.
Delete Deletes the highlighted URL from the JDBC Explorer view.
Exit Exits the JDBC Explorer.

View menu commands

The following options are available from the View menu:

Toolbar If checked, the toolbar is displayed near the top of the Database Explorer. The toolbar offers a few convenient icons that duplicate the functionality of menu commands such as Open, Close, Delete, Apply, and Cancel.
Statusbar If checked, a status bar appears at the bottom of the Database Explorer window. It states how many items are found in the object highlighted in the left pane.
Blob Explorer The Blob Explorer dialog is used to display the contents of memo or graphics data in a blob (binary large object) field. For a description of the Blob Explorer, see Blob Explorer below.
Refresh If enabled for the currently selected database, Refresh retrieves server information and redraws the display of the database and its children.
Options Opens the Options dialog, which contains 2 pages: Login and Query. See JDBC Explorer options for a description of the options.

Blob Explorer

The Blob Explorer dialog is used to display the contents of memo or graphics data in a blob (binary large object) field. To open the Blob Explorer, select View|Blob Explorer. While you can open the Blob Explorer at any time, it is only meaningful when you are browsing or editing blob data in a table.

If the field is an image field, the Blob Explorer attempts to display the graphic. If the field is a text field, the Blob Explorer attempts to display the blob text.

For example, in the sample database used in the Database Application Developer's Guide, the sample database PROJECT contains a field, PROJ_DESC, of type BLOB SUB_TYPE 1, a text field.

To use the Blob Explorer on this column,

  1. Select the PROJECT table in the left pane of the Blob Explorer.
  2. Select the Data page in the right pane.
  3. Select a value in the PROJ_DESC column.
  4. Select View|Blob Explorer.

The entire text of the field appears in the Blob Explorer window.

Help menu commands

The following options are available from the Help menu:

Contents Displays the JBuilder JDBC Explorer Help Viewer window.
About Displays JDBC Explorer version and copyright information.

Toolbar options

The JDBC Explorer toolbar contains the following buttons for executing commands:

JDBC Explorer toolbar

JDBC Explorer Toolbar

From left to right, here is what the buttons do:

Button Command
Open Opens a selected URL. You will be prompted for a user name and password, then a connection to the URL will be established.
Close Closes a selected URL by terminating the connection to the server.
Delete Deletes a selected URL. When the URL is deleted, it will not appear in the list of URLs available to the JDBC Explorer. To see the URL again, select File|New to add the URL to the JDBC Explorer window.
Cancel Abandons modifications made on the Definition page of a selected URL.
Apply Commits modifications made on the Definition page of a selected URL to the database. The Apply button will be available when you have edited or added a URL.


Creating tables

You can use the JDBC Explorer to create a database table and define its columns visually. To open this dialog box, choose File|Create Table in the JDBC Explorer.

Creating the table and defining its columns

To create a new table in the JDBC Explorer,

  1. Create a connection to a database in the JDBC Explorer. (File|New or File|Open.)
  2. Select that database, or any of its child nodes, in the tree on the left side of the JDBC Explorer, and choose File|Create Table. This opens the Create Table dialog box.

    createtables.gif

  3. Type the name of the new table in the Table Name field.

  4. Click the Insert New Row button  insertnew.gif  on the Navigation bar to create a new row.
  5. Click in the Column Name field and type the name of the new column.
  6. Click in each of the parameter fields on that row you want to define, and select or enter a value. The data type column has a drop-down that lists all the data types specific to the database server to which you are connected. You must select or enter values that generate a SQL statement that is valid for that server.
  7. Continue creating the rest of the columns in this manner, rearranging their order in the table as desired. Use the Navigation bar buttons to add or insert additional rows, move to different rows, and rearrange the rows you have added.
  8. Click OK when you are finished creating and defining all the columns.

Modifying the SQL statement

As you specify each column and its parameters, the SQL statement for creating them is automatically generated and displayed in the SQL edit field at the bottom of the dialog box. While you are designing the columns visually, editing in the SQL field is disabled.

To modify the SQL statement manually, click the SQL button. The SQL field at the bottom becomes an active editor.

createtables_sql.gif

Note: If you edit the SQL manually, you will no longer be able to visually define and edit this tables columns in the Create Table dialog box.