ODBC Connection Strings

Resolving The Problem

ODBC applications usually connect using an ODBC data source name (DSN). A DSN is a persistent object that contains the all the settings, other than a password, needed to make an ODBC connection. The ODBC specification also allows applications to connect using a connection string that consists of keyword/value pairs. Each setting in a IBM i Access data source has a matching connection string keyword. Applications connect through ODBC using either a DSN; a combination of DSN and a connection string; or using only a connection string.

DSN only Connections
The deprecated SQLConnect API requires the programmer to specify an ODBC data source (DSN). The DSN can be a User or a System data sources (also called "Machine DSNs" because they are stored in the Windows registry). Values set in the data source cannot be overridden using this API. The DSN must be created on each PC the application runs on by a user or through an API.

DSN Connections with overrides
SQLDriverConnect and SQLBrowseConnect are designed to allow applications to specify connection option values at run-time, optionally overwriting the keyword values specified in the ODBC data source (DSN). SQLDriverConnect and SQLBrowseConnect can accept an input connection string as in the example below:

DSN=S10TR014;UID=Bruce;PWD=MYPWD;DBQ=Testlib;RECBLOCK=1;BLOCKSIZE=256;NAM=1;DFT=4;DSP=0;TFT=1;TSP=0;DEC=0

In this type of connection the DSN keyword (DSN=S10TR014; in the example) is required. All other keywords are driver-defined keywords and are optional. Any options not specified on the connection string will default to the values found in the ODBC data source.

Use of the DSN keyword implies that a "Machine DSN" is being used. Those are DSNs which are stored in the system registry. The other type of DSN is a "File DSN". In this type of DSN, the DSN keys are stored in a file either on the PC or a server. To use a File DSN with one of the ODBC connection functions you would provide the path to the file using a drive letter or UNC path like so:

FILEDSN=\\S10TR014\OURDSNS\TESTDSN1.DSN

DSN-less ODBC Connections

There is another type of ODBC connection known as a DSN-less connection. With this type of connection, an ODBC data source is not specified on the SQLDriverConnect. Rather, an ODBC driver and system to connect to are specified and all required parameters are provided on the connection string. This prevents the user from changing any ODBC connection options via the ODBC data source.

The following keywords are required on the connection string for a DSN-less connection:

DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=myAS400;

Note that the IBM i Access ODBC driver is registered with different names as rebranding has occurred over the years. So on a current client any of the following driver names would work:
{Client Access ODBC Driver (32-bit)}
{IBM i Access ODBC Driver}
{iSeries Access ODBC Driver}
The registered driver names can be found on the Drivers tab of the ODBC Data Source Administrator in Windows.

Connection String keywords
For a list of valid keywords that can be used on a connection string see the online IBM i Knowledge Center then search on "ODBC Connection String".