Omnis Technical Note TNSQ0015 July 2007

ODBC Administration with Omnis Studio

For Omnis Studio 4.2/4.3 and later
By Gary Ashford


The ODBC DAM included with Omnis Studio 4.3 includes additional methods necessary to enable administration of ODBC System, User and File data sources as well as ODBC drivers. These functions are normally performed by the ODBC Administrator utilitiy under Windows and Mac OS X. This technote briefly describes how many of these functions can now be performed from an Omnis library. 
An example library is also provided at the end of this page.

New Features

The following session methods have been added to the ODBC DAM.
These features are also documented in the What's New in Studio 4.3? update manual.
(Please note that Omnis Studio 4.3 DAMs are also compatible with Omnis Studio 4.2.)


$getdrivers (lResult) retrieves a list of all ODBC drivers installed on the system. lResult is populated with the list of drivers installed and is defined with the following character columns:
DriverName -The alternate driver name (i.e. the descriptive name)
Version -The version string reported by the driver
CompanyName -The Company name embedded within the driver file (Win32 only)
FileName -The physical path and file name of the driver

Example: Do tSess.$getdrivers(iDriverList) Returns #F


$getdatasources(lResult, kDSNMode) can be used to retirieve a list of
ODBC System or User DSNs.
On return, lResult is defined with two character columns:
DSNName -The User assigned name for the data source
Driver -The alternate name of the driver associated with the data source

Example: Do tSess.$getdatasources(iDSNList,kODBCSystemDSN) Returns #F


$getinfo(lResult, cDSNName, kDSNMode) retrieves the information defined for the specified data source or driver as a list of keyword-value pairs. kDSNMode should be passed as either kODBCSystemDSN, kODBCUserDSN or kODBCDriverInfo. $getinfo() does not support File DSNs for which standard FileOps methods can be used to read/modify as required.
On return, lResult is defined with the following character columns:
KeyWord -The name of the DSN/driver attribute
Value -The value of the DSN/driver attribute

Example: Do tSess.$getinfo(lDSNInfo,lHostname,kODBCUserDSN) Returns #F


$setinfo(cDSNName, kDSNMode, lData) writes the information contained in lData to the specified Data source or Driver key in the system information. lData should be defined with Keyword and Value columns as returned by $getinfo().
If kDSNMode is kODBCDriverInfo, this has the effect of modifying system information for the specified driver. cDSNName should contain the descriptive name of the ODBC Driver as opposed to the physical file name.
If kDSNMode is kODBCSystemDSN or kODBCUserDSN, this has the effect of modifying the specified data source.
$setinfo() does not register a new data source or driver. To properly create a data source; use the $configdsn() method instead. To properly register a driver, you should refer to the vendor's installation program.

Example: Do tSess.$setinfo(iDSNName,kODBCUserDSN,lDSNinfo) Returns #F


$configdsn(kDSNMode, kRequestType, cDriverName , lAttributes ) allows the specified datasource to be created, modified or removed.
kDSNMode should be either kODBCSystemDSN or kODBCUserDSN. $configdsn() does not support configuration of File DSNs- for which an alternative strategy is provided. kRequestType should be passed as either kODBCAddDSN, kODBCModifyDSN or kODBCRemoveDSN. cDriverName should correspond with the descriptive name of the driver (i.e. not the physical file name).
lAttributes should be defined with two character columns and is used to pass keyword-value pairs to the driver manager sufficient to perform the required action. Usually this involves adding a single line to the list to identify the DSN to be created/modified/removed, e.g.
KeyWord Value
DSN        dsnname
but can also include other keywords that are allowed by the driver.
When $uselogonprompt is set to kODBCPromptNever, this prevents $configdsn() from opening setup dialogues. The DSN is created/modified silently using values read from the attribute list instead.

Example: Do sessObj.$configdsn(kODBCUserDSN,kODBCAddDSN,'SQL Server',lAttribList) Returns #F


$getoption(kOption, cAttribute) allows the value of an ODBC configuration attribute to be retrieved.
kOption should be passed as one of the following constants:
kODBCTrace - Requests the TRACE on/off flag
kODBCTraceLib - Requests the name and path to the ODBC trace library
kODBCTraceFile - Requests the name and path to the ODBC trace log
kODBCFileDSNDir - Requests the default directory containing file DSNs
kODBCPerfMon - Requests the Performance monitoring on/off flag
kODBCRetryWait -Requests the connection pool RetryWait timeout
On return, cAttribute contains the value of the requested option as a character string.

Example: Do sessObj.$getoption(kODBCFileDSNDir,iFileDSNDir) Returns #F


$setoption(kOption, cAttribute) allows the value of an ODBC configuration attribute to be modified.
kOption should be either kODBCTrace, kODBCTraceLib, kODBCTraceFile, kODBCFileDSNDir, kODBCPerfMon or kODBCRetryWait. cAttribute should contain a character string representing the new value for the specified configuration option.

Example: Do sessObj.$setoption(kODBCTraceFile,iTraceFile) Returns #F

In addition, the session property; $uselogonprompt now accepts the following constant values:
kODBCPromptNever (0), kODBCPromptComplete (1) & kODBCPromptAlways (2).
kODBCPromptAlways is used to force the ODBC Administrator library to display a configuration dialogue for example- when connecting to File DSNs.
As you can see from above, several new constants have also been added to help with ODBC administration. These can be found in the Omnis Catalog (F9) listed under "ODBCDAM".

Example Library

The use of these methods is best illustrated by the following example.

The odbcadmin.lbs library puts all of these attributes to use to create an ODBC Administrator- similar to those provided by Microsoft and Apple, which can either be used stand-alone or as the starting point for your own ODBC administration tool.

                       odbcadmin.lbs (92kb)

Note: If using Omnis Studio 4.2, please remember to replace your ODBC DAM before trying the library!