ODBC without DSN

Using ODBC without DSN (Data Source Name)

Easy to administer data refresh to desktops

The Omniscope Database Connection wizard supports ODBC data sources. Normal use of ODBC connections entails configuring an ODBC data source on each client PC requiring connectivity to the database for refresh. In Windows XP, for example, this configuration is done on the client using Control Panel, Administrative Tools, Data Sources. When distributing an .IOK file to numerous desktops within an organisation, if 'refresh from source' functionality is desired, this could require the same ODBC data source to be manually configured on every PC... potentially time-consuming for central administrators

Fortunately, it is possible to configure a "DSN-less" ODBC database connection within Omniscope. This encapsulates the full database connection parameters within the IOK file, and (providing the ODBC database driver is available) requires no administration on each client PC. This allows Omniscope to refresh data from the underlying database automaticallywhenever the .IOK file is opened on the client PCs. Omniscope can also be refreshed by users 'on-demand' using Data > Refresh from source.

DSN-less ODBC Configuration

Start Omniscope, and choose File > Connect to database. The Database Connection Wizard will open. Select ODBC and click Next. You will see the ODBC configuration page with the field Data Source Name.
Normally, when using a user- or system- configured ODBC data source, you would enter the name of the data source (the DSN) here. Instead, to avoid configuring the data source on all client machines,
you must enter the full DSN-less ODBC connection string. This is a line of text containing name=value definitions separated by semi-colons. Any values containing spaces are enclosed in curly braces. This describes the parameters of the ODBC data source.

For example, to connect to a sample Access database, you might use the following in the Data Source Name (DSN) field:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\db1.mdb

All ODBC DSN-less connections begin with DRIVER= however all other parameters are database vendor-specific, although they often are similar.

Discovering the Connection String

To find out the connection string specific to your database vendor and configuration, use the Windows ODBC Data Source Administrator:

On a typical client PC, open Control Panel -> Administrative Tools -> Data Sources.
Select the File DSN tab and click Add.
Select the appropriate driver (e.g. "Microsoft Access Driver (*.mdb)") and click Next
Click Browse and choose where you want to save the .dsn file (this is a temporary file you are going to delete later).
Click Next then Finish.
You will be shown the vendor-specific ODBC setup dialog. For example, with Microsoft Access, you might only need to click Select and browse to an existing .mdb file before clicking OK.
Browse to the location of the .dsn file and open using Notepad.

In the DSN file you might see something similar to:

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\
DBQ=C:\db1.mdb

To convert the above to the full connection strring:

Omit the first [ODBC] line
Put curly braces around all values containing spaces
Put all name=value pairs on one line, separated by semicolons.

This gives you the full connection string. In this example, the string becomes:

DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;
MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\;DBQ=C:\db1.mdb

Note: This is more verbose than absolutely necessary, the following will suffice, in this example:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\db1.mdb

Finally, paste this connection string into the ODBC Data Source Name field of the Omniscope Database Connection wizard, as described above.

Examples:

The following examples have not been verified:

Oracle: DRIVER={Oracle ODBCDriver};UID=Kotzwinkle;PWD=whatever;DBQ=instl_alias;DBA=W

AS400: Driver={Client Access ODBC Driver (32-bit)};System=myAS400

Excel: Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\somepath\mySpreadsheet.xls;DefaultDir=c:\somepath

SQL Server: Driver={SQL Server};Server=MyServerName;Database=myDatabaseName

References

More information and examples for ODBC connection strings can be found on the following pages:

http://www.asp101.com/articles/john/connstring/default.asp (scroll down to ODBC DSN-less)

www.oracle.com/technology/docs/tech/windows/odbc/htdocs/817help/sqoraFormat_of_the_Connection_String_.htm

http://www.databasejournal.com/features/mssql/article.php/1491011

Knowledge Base