DSN Files and IRI Software

by Sean James

A DSN (database source name) file allows multiple users to connect to a database with information in a flat file. It is used by the database client program — in this case, software in the IRI Workbench — to connect to a database.

The DSN file describes properties such as: the data source name and directory, the connection driver, the server address, user ID, and a password. It is used by ODBC drivers to connect to any database supporting that protocol. Typically stored as plain text, DSN files offer:

Convenience – they eliminate the need to configure connections locally.
Portability – they can be shared among different users and easily changed.
Security – they can be saved in repositories, like Git, with restricted access.

The steps below show how to create and use DSN files with IRI job scripts (CoSort SortCL, etc.) when /PROCESS=ODBC is used to process data in relational databases.

Step 1 – Obtain and install the ODBC driver for your client and server

To build a DSN file, ensure you have the right ODBC driver for your database and its server; e.g., MySQL on Windows. For this description, assume that the database and server are on a local machine.

Step 2 – Locate and inspect the odbcinst.ini (connector) file

In each case, you need an odbcinst.ini file to serve as a connector between your ODBC driver library and the DSN file that refers to it. That ini file contains the location/link information the DSN file requires.

I am running MySQL on a Windows 7 PC. With most other operating systems, these steps may be different, and can be modified with information found on searches that explain how to connect to different kinds of servers.

My odbcinst.ini file happens to be in C:\Windows. If you cannot find it, you can get it from your chosen server’s official site, if they support installing the information in the odbcinst.ini file.

The following odbcinst.ini file was created by MySQL using their connector installation.

You can also manually enter the same details you see in mine:

[MySQL ODBC 5.3 Unicode Driver (32 bit)]
Driver=C:\Program Files (x86)\MySQL\Connector ODBC 5.3\myodbc5w.dll
Setup=C:\Program Files (x86)\MySQL\Connector ODBC 5.3\myodbc5S.dll
32Bit=1
[MySQL ODBC 5.3 ANSI Driver (32 bit)]
Driver=C:\Program Files (x86)\MySQL\Connector ODBC 5.3\myodbc5a.dll
Setup=C:\Program Files (x86)\MySQL\Connector ODBC 5.3\myodbc5S.dll
32Bit=1

Each operating system will be different, and this file can be set up in multiple ways. As an example, the following was entered by a user in an odbcinst.ini file on Linux:

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1

Once your connector file is properly configured, move to the next step:

Step 3 – Create the DSN File

For this example, it is assumed there is a MySQL database called “clients” that has a table called “loyal” containing names and phone numbers to track loyal clients. I entered the DSN variable manually in my example, but the Windows ODBC administrator utility can create a DSN file for you via the File DSN tab.

The minimum variables required are:

[ODBC]
DRIVER=
DATABASE=
SERVER=

The DRIVER variable comes from the odbcinst.ini file. In the Windows example above, you would enter MySQL ODBC 5.3 Unicode Driver (32bit).

The DATABASE is the name of the database to be accessed, in this case, “clients.”

The SERVER is the database location. In this case, the location is “localhost” (as the database is on the local machine).

The completed DSN file will contain:

[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver (32 bit)
DATABASE=clients
SERVER=localhost

Note that the DRIVER specification must exactly match the text in the bracketed header applicable to the driver in the odbcinst.ini file. The DSN file must contain the details for the database server connection, whether the executable runs on the job design client or a production/database server.

A DSN file can have more information. In this example, the three lines that will be added are: UID, PASSWORD, and PORT. The UID is any user ID that has access to the server. Here, the UID is root, and the PASSWORD is “mypassword.” I used the default MySQL port, 3306.

The updated DSN file will contain:

[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver
DATABASE=clients
SERVER=localhost
UID=root
PASSWORD=mypassword
PORT=3306

The file can now be saved under any name with a .dsn extension. I specified TEST.DSN.

Any IRI job script (based on the CoSort SortCL program, including NextForm and FieldShield), can use the DSN file information when /PROCESS=ODBC is specified and the input or output file declaration contains the FILEDSN parameter set equal to the .DSN file.

This SortCL job script uses the DSN file above:

/INFILE="loyal;FILEDSN=C:\Users\LocalUser\Documents\Test.dsn;"
/PROCESS=ODBC
/ALIAS=loyal
/FIELD=(NAME, TYPE=ASCII, POSITION=1, SEPARATOR="\t", EXT_FIELD="name", PRECISION=0)
/FIELD=(PHONE, TYPE=ASCII, POSITION=2, SEPARATOR="\t", EXT_FIELD="phone", PRECISION=0)
/REPORT
/OUTFILE=stdout
/PROCESS=RECORD
/FIELD=(NAME, TYPE=ASCII, POSITION=1, SEPARATOR="\t", EXT_FIELD="name", PRECISION=0)
/FIELD=(PHONE, TYPE=ASCII, POSITION=2, SEPARATOR="\t", EXT_FIELD="phone", PRECISION=0)

As the example above illustrates, the absolute path to the DSN file must be specified.

The file DSNs can be registered in the Data Connection Registry just like all other ODBC connections. Go to Preferences > Data Connection Registry > Add. Select File DSN and enter the location of your file. This connection can then be used in wizards throughout Workbench.