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.
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:
All ODBC DSN-less connections begin with DRIVER= however all other parameters are database vendor-specific, although they often are similar.DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\db1.mdb
[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:
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.
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
http://www.asp101.com/articles/john/connstring/default.asp [1] (scroll down to ODBC DSN-less)
http://www.databasejournal.com/features/mssql/article.php/1491011 [3]
Links:
[1] http://www.asp101.com/articles/john/connstring/default.asp
[2] http://www.oracle.com/technology/docs/tech/windows/odbc/htdocs/817help/sqoraFormat_of_the_Connection_String_.htm
[3] http://www.databasejournal.com/features/mssql/article.php/1491011