Opening Windows Databases in Weka

November 1st, 2008

This documentation is superceded by the Wiki article Windows Databases.

Outdated documentation:

A common query we get from our users is how to open an Accesss Database in the Weka Explorer. This page is intended as a guide to help you achieve this. It is a complicated process and we cannot guarantee that it will work for you. The process described makes use of the JDBC-ODBC bridge that is part of Sun's JRE 1.3, but is still experimental.

Important Note: There is an incompatibility problem with the Weka version 3-2-0 and earlier that means that it will fail. This problem has been fixed in versions 3-2-1 and 3-3 or greater. You will know the problem exists if you get the error 'Resultset is closed'. The easiest and most recommended fix is to ugrade to a newer version of Weka. This problem can be fixed in versions below 3-2-1 by replacing the InstanceQuery.class file in Weka with this patched version. If you need it you can also get the patched souce file.
To install the patch, you will need to need to overwrite the files that are already present. One way of doing this would be to extract the weka.jar file. If for example you extracted the contents of the .jar into the directory c:\weka3-2 then you can overwrite the InstanceQuery.class file sitting in c:\weka3-2\weka\experiment\. To launch the Weka Explorer, you can open up a command prompt and execute the following command:

  java -cp c:\weka-3-2\ weka.gui.explorer.Explorer

Note: There is an easier way to get your database table into Weka - simply save it is a CSV file. The Weka explorer is able to load these files directly.

The following instructions are for Weka 3.2 upwards and Windows 2000. Under other Windows versions there may be slight differences.

Extracting .jar files

.jar files are Java archives that can be extracted by tools such as some versions of WinZip, or by Sun's jar tools. See this page for more info.

Step 1: Create a User DSN

Note: Make sure your database is not open in another application before following the steps below.
  1. Go to the Control Panel
  2. Choose Adminstrative Tools
  3. Choose Data Sources (ODBC)
  4. At the User DSN tab, choose Add...
  5. Choose the Microsoft Access driver and click Finish. At this point you could of course choose another driver instead for a different type of database.
  6. Give the source a name by typing it into the Data Source Name field
  7. In the Database section, choose Select...
  8. Browse to find your database file, select it and click OK
  9. Click OK to finalize your DSN
  10. Your DSN should now be listed in the User Data Sources list

Step 2: Set up the DatabaseUtils.props file

You will need to create a file called DatabaseUtils.props. This file already exists under the path /weka/experiment/ in the Weka.jar file that is part of the Weka download.

This file needs to be recognized when the Explorer starts. You can achieve this by making sure it is in the working directory, or by replacing the version the already exists in the /weka/experiment directory. A way of achieving the second alternative would be to extract the contents of the weka.jar, and setting your CLASSPATH to point to the directory where /weka resides rather that the .jar file. (As mentioned above.)

The file is a text file that needs to contain the following lines:

jdbcDriver=sun.jdbc.odbc.JdbcOdbcDriver
jdbcURL=jdbc:odbc:dbname
where dbname is the name you gave the user DSN. (This can also be changed once the Explorer is running.)

Step 3: Open the database

  1. Start up the Weka Explorer. If you want to be sure that the DatabaseUtils.props file is in the current path, you can open a command prompt window, change to the directory where the DatabaseUtils.props file is located, make sure your CLASSPATH environment variable is set correctly (or set it with the -cp option to java) and launch the Explorer with the following command:
      java weka.gui.explorer.Explorer
    
  2. Choose Open DB...
  3. Edit the query field to read 'select * from tablename' where tablename is the name of the database table you want to read, or you could put a more complicated SQL query here instead.
  4. The databaseURL should read 'jdbc:odbc:dbname' where dbname is the name you gave the user DSN.
  5. Click OK

At this point the data should be read from the database.


Send your comments/queries/suggestions to wekasupport@cs.waikato.ac.nz