READ.ME DataDirect Technologies DataDirect Connect for ODBC on Windows Edition 5.0 May 2004 This README file contains DataDirect Connect for ODBC information not included in the DATADIRECT CONNECT FOR ODBC REFERENCE or the online help. IMPORTANT: You must have version 2.6 or higher of the Microsoft Data Access Components (MDAC) installed to use DataDirect Connect for ODBC. The version on the DataDirect CD is 2.7. You can download a utility that determines the version of your currently installed MDAC from the following Microsoft site: http://www.microsoft.com/data/download.htm#26SDKinfo CONTENTS Edition 5.0 Features Deprecated Drivers and Databases Available DataDirect Connect for ODBC Drivers Driver Options Notes, Known Problems, and Restrictions Online Books Installed Files Third Party Vendor License Agreement Edition 5.0 Features New Database Version Support ---------------------------- * Informix 9.4 * Oracle 10g * Progress OpenEdge Release 10 (offered as an early release in this Edition) * Sybase 12.5.1 General Enhancements -------------------- * Connection Failover and Client Load Balancing for the DB2 Wire Protocol, Informix Wire Protocol, Oracle, Oracle Wire Protocol, and Sybase Wire Protocol drivers. * Performance enhancements DB2 Wire Protocol Driver Enhancements ------------------------------------- * Autobinding of packages * Ability to change Schema used for catalog functions * Use of catalog stored procedures for newer servers for increased performance * Use of native parameter arrays for bulk operations for increased performance Oracle Wire Protocol Driver Enhancements ---------------------------------------- * Oracle 10g features, including support for Grid computing * RAC support Documentation Enhancements -------------------------- * New diagnostic and troubleshooting appendix * Glossary Deprecated Drivers and Databases Microsoft SQL Server 6.x driver Microsoft Excel driver Informix Dynamic Server with Universal Data Option 9.1.4 Progress Versions 9.1B and 9.1C Available DataDirect Connect for ODBC Drivers See the DATADIRECT CONNECT FOR ODBC INSTALLATION GUIDE for a complete list of supported databases and client requirements. Driver ------ Btrieve (IVBTR20.DLL) DB2 Wire Protocol (IVDB220.DLL) dBASE (IVDBF20.DLL) FoxPro 3.0 DBC (Built into the dBASE driver) Informix (IVINF20.DLL) Informix Wire Protocol (IVIFCL20.DLL) Oracle (IVOR820.DLL) Oracle Wire Protocol (IVORA20.DLL) Paradox (IVIDP20.DLL) Progress SQL92 (IVPRO920.DLL) Progress OpenEdge (IVPRO1020.DLL, offered as an early release)) SQL Server Wire Protocol (IVMSSS20.DLL) Sybase Wire Protocol (IVASE20.DLL) Text (IVTXT20.DLL) XML (IVXML20.DLL) Driver Options DataDirect has included non-standard options for the drivers that enable you to take full advantage of packaged ODBC-enabled applications requiring non-standard or extended behavior. NOTE: These options are not available for use with the SQL Server Wire Protocol driver. To use these options, we recommend that you create a separate data source for each application. Using the registry editor (REGEDIT), open the ODBC.INI section in the registry. In the section for the data source you created, add the string value WorkArounds (or WorkArounds2) with a value of n (for example, WorkArounds=n). The value n is the cumulative value of all options added together. Note that each of these options has potential side effects related to its use. An option should only be used to address the specific problem for which it was designed. For example, WorkArounds=2 causes the driver to report that database qualifiers are not supported, even when they are. As a result, applications that use qualifiers may not perform properly when this option is enabled. WorkArounds=1. If an ODBC driver reports that its SQL_CURSOR_COMMIT_BEHAVIOR or SQL_CURSOR_ROLLBACK_BEHAVIOR is 0, then return 1 instead and force statements to be prepared again by the driver. WorkArounds=2. Some applications cannot handle database qualifiers. If this option is on, the driver reports that qualifiers are not supported. WorkArounds=8. If an ODBC driver cannot determine the number of rows affected by an INSERT, UPDATE, or DELETE, it may return -1 in SQLRowCount. Some products cannot handle this. Turning this option on causes the driver to return 1 instead. WorkArounds=16. For SQLStatistics, if an ODBC driver reports an INDEX_QUALIFIER that contains a period, some applications raise a "tablename is not a valid name" error. Turning this option on causes the driver to return no INDEX_QUALIFIER. WorkArounds=32. Turning this option on forces the driver to re-bind columns after calling SQLExecute for prepared statements. WorkArounds=64. This option results in a column name of Cwhere is the ordinal position in the result set. For example, "SELECT col1, col2+col3 FROM table1" produces the column names "col1" and C2. SQLColAttributes/SQL_COLUMN_NAME returns for result columns that are expressions. Use this option for applications that cannot handle column names. WorkArounds=256. Forces SQLGetInfo/SQL_ACTIVE_CONNECTIONS to be returned as 1. WorkArounds=512. To prevent ROWID results, this option forces the SQLSpecialColumns function to return a unique index as returned from SQLStatistics. WorkArounds=2048. Microsoft Access performs more efficiently when the output connection string of SQLDriverConnect returns DATABASE= instead of DB= for some data sources. This option forces DATABASE= to be returned. WorkArounds=65536. This option strips trailing zeros from decimal results, which prevents Microsoft Access from issuing an error when decimal columns containing trailing zeros are included in the unique index. WorkArounds=131072. This option turns all occurrences of the double quote character ("") into the grave character (`). Some applications always quote identifiers with double quotes. Double quoting causes problems for data sources that do not return SQLGetInfo/SQL_IDENTIFIER_QUOTE_CHAR = . WorkArounds=524288. The Microsoft Foundation Classes (MFC) bind all SQL_DECIMAL parameters with a fixed precision and scale, which causes truncation errors. Set this option to force the maximum precision/scale settings. WorkArounds=1048576. Some applications incorrectly specify a precision of 0 for character types when the value will be SQL_NULL_DATA. This option overrides the specified precision and sets the precision to 256. WorkArounds=2097152. Some applications incorrectly specify a precision of -1 for character types. This option overrides the specified precision and sets the precision to 2000. WorkArounds=4194304. For PowerBuilder users, this option converts all catalog function arguments to upper case unless they are quoted. WorkArounds=536870912. This option allows for re-binding parameters after calling SQLExecute for prepared statements. WorkArounds=1073741824. Microsoft Access assumes that ORDER BY columns do not have to be in the SELECT list. This workaround addresses that mistaken assumption for data sources such as Informix. WorkArounds2=2. Some applications incorrectly specify the ColumnSize/DecimalDigits when binding timestamp parameters. This workaround causes the driver to ignore the ColumnSize/DecimalDigits specified by the application and use the database defaults instead. WorkArounds2=4. Microsoft Access uses the last native type mapping, as returned by SQLGetTypeInfo, for a given SQL type. This workaround reverses the order in which types are returned, so that Access will use the most appropriate native type. WorkArounds2=8. This workaround causes base to add the bindoffset in the ARD to the pointers returned by SQLParamData. This is to work around a MSDASQL problem. WorkArounds2=16. This workaround causes the drivers to ignore calls to SQLFreeStmt(RESET_PARAMS) and only return success without taking other action. It also causes parameter validation not to use the bind offset when validating the charoctetlength buffer. This is to work around a MSDASQL problem. WorkArounds2=24. If you are using a DataDirect Connect for ODBC flat-file driver, such as dBASE, under MSDASQL, you must use this workaround for the driver to operate properly. WorkArounds2=32. Microsoft Access requires "DSN" to be included in a connection string. This workaround appends "DSN=" to a connection string, if it is not already included. WorkArounds2=128. Some applications will open extra connections if SQLGetInfo(SQL_ACTIVE_STATEMENTS) does not return 0. This workaround causes SQLGetInfo(SQL_ACTIVE_STATEMENTS) to return 0 to avoid the overhead of these extra connections. WorkArounds2=256. Workaround for MSDASQL. Causes the drivers to return Buffer Size for Long Data on calls to SQLGetData with a buffer size of 0 on columns whose SQL type is SQL_LONGVARCHAR or SQL_LONGVARBINARY. Applications should always set this workaround when using MSDASQL and retrieving long data. WorkArounds2=512. Workaround for Microsoft Query 2000. Causes the flat-file drivers to return old literal prefixes and suffixes for date, time, and timestamp data types. Query 2000 does not correctly handle the ODBC escapes that are currently returned as literal prefix and literal suffix. WorkArounds2=1024. Workaround for ADO. ADO incorrectly interprets the SQLGetInfo(SQL_MULT_RESULT_SETS) to mean that the last result set returned from a stored procedure is the output parameters for the stored procedure. Setting this workaround causes the driver to return "N" for this SQLGetInfo call. WorkArounds2=2048. Workaround for the ODBC cursor library. ODBC 3.x applications which use the ODBC cursor library will get errors on bindings for SQL_DATE, SQL_TIME, and SQL_TIMESTAMP columns. The cursor library incorrectly rebinds these columns with the ODBC 2.x type defines. The workaround causes the DataDirect Connect for ODBC drivers to accept these 2.x SQL types as valid. WorkArounds2=4096. The ODBC Driver Manager incorrectly translates lengths of empty strings when a Unicode-enabled application uses a non-Unicode driver. This workaround causes the DataDirect Connect for ODBC drivers to internally adjust the length of empty strings. Use this workaround only if your application is Unicode enabled. Microsoft Access and Visual Basic Users --------------------------------------- We recommend that users of Microsoft Access and Visual Basic add the value pair WorkArounds=25 (1+8+16) for each data source they use with Access and Visual Basic. For data sources that support a single connection, add the line WorkArounds=29 (1+4+8+16). Also, add WorkArounds2=8192. Notes, Known Problems, and Restrictions The following are notes, known problems, and restrictions with Edition 5.0 of DataDirect Connect for ODBC for Windows. Installing and Removing Default Data Sources -------------------------------------------- When you first install a DataDirect Connect for ODBC driver, you are given the option to install a default data source for that driver. If you do not install the default data source at this time, and run the installer again later using the "modify" option, you will not be able to install a default data source for this driver even though this choice is available on the modify window. To install a default data source for a driver after the initial installation, you must uninstall the driver and then reinstall it. If you uninstall DataDirect Connect for ODBC 4.2 after you have installed Connect ODBC 5.0, default data sources that were created by the installer and share the same name for both DataDirect Connect for ODBC versions will be removed from your machine. Installation Issues on Windows 98 and Windows Me ------------------------------------------------ When you install DataDirect Connect for ODBC, the installer modifies the AUTOEXEC.BAT file to include a SET PATH statement that specifies the path to the DataDirect Connect for ODBC installation. If you have installed DataDirect Connect for ODBC on Windows 98 or Windows Me, and then modify, upgrade, or uninstall it, the original SET PATH statement is not removed from AUTOEXEC.BAT. You must manually remove it with a text editor. Installing the XML Driver ------------------------- You cannot have side-by-side installations of the DataDirect Connect for ODBC XML 5.0 driver and the DataDirect Connect for ODBC XML 4.2 driver on your machine. After you install the 5.0 XML driver, the 4.2 XML driver will not work. If you uninstall the 4.2 XML driver after installing the 5.0 driver, you must self-register the following two XML files: *INIADX09.DLL *INXMLX09.DLL For example to self-register INIADX09.DLL, enter: regsvr32 INIADX09.DLL You must also have the Microsoft XML Parser installed. If you are downloading the parser, as instructed in the DATADIRECT CONNECT FOR ODBC REFERENCE, be sure to download the complete package, MSXML.MSI, not the sub-packages also available on the site. SQLColAttribute(s) ------------------ The column attributes 1001 and 1002, which were assigned as DataDirect- specific attributes, were inadvertently used as system attributes by the Microsoft 3.0 ODBC implementation. Applications using those attributes should now use 1901 and 1902, respectively. SQL_C_NUMERIC ------------- Because of inconsistencies in the ODBC specification, users attempting to use SQL_C_NUMERIC parameters should set the precision and scale values of the corresponding structure and the descriptor fields in the APD. For Developers Using IIS ------------------------ One of the most common connectivity issues encountered while using IIS (Microsoft's Internet Information Server) concerns the use and settings of the account permissions. If you encounter problems using Connect for ODBC with an IIS server, please see KnowledgeBase document number 28541 on the DataDirect Technologies web site: http://www.datadirect- technologies.com. For Microsoft Access Users -------------------------- Some SQL implementations, such as PROGRESS, do not allow queries to contain the COUNT function without the DISTINCT set quantifier. For example, "select COUNT(empid) from employees" is not a valid PROGRESS query. This type of query can be generated using Microsoft Access and will result in an error. For MTS-DTC Users ----------------- Distributed transaction support (MTS-DTC) is available only under Windows NT 4.x, Windows 2000, Windows XP, and Windows Server 2003. On Windows NT, the Microsoft Transaction Server must be installed on the client on which you install the DTC-enabled ODBC drivers. Install the Microsoft Transaction Server as follows: 1. Make sure you have Windows NT Service Pack 3 or higher installed. This is required before you can install NT Options Pack 4.0. 2. Install NT Options Pack 4.0. 3. Install NT Service Pack 5 or higher. If you had NT Service Pack 5 or higher installed before you installed NT Options 4.0, you must reinstall the Service Pack. Because MTS-DTC runs as an NT service, the DTC-enabled ODBC drivers and the database clients they require must be installed on a local hard drive. If you install the DTC-enabled ODBC drivers into a directory other than the \PROGRAM FILES\DATADIRECT\ODBC32v50\DRIVERS (default) directory, that directory must be on the System PATH. On Windows 2000, the Microsoft Transaction Server is already installed. For Btrieve Users ----------------- The Btrieve driver may experience problems if the Btrieve Microkernel Engine's communication buffer size is smaller than the Btrieve driver's Array Size attribute. You can increase the communication buffer size with the BTI Database Setup Utility. You can decrease the array size option when you configure a data source using the ODBC Btrieve Setup dialog, or when passing a connection string. For Flat-File Users ------------------- If you are using a DataDirect Connect for ODBC flat file driver, such as dBASE, under MSDASQL, you should use WorkArounds2=24 as described in the section "Driver Options" in this README. For Informix Users ------------------ Restriction: Some Informix error messages may contain more than one error. All errors displayed may not be valid. Attempting to use Microsoft Query '95 with the Informix drivers will result in a protection fault from Query. This is because Query does not recognize the INTERVAL data types supplied by the drivers. Users will need to obtain Microsoft Query '97 to obtain the fix. If you are using connection failover with the Informix Wire Protocol driver, you must specify the Database connection attribute as part of the AlternateServers string. The DATADIRECT CONNECT FOR ODBC REFERENCE documents only the HostName, PortNumber, and ServerName attributes. For example, the following string defines two alternate database servers: (Database=Infdb1;HostName=Informixhost1;PortNumber=5177; ServerName=accounting1, Database=Infdb1;HostName=Informixhost2; PortNumber=5178;ServerName=accounting2) For Oracle Client Users ----------------------- The following connection string attribute for the Oracle (client) driver is not documented in the DATADIRECT CONNECT FOR ODBC REFERENCE: Attribute: ClientVersion (CV) Possible values: ClientVersion={8i | 9iR1 | 9iR2 | 10gR1} Default Value: 9iR2 The driver assumes that it is connected to an Oracle server using the version of Oracle client software specified by this attribute. When set to 10gR1, the driver binds all non-integer numerics as Binary Float and Binary Double. When set to any version previous to 10gR1, the driver binds non-integer numerics as if connected to a 9iR2 or earlier version of the server (regardless of the actual version of the server to which it is connected). This attribute must be set to the same version as the actual Oracle client software in use when connecting to a 10g server with a pre-10g client; otherwise, numeric parameter bindings may fail. Because versions of the Oracle client software prior to 10g R1 do not fully support the new features of the 10g database Server, some of these new features do not work properly when using earlier versions of the client software. The equivalent option for this connection string attribute is a drop-down list on the General Tab of the Oracle (client) driver Setup dialog box. For Oracle8 Server Users ------------------------ The DataDirect Connect for ODBC Oracle driver uses the XA protocol support in Oracle8 to accomplish distributed transaction support. For recovery to work properly, you must grant appropriate privileges on any server that will participate in a DTC transaction. You must grant the SELECT privilege on SYS.DBA_PENDING_TRANSACTIONS to all users that will use MTS. Refer to the Oracle8 documentation for details on using Oracle XA. For Oracle 10g Users ------------------------ The DATADIRECT CONNECT FOR ODBC REFERENCE contains the following paragraph in the Oracle chapter under the "Unicode Support" heading "Alternatively, on Windows, instead of the NLS_LANG environment variable, the value of the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 registry key can be set to:..." This is correct for Oracle9i version 9.2 and earlier. For Oracle 10g, the paragraph should read as follows: "Alternatively, on Windows, instead of the NLS_LANG environment variable, the value of the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ registry key can be set to:... where is the Oracle home name used at the time of client installation." For Microsoft SQL Server Users ------------------------------ SQL Server does not support multiple active statements on a single connection by default. A Microsoft Knowledgebase document explaining how to enable multiple active statements on a single connection with SQL Server using server-side cursors is available at the following URL: http://support.microsoft.com/support/kb/articles/q140/8/96.asp. The SQL Server Wire Protocol driver returns stored procedure names with the version number appended to them. For example, a procedure defined as "proc1" will be returned as "proc1;1". The WorkArounds listed in the "Driver Options" section of this README cannot be used with the SQL Server Wire Protocol driver. For Sybase Users ---------------- On the Logon dialog box, there is a Database drop-down list that displays the database names specified on the Connection tab of the ODBC Sybase Wire Protocol Driver Setup dialog box. This functionality is available in all cases except when connecting through the Test Connect button on the Setup dialog box. When connecting through the Test Connect button, only the default database specified on the General tab of the Setup dialog box is available. For Text Users -------------- Restriction: When encountering an invalid file, you may receive duplicate or multiple errors from SQLError. Online Books You can view the Online Documents with the Adobe Acrobat Reader version 4.0 or higher. For instructions about how to install and view the Online Books, refer to the DATADIRECT CONNECT FOR ODBC INSTALLATION GUIDE. Adobe Acrobat Reader is no longer distributed on the DataDirect Connect Series CD. To download Acrobat Reader from the Web, go to Adobe’s Web site at http://www.adobe.com. Installed Files The file list below assumes a full installation of all drivers and documentation. Setup copies the following files to the system directory, by default, C:\WINDOWS\SYSTEM: CTL3D32.DLL Visual C Runtime Library DBNETLIB.DLL SQL Server Wire Protocol driver support file MFC42.DLL Visual C Runtime Library SQLSODBC.CHM SQL Server Wire Protocol driver help SQLSODBC.HLP SQL Server Wire Protocol driver help SQLSRV32.DLL SQL Server Wire Protocol driver support files SQLSRV32.RLL SQLUNIRL.DLL Setup copies the following files and subdirectories to the product installation directory, by default, C:\PROGRAM FILES\DATADIRECT\ODBC32v50: 401COMUPD.EXE FIXES.TXT A text file that lists the fixes in the release ICULICENSE.TXT License agreement for ICU ODBCREAD.ME This file XMLP.EXE A GUI demo tool that persists a result set to an XML data file \BOOKS\ODBC: BOOKS.PDF DataDirect Connect for ODBC library listing INDEX.PDX Index file for full-text search \INDEX Full-text search directories and files \ODBCIG\ODBCIG.PDF DataDirect Connect for ODBC Installation Guide \ODBCREF\ODBCREF.PDF DataDirect Connect for ODBC Reference \DRIVERS: For the following files, resource files for each driver end in R.DLL; setup files for each driver end in S.DLL. DDEXTWIN.EXE INIADX09.DLL Support DLL for XML driver INIADX09R.DLL INXMLX09.DLL Support DLL for XML driver INXMLX09R.DLL IVASE20.DLL Sybase Wire Protocol driver IVASE20R.DLL IVBTR20.DLL Btrieve (Pervasive.SQL) driver IVBTR20R.DLL IVBTR20S.DLL IVDB220.DLL DB2 Wire Protocol driver IVDB220R.DLL IVDBF20.DLL dBASE driver IVDBF20R.DLL IVICU20.DLL Unicode conversion tables IVIDP20.DLL Paradox driver IVIDP20R.DLL IVIDP20S.DLL IVIFCL20.DLL Informix Wire Protocol driver IVIFCL20R.DLL IVINF20.DLL Informix driver IVINF20R.DLL IVINF20S.DLL IVINFDTC20.DLL IVMSSS20.DLL SQL Server Wire Protocol driver IVMSSS20R.DLL IVODBC.LIC DataDirect ODBC driver license file IVOR820.DLL Oracle driver IVOR820R.DLL IVOR820S.DLL IVOR8DTC20.DLL IVORA20.DLL Oracle Wire Protocol driver IVORA20R.DLL IVPRO1020.DLL Progress OpenEdge driver IVPRO1020R.DLL IVPRO1020S.DLL IVPRO920.DLL Progress SQL92 driver IVPRO920R.DLL IVPRO920S.DLL IVTRN20.DLL DataDirect sample translation DLL IVTXT20.DLL Text driver IVTXT20R.DLL IVXML20.CNT XML driver help contents IVXML20.DLL XML driver IVXML20.HLP XML driver help IVXML20R.DLL IVXML20S.DLL LICENSE.INI License information file QESQLEXT.H Header file for DataDirect-specific ODBC definitions \EXAMPLE: EXAMPLE.C Source file for example program EXAMPLE.DSP Project file for example program EXAMPLE.DSW Workspace file for example program EXAMPLE.H Header file for example program EXAMPLE.MAK Make file for example program EXAMPLE.TXT Short description of the program \HELP: HELP.HTM HTML help system entry file *.CSS Stylesheets for HTML help system *.HTML HTML help system \IMAGES\*.* Images for HTML help system \WWHDATA\*.* Support files for HTML help system \WWHELP\*.* Support files for HTML help system \TRANSLAT: LIBMAIN.C README.TRN Translation Library information file TRANSLAT.C Sample Translation Library source file WINNT.MAK Makefile for the Sample Translation Library Third Party Vendor License Agreement This DataDirect product includes ICU Version 1.8.1 which is subject to the following license agreement: ICU License - ICU 1.8.1 and later ICU License - ICU 1.8.1 and later COPYRIGHT AND PERMISSION NOTICE Copyright (c) 1995-2003 International Business Machines Corporation and others All rights reserved. Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, provided that the above copyright notice(s) and this permission notice appear in all copies of the Software and that both the above copyright notice(s) and this permission notice appear in supporting documentation. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR HOLDERS INCLUDED IN THIS NOTICE BE LIABLE FOR ANY CLAIM, OR ANY SPECIAL INDIRECT OR CONSEQUENTIAL DAMAGES, OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. Except as contained in this notice, the name of a copyright holder shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Software without prior written authorization of the copyright holder. ------------------------------------------------------------------------ All trademarks and registered trademarks mentioned herein are the property of their respective owners. ~~~~~~~~~~~~~~ End of READ.ME