Using the JDBC Explorer for database administration tasks
Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
This section provides an introduction to creating, populating, and deleting tables in an 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 the JDBC Explorer.
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, you must first set up a database connection URL. If you are unfamiliar with how to do this, follow these steps:
- Select Tools|JDBC Explorer.
- From the JDBC Explorer, select File|New, or right-click an existing URL and select New from the context menu. The New URL dialog displays.
- Select a Driver from the drop-down list or enter the driver
information. For a discussion of the different types of drivers, see JDBC database drivers in the JDBC Explorer online help.
- Browse to or enter the desired URL. The Browse button will be enabled when a database driver that is recognized by JBuilder is selected in the Driver field.
- Click OK to close the dialog.
- On the Definitions page in the right pane, specify the
UserName and any other desired properties.
- Click the
Apply button on the toolbar to apply the connection parameters.
Once a connection has been established, you can specify a SQL statement to run against the database. There are two ways to do this. The first way is through the Create Table dialog. To create a table called mytable using the Create Table dialog,
- Select File|Create Table in the JDBC Explorer.
- Type
mytable
in the Table name field.
- Click the Insert button.
- Type
lastName
in the Column name column.
- Select
VARCHAR
as the Data type column value.
- Type
20
in the Precision column.
- Click the Next row button. A new row is created.
- Type
firstName
in the Column name column.
- Select
VARCHAR
as the Data type column value.
- Type
20
in the Precision column.
- Click the Next row button. A new row is created.
- Type
salary
in the Column name column.
- Select
NUMERIC
as the Data type column value.
- Type
10
in the Precision column.
- Type
2
in the Scale column.
- Click the Execute button.
- Note that an SQL statement has been created for you in the SQL text area.
- Click OK. The table is created in the currently open data source.
The second way to create a table is to specify a CREATE TABLE
SQL statement in the Enter SQL tab. For example, to create a table named mytable2 on the data source to which you are connected,
- Click the Enter SQL tab in the JDBC Explorer.
- Enter the following in the text area:
create table mytable2 (
lastName char(20),
firstName char(20),
salary numeric(10,2) )
- Click the Execute button.
These steps create an empty table which can be used in a query.
Use the JDBC Explorer to verify that the table was created correctly. You should see:
- a list of tables in the data source, including the new table (MYTABLE) just created.
- a list of columns for the selected table. Select MYTABLE and the columns list displays FIRSTNAME, LASTNAME and SALARY.
Populating a SQL table with data using JBuilder
Once you've created an empty table, you can easily fill it with data using the JDBC Explorer (in this example), or by creating an application using JBuilder's visual design tools. 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 of the JDBC Explorer if the table permits write access, and if Request Live Queries is checked in the View|Options dialog box. The Data page displays a table populated with the data from the selected table.
- Follow the steps for "Creating the SQL data source".
Select the table you just created in the left window, then select the Data tab in the right window. A table populated with the data from the selected table displays in the right pane. A toolbar control is displayed across the top of the table for navigation and data modification.
You can now use the JDBC Explorer to
view, edit, insert, and delete data in tables. See "Using the Explorer to view and edit table data" for more information on these tasks.
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 checking to see if the table still displays in the left window of the JDBC Explorer.