Database and ODBC

DTBuild can interact with a user-defined database. It uses ODBC, "Open Database Connectivity", to :

Allow user access to database table and field names,
Transmit data to the database,
Execute generated SQL (Structured Query Language) statements.

The user must :

Create the database,
Configure an ODBC "Data Source Name" for the database,
Connect the DTBuild definition to the ODBC data source.

ODBC allows software products to interact with a wide variety of Database Management Systems (DBMSs). The DBMS must provide a software component known as an ODBC "driver". Most DBMSs available on Windows platforms provide ODBC drivers.

A simple database can be created in a matter of minutes. Steps 2 and 3 are even simpler, once you know how ...

Creating a database

The user must provide the "target" database. This will typically involve using a DBMS (Database Management System) to create one or more tables.

A table is defined as a group of "columns", or "fields". Each field has attributes :

name
type
length
...

Here's how to create a database using Microsoft Access :

Select the "File/New" menu option and choose the Database icon,
Choose a name for your new database,
Select one of the "Create table" options,
Define your fields (this may require some thought, and revision!),
Save your new database / table - this creates a new file with the .MDB extension.

A few hints :

The field type will typically be "text", or "char". Pick "text" if you are uncertain what field type to use. Quantities will always be "numeric", but part "numbers" often contain dashes and letters, in which case they must be defined as "text" fields.
Ensure that all your fields are wide enough to accommodate the largest possible input. When estimating field length err on the side of making it too large, or make the field a variable-length "memo" field.
It is strongly recommended that you choose table and field names with no embedded blanks. Embedded blanks seem to cause problems for the MS Access ODBC driver. Avoiding embedded blanks in identifiers is a good general principle to follow as you navigate the computing minefield.
Configure fields to allow "null" values, or set "required" to "no", or "allow zero length". In some cases the entire record will be rejected if one of the incoming fields is empty and that field doesn't accept null values.

At times DTBuild will appear to scan the input correctly, but the target database will be empty or missing some expected data. The cause is very often a mismatched data type, or insufficient field width(s). A good general strategy is to "relax" the table initially - create all "memo" fields, or "text(255)", then tighten the table up after it's loading correctly.

Check the program's status box at the bottom after a run - if it says "View log for messages" there's something that needs attention. Check the log (View/Log option). The messages are sometimes cryptic, but helpful more often than not. If you're having trouble you can submit the log (if it's huge just send the top 50 / bottom 50 lines or so).

Contact INFO X & O for assistance with database issues.

Configuring an ODBC Data Source

The user must configure an ODBC "Data Source Name" (DSN) for the database. This is a simple process of choosing a label and applying it to the database. A Windows program: the ODBC Data Source Administrator, is used for this purpose.

The ODBC Data Source Administrator program can be started in several ways :

Choosing DTBuild menu option "Input/Output | ODBC Administrator",
Clicking on the Windows Control Panel "ODBC" icon,
Entering "ODBCAD32" and the Windows command prompt,
Consulting Windows Help and Support: search on "ODBC".

These options are presented because the ODBC Data Source Administrator can be difficult to locate on some systems. The program's file name is ODBCAD32.EXE, and it is often (but not always) in the \WINDOWS\SYSTEM32 directory. Contact INFO X & O if you have trouble finding it.

Once you are running the ODBC Data Source Administrator choose the "Add" option in the first dialog to add a new Data Source :

screen shot: ODBC Data Source Administrator

In the next dialog choose the appropriate ODBC driver for your DBMS :

screen shot: Create New Data Source dialog

The next dialog, which is DBMS-specific, allows you to specify the Data Source Name and the location of your database :

screen shot: ODBC Microsoft Access Setup

Once you have specified the Data Source Name, the driver, and the database location you are ready for the next step - connecting DTBuild to the Data Source you have just configured ...

Connecting DTBuild to the Data Source

After setting up the database and the ODBC data source, configure the new Data Source Name in DTBuild :

Select the Input/Output | Database menu item,
Press <F1> in the "Database" field to start the "Select Data Source" dialog,
Select the Data Source Name configured in the ODBC setup (probably under the "Machine Data Source" tab).

This will establish DTBuild's connection with the database and make the database table and field names visible within the DTBuild definition as "targets".

SQL statement for creating sample table "message" : SQLMessage.txt.

SQL statement that works with Microsoft Access : SQLMessageA.txt. Memo fields are used rather than char(n) (because MS Access fails to load the record if the character allocation for any field is exceeded).

DTBuild has been tested with Microsoft Access 2000-2003 and Microsoft Visual FoxPro 6.0, and should work with any DBMS that provides a suitable ODBC driver.

Home

Download

Tutorial

Help

Site index

Contact info