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
• Connecting to a database
• Querying a database
• Using JDBC Explorer on JBuilder

Adding the Easysoft JDBC-ODBC Bridge driver

• Adding the Easysoft JDBC-ODBC Bridge driver to JBuilder
• Adding the Easysoft JDBC-ODBC Bridge driver to projects

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:

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

• Creating a library file which contains the driver’s classes, typically a .JAR file and any other auxiliary files such as documentation and source files.
• Deriving a .config file from the library file which JBuilder adds to its classpath at start-up.
• Adding the new library to your project, or to the default project if you want it to be available for all new projects.

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:

   

   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:

   

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

   

   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:

   

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:

   

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

   

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:

   

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

   

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:

   

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

   

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

   

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
• Adding a Database component to your application
• Setting Database connection properties
• Common connection error messages
• Using the Database component in your application

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:

   

   

   

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

   

   

   

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:

   

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 set promptPassword to true, you should also call openConnection() for your database. openConnection() determines when the password dialog box is displayed and when the database connection is made.
• Get user name and password information as soon as the application opens. To do this, call openConnection() at the end of the main frame’s jbInit() method.

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:

   Property	Description
   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:

   

   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:

   

   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:

   

   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:

• Unable to locate the Easysoft JDBC-ODBC Bridge driver

  The Easysoft JDBC-ODBC Bridge driver has not been added as a required library for the project. Select Project|Properties, and add Easysoft JDBC-ODBC Bridge Driver as a Required Library.

• Driver could not be loaded The Easysoft JDBC-ODBC Bridge driver has not been added to the CLASSPATH. Add the EJOB.jar file to the JBuilder startup script CLASSPATH, or to your environment’s CLASSPATH before launching JBuilder.

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
• Retrieving data by querying a database
• Creating the UI

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:

• Database

  The Database component encapsulates a database connection through JDBC to the SQL server and also provides lightweight transaction support.

• QueryDataSet

  A QueryDataSet component provides the functionality to run a query statement (with or without parameters) against tables in a SQL database, and stores the result set from the execution of the query.

• QueryDescriptor

  The QueryDescriptor object stores the query properties, including the database to be queried, the query string to execute, and optional query parameters.

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:

Property	Effect
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:

• Unparameterized queries

  The query is executed and rows are fetched into the QueryDataSet.

• Parameterized queries

  You use variables in the SQL statement and then supply the actual parameters to fill in those values.

• Dynamic fetching of detail groups

  Records from a detail data set are fetched on demand and stored in the detail data set.

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 Name	Value
   Driver	easysoft.sql.jobDriver
   URL	jdbc:easysoft://machine/DSN
   Username	Enter as required
   Password	Not 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 Name	Value
   Database	database1
   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:

   

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

   

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

   

   

   Switch to the More dbSwing tab on the component palette and add a DBDisposeMonitor 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:

   

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

   

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
• Browsing database schema objects
• View, create, and modify database URLs
• Executing SQL statements
• Create, view, and edit data

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:

Use View > Blob Explorer to view the images:

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

• Browse database schema objects such as tables, table data, columns (fields), indexes, primary keys, foreign keys, stored procedure definitions, and stored procedure parameters.
• View, create, and modify database URLs.
• Executing SQL statements to query a database.
• Create, view and edit data in existing tables.

Browsing database schema objects

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

• The left pane displays a hierarchical tree of objects that include database URLs, tables (and their columns, indexes, primary key, and foreign keys), views, system tables, and stored procedures (and their parameters).
• An expand icon beside an object in the left pane indicates that the object contains other objects below it. To see those objects, click the expand icon. When an object is expanded to show its child objects, the expand icon becomes a contract icon. To hide child objects, click the contract icon.
• The right pane contains tabbed pages that display the contents of objects highlighted in the left pane. The tabbed pages in the right pane vary depending on the type of object highlighted in the left pane. For example, when a database alias is highlighted in the left pane, the right pane displays a Definition page that contains the database URL, Driver, UserName, and other parameters, or properties. Bold parameter names indicate a parameter that cannot be modified. All other parameters that appear in the right pane can be edited there.

  The following tabbed pages may appear in the right hand pane:

  • Definition
  • Enter SQL
  • Summary
  • Data

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:

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:

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.

• About Easysoft

• Contact Us
• About Us
• Clients
• Upgrade Offer
• Blog
• Careers

• Products

• ODBC Drivers
• JDBC Drivers
• Bridges and Gateways
• In Development

• Services

• Consultancy
• Training
• Custom Development

• Licensing

• Product Licenses
• Prices

• Support

• Support Home
• Solution Wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer Area
• Client Applications

• Legal

• Privacy Statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

• 
• 
• 
•