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 to JBuilder
- Adding the Easysoft JDBC-ODBC Bridge driver to projects
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:
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
.JARfile and any other auxiliary files such as documentation and source files.
- Deriving a
.config filefrom 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.
- Open JBuilder and choose Tools|Enterprise Setup. Click the Database Drivers tab which displays
.configfiles 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.
- Click New to create a new library file for the driver:
- 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.
- Type a name and select a location for the new file in the Create New Library dialog box:
- 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:
- Click OK to close the file browser. This displays the new library at the bottom of the library list and selects it:
- Click OK. JBuilder creates a new .library file in the JBuilder
/libdirectory 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
.configfile in the list which will be derived from the library file (for example,
- Select the new
.config filein the database driver list and click OK:
This places the
.config filein the JBuilder
- 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.
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:
- Start JBuilder and close any open projects.
- Choose Project|Default Project Properties:
- Select the Required Libraries tab on the Paths page and then click Add:
- Select the new JDBC driver from the library list and click OK:
- 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 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
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.
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.
- Select File|New from the JBuilder menu to create a new Project file using the Application wizard:
- Select File|New and double-click the Application icon to add new Applications to the Project:
- Open the UI designer by selecting the file
Frame1.javain the content pane and then select the Design tab at the bottom of the AppBrowser.
- Select the DataExpress tab from the component palette and click the
- Click anywhere in the designer window to add the
Databasecomponent to your application. This adds the following line of code to the Frame class:
Database database1 = new Database();
Databasecomponent appears in the content pane:
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
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
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.
database1in the component tree.
- Select the
connectionproperty’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
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://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
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.
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.jarfile to the JBuilder startup script CLASSPATH, or to your environment’s CLASSPATH before launching JBuilder.
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
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.
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:
Databasecomponent encapsulates a database connection through JDBC to the SQL server and also provides lightweight transaction support.
QueryDataSetcomponent 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.
QueryDescriptorobject stores the query properties, including the database to be queried, the query string to execute, and optional query parameters.
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:
|database|| Specifies what |
|query||A SQL statement (typically a SELECT statement).|
|parameters|| An optional |
|executeOnOpen|| Causes the |
|loadOption|| An optional integer value that defines the method of loading data into the data set. Options are: |
QueryDataSet can be used in three different ways to fetch data:
- Unparameterized queries
The query is executed and rows are fetched into the
- 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.
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:
- Select File|Close All, then File|New.
- 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.
- Select the Design tab to activate the UI designer.
- Click the
Databasecomponent 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.
database1is added to the DataExpress folder in the component tree, and selected by default.
- Click in the
connectionproperty value field in the Inspector, then click the ellipsis button to open the Connection property editor for
- Set the
connectionproperties to the JDataStore sample EMPLOYEE table, as follows:
Property Name Value Driver
Username Enter as required Password Not required
connectiondialog 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
ConnectionDescriptorcode. Click the Design tab to continue.
For more information on connecting to databases, see Connecting to a database using the Easysoft JDBC driver.
- Now add a
QueryDataSetcomponent to your application from the Data Express tab of the component palette.
- Click in the
queryproperty 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
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
DBDisposeMonitorto the application. This component will close the JDataStore when the window is closed.
- Choose File|Save All.
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:
contentPane (BorderLayout)in the component tree. Black sizing nibs around the edges of the panel in the designer show it is selected.
- Drop a
JdbNavToolBarcomponent into the designer at the top, center of the panel and set its constraints property to NORTH.
- Drop a
JdbStatusLabelcomponent into the designer at the bottom, center of the panel and set its constraints property to SOUTH.
- Drop a
TableScrollPanecomponent into the designer into the center of the panel, and set its constraints property to CENTER.
- Drop a
JdbTablecomponent into the center of
tableScrollPane1and set its
You’ll notice that the designer displays a table with live data. The application looks like this in the designer:
- Select Run|Run Project to run the application and browse the data set. The application looks like this when it is running:
- 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.
- Browsing database schema objects
- View, create, and modify database URLs
- Executing SQL statements
- Create, view, and edit data
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.
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.
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:
The JDBC Explorer browses databases listed in the Connection URL History List section of the
Additions are made to this list when you connect to a database using the
connection property editor of a
The following steps assume the URL is closed, and lists each task, briefly describing the steps needed to accomplish it:
View a URL
- In the left pane, select the URL to view. The Definition page appears in the right pane.
- Click the expand icon beside a database URL (or double-click it) in the left pane to see its contents.
Create a URL
- Select a URL or database in the left pane.
- Right-click to invoke the context menu.
- Choose New (or select File > New from the menu).
- 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.
- Browse to or enter the desired URL.
- On the Definitions page in the right pane, specify the
UserNameand any other desired properties.
- Click the Apply button on the toolbar to apply the connection parameters.
Modify a URL
- Select the URL to modify in the left pane. The Definitions page appears in the right pane.
- Edit settings on the Definitions page as desired.
- Click the Apply button on the toolbar to update the connection parameters.
Delete a URL
- Select the URL to delete in the left pane.
- Select File > Delete from the menu to remove the URL.
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:
- Open a database by selecting its URL in the left pane and entering user name and password if applicable.
- Select the database or one of its child nodes in the left pane.
- Click the Enter SQL tab in the right pane to display an edit box where you can enter or select an SQL statement.
- 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.
- 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.
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
- Select a table to view in the left pane.
- Click the Data page tab in the right pane to view a scrollable table of all data in the table.
- Use the toolbar buttons at the top of the table to scroll from record to record.
Edit a record
- Make sure that Request Live Queries in the View > Options menu is checked.
- Edit the record’s fields in the table.
- To post the edits to the local data set, select a different record in the table, or click the toolbar’s Post button.
- To cancel an edit before moving to another record, click the toolbar’s Cancel button or press
- To save your changes to the database, click the Save changes button.
Insert a new record
- Place the cursor on the row before which you wish to insert another row.
- Click the toolbar’s Insert button. A blank row appears.
- Enter data for each column. Move between columns with the mouse, or by tabbing to the next field.
- To post the insert to the local data set, select a different record in the table, or click the toolbar’s Post button.
- To cancel an insert before moving to another record, click the toolbar’s Cancel button or press
- To save an insert to the database, click the Save changes button.
Delete a record
- Place the cursor on the row you wish to delete.
- Click the toolbar’s Delete button.
Edits only take effect when they are applied. To apply edits and make changes permanent:
- Click the Post button on the toolbar. This posts the changes to the local data set only (not the database).
- Click the Save changes button to commit the edits to the database.