The Microsoft ODBC 2.0, 3.0 & 3.5 Driver Manager (depicted below) allows the creation of User, System, and File DSNs.
A User DSN will only be available to the user who creates the data source; a System DSN will be available to the whole system so that any user, including the system account, will be able to use that data source; A File DSN is a special 'mobile' data source that stores the data source information associated with the ODBC Driver in a file that is sharable.
The steps for creating a DSN are as follows:-
From the desired tab (User; System; File) press the Add button to begin creating a new Data source.
|
Pick the ODBC Driver to be used to create your ODBC DSN. In this example the Driver Labeled "OpenLink Lite for DB2 (32 Bit)" is highlighted.
|
Once you have selected the appropriate OpenLink Lite Driver, you will be presented with the OpenLink ODBC Data Source Configuration Wizard for the driver selected. The Wizard has several frames, most of which require you to provide information.
The first tab is the Data Source Tab. The following parameters are common to all databases.
|
(Data source) The name of the ODBC DSN; this is how you will interact with the OpenLink Lite ODBC Driver from within ODBC-compliant applications once your ODBC DSN has been created.
Additional information that further describes the ODBC DSN that you are creating.
In the second frame of the wizard, you enter a database alias for the underlying communications layer of the database, and a username and password. If you select the "Connect now to verify that all settings are correct" check-box, pressing the Next> button will verify that DSN can connect.
|
The third frame contains OpenLink-specific parameters for the DSN connection; here you can set the connection to be read-only, have it defer the fetching of long (BLOB) data, disable interactive login, set a row-buffer size (the number of records to be transported over the network in a single network hop), set a file containing SQL statements to run on login, set the dynamic cursor sensitivity level, and enable logging to a file.
|
The fourth frame contains more custom parameters: you can enable changes for the MS Jet Engine, disable autocommitting, and disable the rowset size limit. You can also set the SQL_DBMS_NAME (required for some applications, notably Access).
|
The fifth and final frame in the wizard shows the details of the connection - the driver name and version and DSN configuration parameters; it also gives you the option to test the data-source.
|
(Data source) The name of the ODBC DSN; this is how you will interact with the OpenLink Lite ODBC Driver from within ODBC-compliant applications once your ODBC DSN has been created.
Additional information that further describes the ODBC DSN that you are creating.
Allows you to define a limit on the maximum number of rows to returned from a query. The default value of 0 means no limit.
Lets you specify a file containing SQL statements that will be run against the database upon connection, automatically.
Extra support or altered functionality for better compatibility with Microsoft Jet using applications such as Microsoft Access.
Change the default commit behaviour of the OpenLink Lite Driver. The default mode is AutoCommit mode (box unchecked).
Disable the limitation enforced by the cursor library. The limitation is enforced by default to prevent the Driver claiming all available memory in the event that a resultset is generated from an erroneous query is very large. The limit is normally never reached.
Enables or disables the row version cache used with dynamic cursors.
When dynamic cursor sensitivity is set high, the Cursor Library calculates checksums for each row in the current rowset and compares these with the checksums (if any) already stored in the row version cache for the same rows when fetched previously. If the checksums differ for a row, the row has been updated since it was last fetched and the row status flag is set to SQL_ROW_UPDATED. The row version cache is then updated with the latest checksums for the rowset.
From the user's point of view, the only visible difference between the two sensitivity settings is that a row status flag can never be set to SQL_ROW_UPDATED when the cursor sensitivity is low. (The row status is instead displayed as SQL_ROW_SUCCESS.) In all other respects, performance aside, the two settings are the same - deleted rows don't appear in the rowset, updates to the row since the row was last fetched are reflected in the row data, and inserted rows appear in the rowset if their keys fall within the span of the rowset.
If your application does not need to detect the row status SQL_ROW_UPDATED, you should leave the 'High Cursor Sensitivity' checkbox unchecked, as performance is improved. The calculation and comparison of checksums for each row fetched carries an overhead.
If this option is enabled, the table oplrvc must have been created beforehand using the appropriate script for the target database.
This attribute specifies the number of records to be transported over the network in a single network hop. Values can range from 1 to 99.
This attribute specifies the default username to be used when attempting to make a connection. You may still override this at run time.
Suppress the ODBC "Username" and "Password" login dialog box when interacting with your ODBC DSN from within an ODBC compliant application.
Specify whether the connection is to be "Read-only". Make sure the checkbox is unchecked to request a "Read/Write" connection.
|
|
|
|
The DB2 Connect Alias for the database to which you wish to connect.
|
|
|
|
|
|
|
|
Enter details of the Informix server.
The name of the Informix server that you want to communicate with.
The network hostname of the machine hosting the Informix server.
The name of the database on the server that you want to connect to.
The informix network protocol identifier.
The service name as configured in the winnt\system32\drivers\etc\services or windows\services file
Define the client locale. This takes the form:
[language_territory.]codeset[@modifier]
For example:
57371
en_us.57372
en_us.utf8@dict
An Informix 9 Lite driver should use UTF-8 as the codeset. The language and territory should not matter; so it should be possible, for example, to use French (fr_fr) or American English (en_us).
For Informix clients on Windows, the client locale is typically set through SetNet32. Rather than rely on the SetNet32 settings, our Lite driver instead sets the client locale at runtime.
It is possible to use a codeset number (Eg. 57372) rather than a codeset name (UTF8) to specify UTF-8 as the codeset. Either form can be used. The registry file included in an Informix client installation lists the supported code sets and the correspondence between codeset names and numbers.
The optional modifier has a maximum of four alphanumeric characters. This specification modifies the cultural-convention settings that the language and territory settings imply. The modifier usually indicates a special type of localized order that the locale supports. For example, you can set @modifier to specify dictionary or telephone book collation order.
If you enter the username and password here, and check the "Connect now" checkbox, proceeding to `Next>' will make a test connection to verify the above parameters.
In the case of an OpenLink driver, this parameter is an ODBC Datasource Name (DSN): see the XA documentation for more about this.
|
Enter options for the Informix connection:
If set, then colums are padded to full width by using the space character.
determine the connection parallelization mode.
If set, then the database will always appear as online.
|
|
|
|
|
Enter details here about the Ingres server.
Takes the form <vnode>::<dbname> where vnode is the name of the virtual node configured either in the Ingres V(isual)DBA or Ingres Netutil utility, and dbname is the name of the database at the vnode destination that you want to connect to.
Ingres allows you to connect as a particular role or effective user. From iidbdb you could execute:
create role myrole with password=myrolepwd;\g
to create a role; it is then this role and password you would enter in the above dialog.
|
|
|
|
|
Enter details here for the SQL*NET.
This needs to be a valid SQL*Net connect string. Typically this will only need to be the TNS Name as defined using the Oracle Easy Net configuration utility.
This lets you select the SQL*Net DLL to be used. AutoScan is the default and best option. If this does not work then try and pick the option that seems to best suite your version of Oracle. If you are using Oracle 8i then use 'oci.dll'.
In the case of an OpenLink driver, this parameter is an ODBC Datasource Name (DSN): see the XA documentation for more about this.
|
contains the options:
You must run the ODBCCATx.SQL script against your database as user internal before this option can be checked! Enabling this gives best functionality and support especially for ODBC catalog calls.
These scripts exist for each version of Oracle supported, the files "odbccat6.sql", "odbccat7.sql", and "odbccat8.sql" representing Oracle versions 6 up to version 8 respectively. These scripts are to be applied to your Oracle instance to enable efficient and extended functionality between OpenLink and Oracle when handling ODBC, JDBC, UDBC, and OLE-DB catalog calls such as SQLForeignKeys() and SQLPrimaryKeys() functions. These functions have significant impact on the performance of your OpenLink clients.
To run these scripts you need to start the Oracle server manager (svrmgr or sqldba if you do this from the command line). Connect as internal and run the script by locating the relevant script file as you would any other Oracle SQL script file.
Enable 'strict' parameter use.
|
When OpenLink's TAF support is enabled, in the event that a failover attempt fails, the Lite driver or Multi-Tier agent will instruct Oracle to retry. By default, the maximum number of failover attempts is 10 and the interval between retry attempts is 10 seconds. The default values can be overridden.
See the Oracle RAC/TAF documentation for more.
|
|
|
|
|
|
Enter details here to specify Progress connection information.
Name of the Progress database.
Domain name or IP number of the machine hosting the database.
TCP port on which the database is listening.
|
|
|
|
|
Enter details here to specify Progress connection information.
Any Progress server startup options are accepted in this box. Note: These options are required for the Small Client driver only.
Typical options could be:
-SV -S <service> -H <hostname> -N TCP
Any valid Progress Database connection options. See your Progress manual for a complete list of accepted options.
When using the Small Client driver, enter the following minimum database information in this box:
-db <dbname>
When using the Large Client driver, enter the following minimum database information in this box:
-db <dbname> -S <service> -H <hostname> -N <protocol>
Note: The <service> entry must meet the following requirements:
To connect to multiple databases and make use of array fields see the tableview guide
The full path and file name of the .dat 'tableview' file created after running the setup.p applications. See tableview guide for more information.
|
Enter the minimum and maximum sizes for a char(N) field here
This is the default precision and scale for anonymous numeric fields in the resultset.
Check this to enable a workaround for Business Objects' handling of the space-character in quoting an identifier.
|
|
|
Utility and functionality for the Progress Driver that enables the user to connect to multiple databases and use array fields. This is only applicable to drivers not built against Progress SQL92 Libraries.
After installing a Progress Lite Driver you should find the following files in the lite32 directory:
setup.p setup.i oplrvc1.p oplrvc2.p
setup.p is a Progress script program. When the program runs it will ask you what databases you wish to connect to and generate a catalog file of all the databases and tables within into a tableview file (.dat), and will create its associated parameter file (.pf).
Doing this allows you to connect to multiple databases, allows you to see all the tables in all databases from SQLTables, and allows you to use arrays fields through ODBC.
Prior to running this program you must ensure that all database servers for the databases you wish to include are running since the program will need to connect to them.
Make sure that your $DLC environment variable is set correctly to point to your DLC directory.
e.g. DLC=/dbs/progress/dlc
export DLC
Go to the openlink/lite32 directory, and type the following:
$DLC/bin/pro -p setup.p
This may vary between versions. In Progress 6, pro is in the dlc directory not dlc/bin.
You may also need to set a PROPATH environment variable to include the current working directory and the directory containing the setup.p utility files.
You will get the following screens:
. @@@@@@ @@@@@@ @@@@@@@ @@@@@ @@@@@@ @@@@@@@ @@@@@ @@@@@
@ @ @ @ @ @ @ @ @ @ @ @ @ @ @
@ @ @ @ @ @ @ @ @ @ @ @
@@@@@@ @@@@@@ @ @ @ @@@@ @@@@@@ @@@@@ @@@@@ @@@@@
@ @ @ @ @ @ @ @ @ @ @ @
@ @ @ @ @ @ @ @ @ @ @ @ @ @
@ @ @ @@@@@@@ @@@@@ @ @ @@@@@@@ @@@@@ @@@@@
Progress Software Corporation
14 Oak Park
Bedford, Massachusetts 01730
617-280-4000
PROGRESS is a registered trademark of Progress Software Corporation
Copyright 1984,1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995
by Progress Software Corporation
All Rights Reserved
PROGRESS Version 7.3C as of Thu Jun 29 15:05:14 EDT 1995
Followed by:
+-----------------------------------------------------+ | Enter a number of databases you want to connect to. | | The first database is your master database. | +-----------------------------------------------------+ +-----------------------------------------------------------------------------+ | # DbName Connect String OK| |-- ------------------------------ ---------------------------------------- --| | 1 ______________________________ ________________________________________ __| | | | | | | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Enter data or press PF4 to end.
At this point you will need to give the program information to contact the databases you want to include here. As shown below.
+-----------------------------------------------------+ | Enter a number of databases you want to connect to. | | The first database is your master database. | +-----------------------------------------------------+ +-----------------------------------------------------------------------------+ | # DbName Connect String OK| |-- ------------------------------ ---------------------------------------- --| | 1 /users/progress/dbs/pro7test__ -N tcp -H 194.152.95.26 -S pro7test_____ __| | | | | | | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Enter data or press PF4 to end.
After entering the line of information press return. There will be a little pause while the program checks that the database server is running and can be contacted. When this is verified OK will appear at the end of the line.
+-----------------------------------------------------+ | Enter a number of databases you want to connect to. | | The first database is your master database. | +-----------------------------------------------------+ +-----------------------------------------------------------------------------+ | # DbName Connect String OK| |-- ------------------------------ ---------------------------------------- --| | 1 /users/progress/dbs/pro7test__ -N tcp -H 194.152.95.26 -S pro7test_____ OK| | | | | | | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Enter data or press PF4 to end.
The cursor will move to the next line. Repeat the above procedure for each database. Press PF4 when finished. You will then need to supply the name for the .dat and .pf files to be created.
+-----------------------------------------------------+ | Enter a number of databases you want to connect to. | | The first database is your master database. | +-----------------------------------------------------+ +-----------------------------------------------------------------------------+ | # DbName Connect String OK| |-- ------------------------------ ---------------------------------------- --| | 1 /users/progress/dbs/pro7test__ -N tcp -H 194.152.95.26 -S pro7test_____ OK| | 2 ______________________________ ________________________________________ __| | | | | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Basename for .pf and .dat files_____________________ Enter data or press PF4 to end.
After a brief period the program will have finished:
+-----------------------------------------------------+ | Enter a number of databases you want to connect to. | | The first database is your master database. | +-----------------------------------------------------+ +-----------------------------------------------------------------------------+ | # DbName Connect String OK| |-- ------------------------------ ---------------------------------------- --| | 1 /users/progress/dbs/pro7test__ -N tcp -H 194.152.95.26 -S pro7test_____ OK| | 2 ______________________________ ________________________________________ __| | | | | | | | | | | | | | | | | +-----------------------------------------------------------------------------+ Creating .pf file to use Creating .dat file with table information Procedure Complete. Press space bar to continue.
Now you have to integrate these files into your system.
In the Lite Driver setup dialog box you will find an option box called Table View. In this field place:
<full path and file name to .dat file>
Now you can use in the Connection Options, only:
-pf <full path and file name to .pf file>
To resolve you connection parameters since they are include in this file.
The OpenLink ODBC Driver for SQL Server and Sybase for Windows is distributed in a single .msi file.
Click the Open link that appears in your Downloads dialog.
|
The installer will display a "Welcome" message. Click "Next."
|
The next screen will display the License Agreement for the OpenLink Lite Driver. Please read and check the "I accept the license agreement" checkbox. Then, click Next.
|
Your driver needs a license file to operate. Click the Browse button to locate a commercial or evaluation license that you have previously downloaded onto your local hard drive. Alternatively, click the Try & Buy button to obtain a commercial or evaluation license.
|
You can check the "I don't want to install a license file right now" check box. This option will permit you to install the product. However, you will not be able to use the product until you obtain a commercial or evaluation license key.
|
Click Next.
Choose among the Typical, Complete, or Custom installation types.
|
|
Click Next.
Click the Install button.
|
Installation is complete. Click the Finish button.
|
You may be prompted to restart your computer, if you have a pre-existing OpenLink License Manager running on your computer.
Open the ODBC Data Sources Administrator that appears in the Administrative Tools section of your Control Panel.
|
Click the System DSN tab:
|
Click the Add button. Then, select the OpenLink SQL Server and Sybase Lite Driver from the list of available drivers:
|
Click Finish.
The first dialog prompts for a Data Source Name and optional description.
|
Click Next.
The second dialog prompts for information that identifies the SQL Server DBMS and database.
|
Use the "Advanced" button to manually configure a connection if the SQL Server instance could not be dynamically located, as detailed below.
|
Click Next to continue.
The third dialog takes a combination of database specific and optional parameters:
|
Click Next to continue.
The fourth dialog enables you to set optional, ODBC connection parameters:
|
Click Next to continue.
The fifth dialog enables you to set additional parameters to enhance compatibility with applications:
|
Click Next to continue.
The final dialog enables you to text your Data Source. Click the Test Data Source button.
|
A connection has been established:
|
|
|
Enter details here for the JDBC Driver to connect with:
The name of the JDBC Driver to be used for the connection
The JDBC connectstring URL for the JDBC Driver
Enter the username and password to use for the connection here. If you check the `Connect now' box, pressing `Next>' will make a test connection to verify the above parameters.
|
Enable this option to have the catalog name not appear for tables, views and procedures when requesting database meta-data.
Enable this option to have the schema-name not appear for tables, views and procedures when requesting database meta-data.
Check this box to have SQLStatistics() return an empty resultset - use this if the underlying database does not support retrieving statistics about a table (e.g. what indexes there are on it).
If it is set, the call SQLGetInfo for 'SQL_IDENTIFIER_QUOTE_CHAR' will return the space (" "). It can be used if DBMS doesn't support quoted SQL like select * from "account"
If it is set, the call SQLGetInfo for 'SQL_LIKE_ESCAPE_CLAUSE' will return the space (" "). It can be used if DBMS doesn't support SQL escape patterns
|
|
|
|
|
The hostname on which the MySQL database server to be connected to is running on.
The port number on which the MySQL database server is running on the host machine. Defaults to 3306 if not specified.
The database name to which the connection is to be made.
|
Disable ODBC transaction management. All transactions will be automatically committed. This prevents palloc() failures with out-of-memory errors when doing really big transactions such as Exporting 10000 records from MS/Access.
|
|
|
|
|
The hostname on which the PostgreSQL database server to be connected to is running on.
The port number on which the PostgreSQL database server is running on the host machine. Defaults to 3306 if not specified.
The database name to which the connection is to be made.
Disable ODBC transaction management. All transactions will be automatically committed. This prevents palloc() failures with out-of-memory errors when doing really big transactions such as Exporting 10000 records from MS/Access.
|
|
|
You may sometimes be required to build a complete ODBC Connection String to access an ODBC Datasource.
A connection string has the following syntax:
connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string
empty-string ::=
attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
attribute-keyword ::= DSN | UID | PWD | driver-defined-attribute-keyword
attribute-value ::= character-string
driver-defined-attribute-keyword ::= identifier
where character-string has zero or more characters; identifier has one or more characters; attribute-keyword is not case-sensitive; attribute-value may be case-sensitive; and the value of the DSN keyword does not consist solely of blanks.
The following list describes valid attributes and their values common to all databases:
Datasource name.
Username.
Password.
The name of the ODBC Driver to be used. This will be the name displayed in the ODBC Administrator or returned by the SQLDrivers function.
Yes/No - make the session readonly.
2 digit integer value to determine the number of rows to buffer in each fetch.
Yes/No - suppress the authentication dialog box that will appear if either username or password have been omitted.
integer value to limit the number of returned rows.
Yes/No - define the default behaviour of the driver to adopt.
Yes/No - enable Microsoft Jet Engine compatibility features.
Yes/No
Yes/No - System views as system tables.
Points to a file containing a list of initial SQL statements to be executed against the Database on connnect.
There is nothing specific to DB2. See above section for common parameters.
The following list describes valid attributes and their values that are specific to Informix:
Informix protocol identifier.
Service name as in the etc\services file.
hostname of the machine hosting the Informix server.
Name of the Informix server.
name of the database on the Informix server.
Yes/No - Multiplex Session.
The following list describes valid attributes and their values that are specific to Ingres and OpenIngres:
name of the database alias.
<vnode>::<dbname>.
The following list describes valid attributes and their values that are specific to Oracle:
Yes/No.
Yes/No.
SQL*Net connect string usually just the TNS name.
Interface DLL or AutoScan for Driver self determination.
The following list describes valid attributes and their values that are specific to Progress:
Valid Progress connection options.
full path and filename to the tableview file.
See tableview guide for more information.
Valid Progress server options.
Interface DLL or AutoScan for Driver self determination.
The following list describes valid attributes and their values that are specific to Microsoft SQLServer:
The IP address or alias name for the server running SQL Server.
The TCP port number the SQLServer instance is running on
The TDS Protocol version for the type of SQLServer instance being used.
The name of the SQLServer Database
Once you have configured an ODBC Data Source you can test it using the Test Connection button on the DataSources tab.
For a more thorough test, you can use the sample applications provided in the OpenLink Lite Driver installer (unless you chose not to install them). You will have short-cuts to the sample applications in the 'Start' Menu.
The sample application 'CPP Demo 32 Bit' is a good application to use to test an ODBC Data Source. It is simple, allows you to execute simple SQL Queries and includes source code.
When the application has been started, select 'Open Connection' from the Environment menu item.
|
Select the data source you recently created and now want to test. Unless you configured the data source to hide the login dialog prompt you will be presented with it. You need to enter a valid username and password to authenticate the connection.
|
Once the connection has been established you should observe that the title bar of the application now includes the name of the data source that you connected to.
Use the SQL/Execute SQL menu item to query the database and validate basic communication between the Driver and the Database.
|
Enter a valid SQL Statement. You will need to know a valid table name within the database to be able to retrieve information from it. To simply return all available data in a particular table of your choice simply change the name 'authors' (show below) to the name of the table you wish to query. Press OK to execute the query.
|
If the query executes successfully you will see a table of the data returned by the query.
|
|
Previous
Contents of ODBC Drivers |
Chapter Contents |
Next
Unix Data Source Configuration |
