How To Setup a Text ODBC Data Source

Many types of databases have JDBC drivers available from the Internet (Oracle, MySQL, ...). Not all do, however. Microsoft created a “bridge” called ODBC so that software wouldn't need drivers for each type of database. You only need an ODBC driver, and MS provides ODBC support for most types of databases.

Java ships with an ODBC—JDBC driver. You write your Java programs to use ODBC databases, then configure your system's ODBC repository with the desired data souce. On Windows this is configured using a control pannel (or Administrative Tool).

In Windows open up the “Data Sources (ODBC)” control pannel. (This may be found under “Administrative Tools”.) The tabs you see next depend on your version of Windows. If you see it, click “User DSN” tab.

Note!  There are two ODBC systems on 64-bit Windows systems, ODBC32 and ODBC64.  If you use 32-bit Java on a 64-bit Windows system (we do at HCC), you must use ODBC32 drivers or your program won't work.  Unfortunately, the default on 64-bit Windows 7 is ODBC64.  To use ODBC32, you must launch the 32-bit utility, not found in the Start menu: %windir%\SysWOW64\odbcad32.exe.  (It may be helpful to rename the existing Start menu shortcut to “Data Sources (ODBC64)”, and to add a shortcut to the 32-bit tool with an appropriate name.)

Next, click the “Add...” button to create a new ODBC data source.

Here we are going to use a text file, one per table, in some directory, as the data source. Below is a file “Cups.txt” you can use. The table name is the file name. This table has three columns and has one line per record. Each record represents the number of cups of coffee a given student consumed on a given day. I put this file into a new directory named “Coffee”, which is also used for the DB name.

To create a new data source from a file, you need to use the correct driver for the type of the file. In our case we need “Microsoft Text Driver (*.txt; *.csc)”. After selecting the correct driver, click on “Finish”. (Note! The Microsoft ODBC Text Driver doesn't support all SQL operations. You need to use MS Access files, or some other ODBC supported database type, for full functionality. Of course, you don't have to use ODBC at all if you have the required JDBC driver.)

This brings up a dialog that allows you to configure your text data source. For the “Data Source Name”, I chose “Coffee”. You can add an optional description of the database next.

Next you need to select the directory that holds the database (text) files. You need to uncheck “Use Current Directory” before you can select one. Select your “Coffee” directory that contains “Cups.txt”.

Next click on the “Options>>” button. This allows you to define what file types to use for the tables. It is okay to click on the “Default (*.*)” checkbox.

Next click on the “Define Format...” button. From here you can define your columns types and names, for each table. You can select “Cups.txt” file. Next click the “Column Name Header” checkbox if the first row in the file contains the column names. Click on the “Guess” button to have the driver guess the name and type of the various columns. If it guesses wrong you can add, modify, or remove columns. Repeat for each file, and then click the “OK” button.

Back at the “ODBC Text Setup” dialog, just click “OK” to finish. Back at the “ODBC Data Source Administrator” dialog, click “OK” to finish.

If you examine the directory you will see a new file, “schema.ini”. This file describs your database, with one section per table (file), an entry for every column, and other information.

"Student", "Day", "Cups"
"Jay", "Mon", 2
"Robi", "Mon", 3
"Gregory", "Mon", 3
"Kelvy", "Tue", 5
"Robi", "Tue", 2
"Laura", "Tue", 2
"Pam", "Wed", 4
"Laura", "Thu", 3
"Jay", "Thu", 1
"Allen", "Fri", 4
"Kelvy", "Fri", 2
"Pam", "Fri", 3

[Cups.txt]
ColNameHeader=True
CharacterSet=OEM
Format=CSVDelimited
Col1=Student Char Width 32
Col2=Day Char Width 3
Col3=Cups Integer