Using Borland JBuilder with the Easysoft JDBC-ODBC Bridge to Access ODBC Data Sources

The following links cover four topics related to the use of the Easysoft JDBC-ODBC Bridge with JBuilder 4 and present modified versions of relevant pages from the JBuilder on-line documentation.

Adding the Easysoft JDBC-ODBC Bridge driver

Adding the Easysoft JDBC-ODBC Bridge driver to JBuilder

The first step in setting up the Easysoft JDBC-ODBC Bridge is to install the server on the machine on which the ODBC data sources that you want to connect to are set up, which will typically be a machine different from the one on which you will run JBuilder.

The Easysoft JDBC-ODBC Bridge driver classes should be downloaded from the machine on which you have installed the JOB Server. Ensure that the JOB Server is running and use your web browser to connect to the web server of the installed JOB Server using an URL of the form:

http://yourmachine:8031/

where yourmachine is the machine on which the JOB Server has been installed.

If in the course of installing the JOB Server you have changed the default port then replace "8031" with the new port number.

Follow the link to "EJOB.jar" on the navigation bar under the heading Driver Classes. Right click and select "Save As". Navigate to a directory where you keep .JAR files (e.g. c:\jars) and click Save:

Save As dialog box c:\jars directory

There are three steps to adding a JDBC driver to JBuilder:

  1. Open JBuilder and choose Tools|Enterprise Setup. Click the Database Drivers tab which displays .config files for all the currently known database drivers. Click Add to add a new driver:

    Enterprise Setup dialog box Database Drivers tab

    Note: Uninstalled drivers are displayed in red on the Drivers list in the Connection Property dialog box and cannot be selected for use in JBuilder. You must install them according to the manufacturer before setting them up in JBuilder.

  2. Click New to create a new library file for the driver:

    Select One or More Libraries dialog box

  3. Add the driver to the required libraries list for projects:

    New Library Wizard dialog box

    Note: You can also create a new library under Tools|Configure Libraries, but since you would then have to use Enterprise Setup to derive the .config file, it is simpler to do it all here.

  4. Type a name and select a location for the new file in the Create New Library dialog box:

    New Library Wizard dialog box with Easysoft JDBC-ODBC Bridge as the example Name and JBuilder as the Location.

  5. Click Add and browse to the location of the driver. You can select the directory containing the driver and all its support files or you can select only the archive file for the driver. Either method will work, as JBuilder will extract the information it needs:

    New Easysoft JDBC-ODBC Bridge library

  6. Click OK to close the file browser. This displays the new library at the bottom of the library list and selects it:

    Select One or More Libraries dialog box with new Easysoft JDBC-ODBC Bridge selected

  7. Click OK. JBuilder creates a new .library file in the JBuilder /lib directory with the name you specified (for example, InterClient.library). It also returns you to the Database Drivers page which displays the name of the corresponding .config file in the list which will be derived from the library file (for example, InterClient.config).
  8. Select the new .config file in the database driver list and click OK:

    Enterprise Setup dialog box Database Drivers tab with the Easysoft JDBC-ODBC Bridge .config file selected

    This places the .config file in the JBuilder /lib/ext directory:

    Restart JBuilder Message Box

  9. Close and restart JBuilder so the changes to the database drivers will take effect and the new driver will be added to the JBuilder classpath.

Important: If you make changes to the .library file after the .config file has been derived, you must regenerate the .config file using Enterprise Setup, then restart JBuilder.

Adding the JDBC driver to projects

Projects run from within JBuilder use only the classpath defined for that project. Therefore, to make sure the JDBC driver is available for all new projects that will need it, define the library and add it to your default list of required libraries. This is done from within JBuilder using the following steps:

  1. Start JBuilder and close any open projects.
  2. Choose Project|Default Project Properties:

    Default Project Properties dialog box Paths tab

  3. Select the Required Libraries tab on the Paths page and then click Add:

    Select One or More Libraries dialog box with new Easysoft JDBC-ODBC Bridge selected

  4. Select the new JDBC driver from the library list and click OK:

    Default Project Properties dialog box Paths tab with the Easysoft JDBC-ODBC Bridge highlighted

  5. Click OK to close the Default Project Properties dialog box.

Note: You can also add the JDBC driver to an existing project. Just open the project, then choose Project|Properties and use the same process as above.

Now that JBuilder and the new JDBC driver have been set up to work together, the next step is to create or open a project that uses this driver, add a database component to it and set its connection property so that it can use that driver to access the data.

For an example of how to do this, see Connecting to a database.

Connecting to a database

Connecting to an ODBC data source on JBuilder

The Database component handles the JDBC connection to a SQL server and is required for all database applications involving server data. JDBC is the Sun Database Application Programmer Interface, a library of components and classes developed by Sun to access remote data sources. The components are collected in the java.sql package and represent a generic, low-level SQL database access framework.

The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java. The JDBC API is implemented via a driver manager that can support multiple drivers connecting to different databases. For more information about JDBC visit the Sun JDBC Database Access API web site.

JBuilder uses the JDBC API to access the information stored in databases. Many of JBuilder’s data access components and classes use the JDBC API. Therefore, these classes must be properly installed in order to use the JBuilder database connectivity components. In addition, you need an appropriate JDBC driver to connect your database application to a remote server. Drivers can be grouped into two main categories: drivers implemented using native methods that bridge to existing database access libraries, or all-Java based drivers. Drivers that are not all-Java must run on the client (local) system. All-Java based drivers can be loaded from the server or locally. The advantages to using a driver entirely written in Java are that it can be downloaded as part of an applet and is cross-platform.

Note: When you no longer need a Database connection, you should explicitly call the Database.closeConnection() method in your application. This ensures that the JDBC connection is not held open when it is not needed and allows the JDBC connection instance to be garbage collected.

Adding a Database component to your application

The Database component is a JDBC-specific component that manages a JDBC connection. To access data using a QueryDataSet or a ProcedureDataSet component, you must set the database property of the component to an instantiated Database component. Multiple data sets can share the same database, and often will.

In a real world database application, you would probably place the Database component in a data module. Doing so allows all applications that access the database to have a common connection.

Note: Ensure that any existing Projects are closed by selecting File|Close from the JBuilder menu or your new application files will be added to the existing Project, rather than the new one.

  1. Select File|New from the JBuilder menu to create a new Project file using the Application wizard:

    Project Wizard Step 1 with JDBCDemo as the project name and directory, other settings are inherited from the default project

    Project Wizard Step 2 with the Easysoft JDBC-ODBC Bridge highlighted in the Required libraries list

    Project Wizard Step 3 with the Easysoft JDBC-ODBC Bridge highlighted in the Required libraries list

  2. Select File|New and double-click the Application icon to add new Applications to the Project:

    Object Gallery with the Application icon selected

    Application Wizard Step 1 with jdbcdemo as the Package and Application1 as the Class

    Application Wizard Step 2 with Frame1 as the Class and Frame Title as the Title

  3. Open the UI designer by selecting the file Frame1.java in the content pane and then select the Design tab at the bottom of the AppBrowser.
  4. Select the DataExpress tab from the component palette and click the Database component.
  5. Click anywhere in the designer window to add the Database component to your application. This adds the following line of code to the Frame class:
    Database database1 = new Database();
    

    The Database component appears in the content pane:

    The Database component in the JBuilder content pane

Setting Database connection properties

The Database connection property specifies the JDBC driver, connection URL, user name, and password. The JDBC connection URL is the JDBC method for specifying the location of a JDBC data provider (for example, SQL server). It contains all the information necessary for making a successful connection, including user name and password.

You can access the ConnectionDescriptor object programmatically, or you can set connection properties through the Inspector. If you access the ConnectionDescriptor programmatically, follow these guidelines:

If you don’t explicitly open the connection, it will try to open when a component or data set first needs data.

The following steps describe how to set connection properties through the UI designer to an ODBC data source of your choice.

Note: You will need to make sure that your JOB server running on your system.

  1. Select database1 in the component tree.
  2. Select the connection property’s value in the Inspector, and click the ellipsis button to open the Connection property editor.

    Set the following properties:

    PropertyDescription
    Driver The class name of the JDBC driver that corresponds to the URL. For this example use easysoft.sql.jobDriver.
    URL The Universal Resource Locator (URL) for the Easysoft JDBC-ODBC Bridge if it is located on the same machine as the JDBC application is of the form
    jdbc:easysoft:ODBC DSN

    or

    jdbc:easysoft://server host/ODBC DSN
    Username The user name authorized to access the server database. For this example any user name will work.
    Password The password for the authorized user. For this example no password is required.
    Prompt user password Whether to prompt the user for a password when opening the database connection.

    The dialog box looks like this:

    Connection dialog box General tab with easysoft.sql.jobDriver as the Driver and jdbc:easysoft as the URL

    If you now press the Test connection button a dialog box will be popped up by the Easysoft JDBC-ODBC Bridge allowing you to select a (System) Data Source Name available to be selected for connection:

    Easysoft JDBC-ODBC Bridge Select DSN dialog box

    Note: It is advisable to modify the URL to include the name of the data source, so that a connection can be made without having to preselect the data source name first:

    Connection dialog box with the data source name specified in the URL jdbc:easysoft:ibsample

    Click the Test connection button to check that the connection properties have been correctly set. The connection attempt results are displayed beside the Test connection button.

    Click OK to exit the dialog box and write the connection properties to the source code when the connection is successful.

    The source code, if the example above is followed, looks similar to this:

    database1.setConnection(new
    com.borland.dx.sql.dataset.ConnectionDescriptor(
          "jdbc:easysoft:ibsample",
          "", "", false, "easysoft.sql.jobDriver")
    );
    

    Select a DBDisposeMonitor component from the More dbSwing tab. Click in the content pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

    Set the DBDisposeMonitor’s dataAwareComponentContainer property to this.

Tip: Once a database URL connection is successful, you can use the JDBC Explorer to browse JDBC-based meta-database information and database schema objects in the JDataStore, and to execute SQL statements, and browse and edit data in existing tables.

Common connection error messages

Listed below are some common connection errors and solutions:

Using the Database component in your application

Now that your application includes the Database component, you’ll want to add another DataExpress component that retrieves data from the data source to which you are connected. JBuilder uses queries and stored procedures to return a set of data. The components implemented for this purpose are QueryDataSet and ProcedureDataSet. These components work with the Database component to access the SQL server database.

For details of how to use these components see Querying a database.

Querying a database

Introduction

A QueryDataSet component is a JDBC-specific DataSet that manages a JDBC data provider, as defined in the query property. You can use a QueryDataSet component in JBuilder to extract data from a data source into a StorageDataSet component. This action is called "providing". Once the data is provided, you can view and work with the data locally in data-aware components. When you want to save the changes back to your database, you must resolve the data.

QueryDataSet components enable you to use SQL statements to access, or provide, data from your database. You can add a QueryDataSet component directly to your application, or add it to a data module to centralize data access and control business logic.

To query a SQL table, you need the following components, which can be supplied programmatically or with JBuilder design tools:

The QueryDataSet has built-in functionality to fetch data from a JDBC data source. However, the built-in functionality (in the form of the default resolver) does much more than fetch data. It also generates the appropriate SQL INSERT, UPDATE, and DELETE queries for saving changes back to the data source after it has been fetched.

The following properties of the QueryDescriptor object affect query execution. These properties can be set visually in the query property editor:

PropertyEffect
database Specifies what Database connection object to run the query against.
query A SQL statement (typically a SELECT statement).
parameters An optional ReadWriteRow from which to fill in parameters, used for parameterized queries.
executeOnOpen Causes the QueryDataSet to execute the query when it is first opened. This is useful for presenting live data at design time. You may also want this enabled at run time.
loadOption An optional integer value that defines the method of loading data into the data set. Options are:
  • Load All Rows

    Load all data up front.

  • Load Rows Asynchronously

    Causes the fetching of DataSet rows to be performed on a separate thread. This allows the DataSet data to be accessed and displayed as the QueryDataSet is fetching rows from the database connection.

  • Load As Needed

    Load the rows as they are needed.

  • Load One Row At A Time

    Load as needed and replace the previous row with the current. Useful for high-volume batch-processing applications.

A QueryDataSet can be used in three different ways to fetch data:

Retrieving data by querying a database

The following example shows how to retrieve data using a QueryDataSet component and how to attach the resulting data set to a JdbTable for data viewing and editing.

To create the application and retrieve data from a table:

  1. Select File|Close All, then File|New.
  2. Double-click the Application icon and accept all defaults to create a new application. Frame1.java will be opened by default in the content pane.
  3. Select the Design tab to activate the UI designer.
  4. Click the Database component on the Data Express tab of the component palette, then click anywhere in the UI designer or the component tree to add the component to the application. database1 is added to the DataExpress folder in the component tree, and selected by default.
  5. Click in the connection property value field in the Inspector, then click the ellipsis button to open the Connection property editor for database1.
  6. Set the connection properties to the JDataStore sample EMPLOYEE table, as follows:
    Property NameValue
    Drivereasysoft.sql.jobDriver
    URL jdbc:easysoft://machine/DSN
    UsernameEnter as required
    PasswordNot required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the connection is successful, click OK.

    You can view the code generated by the designer for this step by selecting the Source tab and looking for the ConnectionDescriptor code. Click the Design tab to continue.

    For more information on connecting to databases, see Connecting to a database using the Easysoft JDBC driver.

  7. Now add a QueryDataSet component to your application from the Data Express tab of the component palette.
  8. Click in the query property value field in the Inspector for queryDataSet1, then click the ellipsis button to open the Query property editor.

    Set the following properties:

    Property NameValue
    Databasedatabase1
    SQL Statement
    select * from employee
    

    Click Test Query to ensure that the query is runnable. When the area beneath the button indicates Success, as shown below, click OK to close the dialog box:

    Query dialog box query tab

    Click Browse Tables to get a list of tables and columns to build the query from:

    Available tables and columns dialog box

    Alternatively, you could use the SQL Builder to construct your query:

    SQL Builder

    Query dialog box following a successful query

    Switch to the More dbSwing tab on the component palette and add a DBDisposeMonitor DBDisposeMonitor icon to the application. This component will close the JDataStore when the window is closed.

    Set the dataAwareComponentContainer property for dBDisposeMonitor1 to ‘this’.

  9. Choose File|Save All.

Creating the UI

Now create the UI for viewing and navigating the data in your application. Select the dbSwing tab on the component palette, and do the following:

  1. Select contentPane (BorderLayout) in the component tree. Black sizing nibs around the edges of the panel in the designer show it is selected.
  2. Drop a JdbNavToolBar component into the designer at the top, center of the panel and set its constraints property to NORTH.
  3. Drop a JdbStatusLabel component into the designer at the bottom, center of the panel and set its constraints property to SOUTH.
  4. Drop a TableScrollPane component into the designer into the center of the panel, and set its constraints property to CENTER.
  5. Drop a JdbTable component into the center of tableScrollPane1 and set its dataSet property to queryDataSet1.

    You’ll notice that the designer displays a table with live data. The application looks like this in the designer:

    JBuilder designer displaying table data

  6. Select Run|Run Project to run the application and browse the data set. The application looks like this when it is running:

    Browsing the data set in the sample application

  7. To save the changes back to the data source, you can use the Save Changes button on the toolbar component or, for more control on how changes will be saved, create a custom data resolver.

For details of how to use JDBC Explorer on JBuilder see Using JDBC Explorer on JBuilder.

Using JDBC Explorer on JBuilder

Introduction

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:

Browsing the dbo_Employees table in the JDBC Explorer

Use View > Blob Explorer to view the images:

Employee image

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.

View, create, and modify database URLs

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.

The following steps assume the URL is closed, and lists each task, briefly describing the steps needed to accomplish it:

View a URL

  1. In the left pane, select the URL to view. The Definition page appears in the right pane.
  2. Click the expand icon beside a database URL (or double-click it) in the left pane to see its contents.

Create a URL

  1. Select a URL or database in the left pane.
  2. Right-click to invoke the context menu.
  3. Choose New (or select File > New from the menu).
  4. Select a Driver from the drop-down list or enter the driver information. Drivers must be installed to be used, and the driver’s files must be listed in the CLASSPATH statement in the JBuilder setup script.
  5. Browse to or enter the desired URL.
  6. On the Definitions page in the right pane, specify the UserName and any other desired properties.
  7. Click the Apply button on the toolbar to apply the connection parameters.

Modify a URL

  1. Select the URL to modify in the left pane. The Definitions page appears in the right pane.
  2. Edit settings on the Definitions page as desired.
  3. Click the Apply button on the toolbar to update the connection parameters.

Delete a URL

  1. Select the URL to delete in the left pane.
  2. Select File > Delete from the menu to remove the URL.

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. The screen may need to be resized to view all its components:

JDBC Explorer Enter SQL pane

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.

Note: Some SQL servers require that the table name be entered in quotation marks. 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.

Create, view, and edit 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 on 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:

JDBC Explorer Data pane

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:

View table data

  1. Select a table to view in the left pane.
  2. Click the Data page tab in the right pane to view a scrollable table of all data in the table.
  3. Use the toolbar buttons at the top of the table to scroll from record to record.

Edit a record

  1. Make sure that Request Live Queries in the View > Options menu is checked.
  2. Edit the record’s fields in the table.
  3. To post the edits to the local data set, select a different record in the table, or click the toolbar’s Post button.
  4. To cancel an edit before moving to another record, click the toolbar’s Cancel button or press ESC.
  5. To save your changes to the database, click the Save changes button.

Insert a new record

  1. Place the cursor on the row before which you wish to insert another row.
  2. Click the toolbar’s Insert button. A blank row appears.
  3. Enter data for each column. Move between columns with the mouse, or by tabbing to the next field.
  4. To post the insert to the local data set, select a different record in the table, or click the toolbar’s Post button.
  5. To cancel an insert before moving to another record, click the toolbar’s Cancel button or press ESC.
  6. To save an insert to the database, click the Save changes button.

Delete a record

  1. Place the cursor on the row you wish to delete.
  2. Click the toolbar’s Delete button.

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.

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)