5.3.1.Creating ODBC Data Sources
OpenLink Software's components are fully supported on Mac OS X version 10.1.x ("Puma") and 10.2.x ("Jaguar"). In most ways, this looks the same to users. However, there are some significant differences to be aware of between these Operating Systems.
As shipped from Apple, Mac OS X did not include any ODBC support until Jaguar. The ODBC Driver Manager, Data Source Administrator, etc. - all had to be delivered with the Drivers.
When Apple introduced Darwin, an Open-Source project meant to form the kernel of Mac OS X, OpenLink determined to port its open-source iODBC Driver Manager to the new platform. With the introduction of the Preview Release of Mac OS X, the traditional set of dynamic libraries was broadened to include a system of Frameworks to encourage the development of fully ODBC compliant, native Mac OS X drivers and client applications.
That set of Frameworks, along with the OpenLink ODBC Administrator (then known as the iODBC Administrator), has been included with all OpenLink installations for Mac OS X since 10.0. The OpenLink ODBC Administrator supports all core features of ODBC, and presents driver-specific DSN configuration panels, as defined by the Driver developer, through the use of Setup Libraries.
Jaguar's release marked Apple's recognition that Data Access was an important part of an Enterprise Operating System. Already part of the standard distribution of Darwin, the basic iODBC dynamic libraries are now a part of the standard installation of Mac OS X 10.2. Apple also included their own version of an ODBC Administrator, as a proof-of-concept. Apple's Administrator permits Driver Registration, Tracing, and all other core features of ODBC; however, among other hard edges, all DSN configuration must be done by manually entering Keyword-Value pairs. Further, the user must know what Keywords to use, along with their acceptable Values.
Mac OS X ODBC Data Sources
The OpenLink ODBC Administrator for Mac OS X currently supports creation of User and System Data Source Names (DSNs). Support for File DSNs will be delivered in a future release.
A User DSN is only available to the user who creates the data source. Its parameters are stored in that user's settings file ~/Library/Preferences/ODBC.preference .
A System DSN is available to the whole system so that any user, including the system itself, will be able to use that data source. Its parameters are stored in the System settings file /Library/Preferences/ODBC.preference.
A File DSN is a special 'mobile' data source that stores the data source information associated with the Driver in a file, which may then be copied and shared among different users and ODBC application host machines.
The steps for creating a DSN are as follows:-
-
Launch the OpenLink ODBC Administrator, found in the /Applications/Utilities/ directory.
Figure5.127.OpenLink ODBC Administrator icon
-
Click the tab for the kind of DSN you wish to create -- User or System. Press the Add button to begin creating a new Data source.
Figure5.128.iODBC Data Source Administrator
-
Select the Driver to be used to create your ODBC DSN. In this example the "OpenLink SQL Server Lite Driver" is highlighted. Then click the Finish button.
Figure5.129.Choose an ODBC Driver
-
You will now be presented with the ODBC Data Source Configuration dialog for the driver selected. Generally, there will be several tabs, most of which will need some information.
Common Data Source Tab
First is the Data Source Tab. The parameters on this tab are similar (not identical) for all OpenLink Single-Tier drivers. This tab always holds the Name and Description parameters. Generally, it will also include parameters describing the target database host - Hostname, Port, and, if the Driver handles more than one DBMS, Server Type. Specific information will be presented for each Driver, below.
-
DSN. The name of the ODBC DSN. This is how you will generally refer to this data source, from within ODBC client applications.
-
Description. Any extra information you wish to note about the DSN you are creating. This is generally not displayed by ODBC client applications, though it is available to them.
-
Hostname. This is the fully qualified hostname, or IP address, of the machine hosting the DBMS you wish to access, e.g., dbms-server.example.com, or 192.168.155.123. Any hostname which will be resolved by your local DNS is acceptable.
-
Server Type. Select the version and/or vendor of the DBMS engine you will be accessing, from the list presented. If your exact version is not listed, try the closest version available. If that fails, please contact OpenLink Technical Support.
Common Connection Tab
The second and possibly third tab hold fields specific to the database communications layer. The name(s) of the tab(s), the fields, and their data, are specific to each database. The database specific settings are detailed further in the text. The following list will allow you to quick jump to the relevent section (if reading on-line):
| MySQL |
| Oracle 8 (or 9) |
| PostgreSQL |
| SQL Server |
| Virtuoso |
Common fields are:
-
User name. This specifies the default username to be used when attempting to make a connection. You may override this at run time.
-
Row Buffer Size. This attribute specifies the number of records to be returned in a chunk. Values may range from 1 to 999. Setting this value too high or too low can significantly impact performance. Generally, best results are achieved with values between 50 and 150.
-
Hide Login Dialog. Suppress the ODBC "Username" and "Password" login dialog box when interacting with your ODBC DSN from within an ODBC compliant application. Please note - this requires that these values be correctly specified within the DSN, which can open a security hole.
-
Read Only connection. Specify whether the connection is to be "Read-only". Make sure the checkbox is unchecked to request a "Read/Write" connection.
Common Preferences Tab
The Preferences tab parameters are also generally common across all OpenLink Single-Tier Drivers for ODBC. All Preferences parameters are defined here, though they may not all appear on your Driver.
-
Initialization SQL. Lets you specify a file containing SQL statements that will be run against the database upon connection, automatically.
-
MaxRows Override. Allows you to limit the maximum number of rows to return from a query. The default value of 0 means no limit.
-
Enable Microsoft Jet Engine Options. Extra support or altered functionality for better compatibility with Microsoft Jet using applications such as Microsoft Access. This generally doesn't apply to Macintosh applications; however, we retain the option.
-
Disable AutoCommit. Change the default commit behaviour of the OpenLink Lite Driver. The default mode is AutoCommit mode (box unchecked).
-
Disable Rowset Size Limit. Disable any limitation enforced by the cursor library. The limitation is enforced by default, to prevent the Driver claiming all available memory when a result set is very large. The limit is normally never reached.
-
High Cursor Sensitivity
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.
For this option to take effect, the table 'oplrvc' must already have been created, using the appropriate script for the target database.
-
Defer fetching of long data. Checked by default, this parameter determines whether long-data fields are retrieved after all other fields in a query, or in whatever order they appear in the query. Performance is generally best with this enabled; hence the default setting.