Accessing ODBC data sources from JBuilder

Contents

Adding the Easysoft JDBC-ODBC Bridge client

Adding the Easysoft JDBC-ODBC Bridge client to JBuilder

The JDBC-ODBC Bridge is a client/server product. Once you've installed the JDBC-ODBC Bridge server, you need to add the JDBC-ODBC Bridge client (EJOB.jar) to JBuilder.

Download EJOB.jar from 

http://yourmachine:8031/

where yourmachine is the machine on which the JDBC-ODBC Bridge server is running. Download EJOB.jar from the Information page to the JBuilder machine. Then add EJOB.jar to JBuilder.

  1. In JBuilder, choose Tools > Enterprise Setup. Choose the Database Drivers tab, which displays .config files for all the currently known database drivers. Choose Add to add a new driver.
  2. Choose 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's simpler to do it all here.

  4. Enter a name and select a location for the new file in the Create New Library dialog box.
  5. Choose 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 works, as JBuilder extracts the information it needs.
  6. Choose OK to close the file browser. This displays the new library at the bottom of the library list and selects it
  7. Choose 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. For example, InterClient.config.
  8. Select the new .config file in the database driver list and choose OK.

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

  9. Close and restart JBuilder to add the new driver 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 JBuilder 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 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. Choose the Required Libraries tab on the Paths page, and then choose Add.
  4. Select the new JDBC driver from the library list and choose OK
  5. Choose 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 described 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. Then set its connection property so that it can use that driver to access the data.

Connecting to a database

Connecting to an ODBC data source in JBuilder

The Database component handles the JDBC connection to a database. JDBC is the Java Database API and is a library of components and classes that are collected in the java.sql package, which represents a generic, low-level SQL database access framework.

The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, and so on. It allows a Java programmer to run SQL statements and process the results. JDBC is the primary API for database access in Java. The JDBC API is implemented with a Driver Manager that can support multiple drivers connecting to different databases.

JBuilder uses the JDBC API to access information stored in databases. Many of JBuilder's data access components and classes use the JDBC API. Therefore, these classes must be properly installed 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 of 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 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 a new application to the project.
  3. Open the UI designer by selecting the file Frame1.java in the Content pane. Then select the Design tab at the bottom of the AppBrowser.
  4. Select the DataExpress tab from the component palette and choose 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.

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

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

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

Note You need to make sure that your JDBC-ODBC Bridge server is running on your system.

  1. Select database1 in the Component tree.
  2. Select the connection property's value in the Inspector, and choose 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's located on the same machine as the JDBC application, use: 
    jdbc:easysoft:ODBC DSN

    If not, use:

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

    If you now choose the Test connection button, you'll be prompted to choose an ODBC data source to connect to.

    Note It's 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.

    Choose 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.

    Choose OK to exit the dialog box and write the connection properties to the source code following a successful connection.

    The source code, if the example above is followed, is 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 closes 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 metadata and database schema objects in the JDataStore. You can also execute SQL statements and browse and edit data in existing tables.

Common connection error messages

Unable to locate the Easysoft JDBC-ODBC Bridge client

The Easysoft JDBC-ODBC Bridge client 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 client has not been added to the CLASSPATH. Add EJOB.jar 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 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 statements for saving changes back to the data source after fetching the data.

The following of the QueryDescriptor properties affect query execution. These properties can be set 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 parameterised queries.
executeOnOpen Causes the QueryDataSet to execute the query when it's 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 how to load data into the data set. Options are:
  • Load All Rows

    Load all data up front.

  • Load Rows Asynchronously

    Fetch DataSet rows on a separate thread. This allows the DataSet data to be accessed and displayed while the QueryDataSet fetches more rows.

  • 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.

Use a QueryDataSet 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.

  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 opens in the Content pane.
  3. Select the Design tab to activate the UI designer.
  4. Choose 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 choose 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. Choose 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, choose OK.

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

    For more information on connecting to databases, refer to 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. Choose the query property value field in the Inspector for queryDataSet1, then choose the ellipsis button to open the Query property editor.

    Set the following properties:

    Property Name Value
    Database database1
    SQL Statement 
    select * from employee

    Choose Test Query to ensure that the query is runnable. When the area beneath the button displays Success, choose OK to close the dialog box.

    Choose 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.

  6. Select Run > Run Project to run the application and browse the data set.
  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.

Using JDBC Explorer

Introduction

The JDBC Explorer is a hierarchical database browser that also allows you to edit data. It presents JDBC-based metadata 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, choose 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:

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. Choose the expand icon beside a database URL (or double-click it) in the left pane to display 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. Choose 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. Choose 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.

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. Choose 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. Choose 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 the steps needed to accomplish each task:

View table data

  1. Select a table to view in the left pane.
  2. Choose 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. Choose 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. Choose the toolbar's Delete button.

Edits only take effect when they are applied. To apply edits and make changes permanent:

  1. Choose the Post button on the toolbar. This posts the changes to the local data set only (not the database).
  2. Choose the Save changes button to commit the edits to the database.