Transcription

MySQL Connector/ODBC Developer Guide

AbstractThis manual describes how to install and configure MySQL Connector/ODBC, the driver that enables ODBCapplications to communicate with MySQL servers, and how to use it to develop database applications.MySQL Connector/ODBC 8.0 is recommended for use with MySQL Server 8.0, 5.7, and 5.6. Please upgrade toMySQL Connector/ODBC 8.0.For notes detailing the changes in each release of Connector/ODBC, see MySQL Connector/ODBC Release Notes.For legal information, see the Legal Notices.For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQLusers.Licensing information.This product may include third-party software, used under license. MySQL Connector/ODBC 8.0 Community License Information User Manual has information about licenses relating to Connector/ODBCcommunity releases in the 8.0 release series. MySQL Connector/ODBC 8.0 Commercial License Information UserManual has information about licenses relating to Connector/ODBC commercial releases in the 8.0 release series.MySQL Connector/ODBC Community License Information User Manual has information about licenses relating toConnector/ODBC community releases up to and including version 5.3. MySQL Connector/ODBC Commercial LicenseInformation User Manual has information about licenses relating to Connector/ODBC commercial releases up to andincluding version 5.3.Document generated on: 2022-06-13 (revision: 73478)

Table of ContentsPreface and Legal Notices . v1 Introduction to MySQL Connector/ODBC . 12 Connector/ODBC Versions . 33 General Information About ODBC and Connector/ODBC . 53.1 Connector/ODBC Architecture . 53.2 ODBC Driver Managers . 74 Connector/ODBC Installation . 94.1 Installing Connector/ODBC on Windows . 104.1.1 Installing the Windows Connector/ODBC Driver Using the Zipped DLL Package . 114.2 Installing Connector/ODBC on Unix-like Systems . 134.2.1 Installing Connector/ODBC Using the MySQL Yum Repository . 134.2.2 Installing Connector/ODBC from a Binary Tarball Distribution . 134.2.3 Installing Connector/ODBC from a DEB Distribution . 144.2.4 Installing Connector/ODBC from an RPM Distribution . 154.3 Installing Connector/ODBC on macOS . 154.4 Building Connector/ODBC from a Source Distribution on Windows . 174.5 Building Connector/ODBC from a Source Distribution on Unix . 184.6 Building Connector/ODBC from a Source Distribution on macOS . 204.7 Installing Connector/ODBC from the Development Source Tree . 215 Configuring Connector/ODBC . 235.1 Overview of Connector/ODBC Data Source Names . 235.2 Connector/ODBC Connection Parameters . 245.3 Configuring a Connector/ODBC DSN on Windows . 335.3.1 Configuring a Connector/ODBC DSN on Windows with the ODBC Data SourceAdministrator GUI . 335.3.2 Configuring a Connector/ODBC DSN on Windows, Using the Command Line . 375.3.3 Troubleshooting ODBC Connection Problems . 375.4 Configuring a Connector/ODBC DSN on macOS . 385.5 Configuring a Connector/ODBC DSN on Unix . 415.6 Connecting Without a Predefined DSN . 415.7 ODBC Connection Pooling . 425.8 Authentication Options . 425.9 Getting an ODBC Trace File . 435.9.1 Enabling ODBC Tracing on Windows . 435.9.2 Enabling ODBC Tracing on macOS . 445.9.3 Enabling ODBC Tracing on Unix . 455.9.4 Enabling a Connector/ODBC Log . 456 Connector/ODBC Examples . 476.1 Basic Connector/ODBC Application Steps . 476.2 Step-by-step Guide to Connecting to a MySQL Database through Connector/ODBC . 486.3 Connector/ODBC and Third-Party ODBC Tools . 496.4 Using Connector/ODBC with Microsoft Access . 506.4.1 Exporting Access Data to MySQL . 506.4.2 Importing MySQL Data to Access . 536.4.3 Using Microsoft Access as a Front-end to MySQL . 556.5 Using Connector/ODBC with Microsoft Word or Excel . 616.6 Using Connector/ODBC with Crystal Reports . 636.7 Connector/ODBC Programming . 696.7.1 Using Connector/ODBC with Visual Basic Using ADO, DAO and RDO . 696.7.2 Using Connector/ODBC with .NET . 737 Connector/ODBC Reference . 79iii

MySQL Connector/ODBC Developer Guide7.1 Connector/ODBC API Reference . 797.2 Connector/ODBC Data Types . 837.3 Connector/ODBC Error Codes . 848 Connector/ODBC Notes and Tips . 878.1 Connector/ODBC General Functionality . 878.1.1 Obtaining Auto-Increment Values . 878.1.2 Dynamic Cursor Support . 888.1.3 Configuring Catalog and Schema Support . 888.1.4 Connector/ODBC Performance . 888.1.5 Setting ODBC Query Timeout in Windows . 898.2 Connector/ODBC Application-Specific Tips . 898.2.1 Using Connector/ODBC with Microsoft Applications . 898.2.2 Using Connector/ODBC with Borland Applications . 928.2.3 Using Connector/ODBC with ColdFusion . 938.2.4 Using Connector/ODBC with OpenOffice.org . 938.2.5 Using Connector/ODBC with Pervasive Software DataJunction . 938.2.6 Using Connector/ODBC with SunSystems Vision . 938.3 Connector/ODBC and the Application Both Use OpenSSL . 948.4 Connector/ODBC Errors and Resolutions (FAQ) . 949 Connector/ODBC Support . 1019.1 Connector/ODBC Community Support . 1019.2 How to Report Connector/ODBC Problems or Bugs . 101iv

Preface and Legal NoticesThis manual describes how to install, configure, and develop database applications using MySQLConnector/ODBC, the driver that allows ODBC applications to communicate with MySQL servers.Legal NoticesCopyright 2005, 2022, Oracle and/or its affiliates.This software and related documentation are provided under a license agreement containing restrictionson use and disclosure and are protected by intellectual property laws. Except as expressly permittedin your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast,modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by anymeans. Reverse engineering, disassembly, or decompilation of this software, unless required by law forinteroperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free.If you find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing iton behalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integratedsoftware, any programs embedded, installed or activated on delivered hardware, and modifications ofsuch programs) and Oracle computer documentation or other Oracle data delivered to or accessed byU.S. Government end users are "commercial computer software" or "commercial computer softwaredocumentation" pursuant to the applicable Federal Acquisition Regulation and agency-specificsupplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure,modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including anyoperating system, integrated software, any programs embedded, installed or activated on deliveredhardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracledata, is subject to the rights and limitations specified in the license contained in the applicable contract.The terms governing the U.S. Government's use of Oracle cloud services are defined by the applicablecontract for such services. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information managementapplications. It is not developed or intended for use in any inherently dangerous applications, includingapplications that may create a risk of personal injury. If you use this software or hardware in dangerousapplications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damagescaused by use of this software or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarksof their respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarksare used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is aregistered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content,products, and services from third parties. Oracle Corporation and its affiliates are not responsible for andexpressly disclaim all warranties of any kind with respect to third-party content, products, and servicesunless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and itsv

Documentation Accessibilityaffiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use ofthird-party content, products, or services, except as set forth in an applicable agreement between you andOracle.This documentation is NOT distributed under a GPL license. Use of this documentation is subject to thefollowing terms:You may create a printed copy of this documentation solely for your own personal use. Conversion to otherformats is allowed as long as the actual content is not altered or edited in any way. You shall not publishor distribute this documentation in any form or on any media, except if you distribute the documentation ina manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with thesoftware) or on a CD-ROM or similar medium, provided however that the documentation is disseminatedtogether with the software on the same medium. Any other use, such as any dissemination of printedcopies or use of this documentation, in whole or in part, in another publication, requires the prior writtenconsent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rightsto this documentation not expressly granted above.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program ility/.Access to Oracle Support for AccessibilityOracle customers that have purchased support have access to electronic support through My OracleSupport. For information, y/learning-support.html#support-tab.vi

Chapter 1 Introduction to MySQL Connector/ODBCThe MySQL Connector/ODBC is the name for the family of MySQL ODBC drivers (previously calledMyODBC drivers) that provide access to a MySQL database using the industry standard Open DatabaseConnectivity (ODBC) API. This reference covers Connector/ODBC 8.0, which includes the functionality ofthe Unicode driver and the ANSI driver.MySQL Connector/ODBC provides both driver-manager based and native interfaces to the MySQLdatabase, with full support for MySQL functionality, including stored procedures, transactions and fullUnicode compliance.For more information on the ODBC API standard and how to use it, refer to http://support.microsoft.com/kb/110093.The application development section of the ODBC API reference assumes a good working knowledge ofC, general DBMS, and a familiarity with MySQL. For more information about MySQL functionality and itssyntax, refer to https://dev.mysql.com/doc/.Typically, you need to install Connector/ODBC only on Windows machines. For Unix and macOS, youcan use the native MySQL network or named pipes to communicate with your MySQL database. You mayneed Connector/ODBC for Unix or macOS if you have an application that requires an ODBC interface tocommunicate with the database. Applications that require ODBC to communicate with MySQL includeColdFusion, Microsoft Office, and Filemaker Pro.For notes detailing the changes in each release of Connector/ODBC, see MySQL Connector/ODBCRelease Notes.Key Connector/ODBC topics include: Installing Connector/ODBC: Chapter 4, Connector/ODBC Installation. The configuration options: Section 5.2, “Connector/ODBC Connection Parameters”. An example that connects to a MySQL database from a Windows host: Section 6.2, “Step-by-step Guideto Connecting to a MySQL Database through Connector/ODBC”. An example that uses Microsoft Access as an interface to a MySQL database: Section 6.4, “UsingConnector/ODBC with Microsoft Access”. General tips and notes, including how to obtain the last auto-increment ID: Section 8.1, “Connector/ODBC General Functionality”. Application-specific usage tips and notes: Section 8.2, “Connector/ODBC Application-Specific Tips”. A FAQ (Frequently Asked Questions) list: Section 8.4, “Connector/ODBC Errors and Resolutions (FAQ)”. Additional Connector/ODBC support options: Chapter 9, Connector/ODBC Support.1

2

Chapter 2 Connector/ODBC VersionsThese are the versions of Connector/ODBC that are currently available: Connector/ODBC 8.0: adds MySQL Server 8.0 support, including caching sha2 password and therelated GET SERVER PUBLIC KEY connection attribute. For additional details, see the Connector/ODBC 8.0 release notes. Connector/ODBC 8.0: is the recommended version. Connector/ODBC 5.3: functions with MySQL Server versions between 4.1 and 5.7. It does notwork with 4.0 or earlier releases, and does not support all MySQL 8 features. It conforms to theODBC 3.8 specification and contains key ODBC 3.8 features including self-identification as a ODBC3.8 driver, streaming of output parameters (supported for binary types only), and support of theSQL ATTR RESET CONNECTION connection attribute (for the Unicode driver only). Connector/ODBC5.3 also introduces a GTK -based setup library, providing GUI DSN setup dialog on some Unix-basedsystems. The library is currently included in the Oracle Linux 6 and Debian 6 binary packages. Othernew features in the 5.3 series include file DSN and bookmark support; see the release notes for the 5.3series for details.Connector/ODBC 5.3.11 added caching sha2 password support by adding theGET SERVER PUBLIC KEY connection attribute. Connector/ODBC 5.2: upgrades the ANSI driver of Connector/ODBC 3.51 to the 5.x code base. It alsoincludes new features, such as enabling server-side prepared statements by default. At installationtime, you can choose the Unicode driver for the broadest compatibility with data sources using variouscharacter sets, or the ANSI driver for optimal performance with a more limited range of character sets. Itworks with MySQL versions 4.1 to 5.7. Connector/ODBC 5.1: is a partial rewrite of the of the 3.51 code base, and is designed to work withMySQL versions 4.1 to 5.7.Connector/ODBC 5.1: also includes the following changes and improvements over the 3.51 release: Improved support on Windows 64-bit platforms. Full Unicode support at the driver level. This includes support for the SQL WCHAR data type, andsupport for Unicode login, password and DSN configurations. For more information, see MicrosoftKnowledgebase Article #716246. Support for the SQL NUMERIC STRUCT data type, which provides easier access to the precisedefinition of numeric values. For more information, see Microsoft Knowledgebase Article #714556 Native Windows setup library. This replaces the Qt library based interface for configuring DSNinformation within the ODBC Data Sources application. Support for the ODBC descriptor, which improves the handling and metadata of columns andparameter data. For more information, see Microsoft Knowledgebase Article #716339. Connector/ODBC 3.51, also known as the MySQL ODBC 3.51 driver, is a 32-bit ODBC driver.Connector/ODBC 3.51 has support for ODBC 3.5x specification level 1 (complete core API level 2features) to continue to provide all functionality of ODBC for accessing MySQL.The manual for versions of Connector/ODBC older than 5.3 can be located in the corresponding binary orsource distribution.3

NoteVersions of Connector/ODBC earlier than the 3.51 revision were not fully compliantwith the ODBC specification.NoteFrom this section onward, the primary focus of this guide is the Connector/ODBC5.3 driver.NoteVersion numbers for MySQL products are formatted as X.X.X. However, Windowstools (Control Panel, properties display) may show the version numbers asXX.XX.XX. For example, the official MySQL formatted version number 5.0.9 maybe displayed by Windows tools as 5.00.09. The two versions are the same; only thenumber display formats are different.4

Chapter 3 General Information About ODBC and Connector/ODBCTable of Contents3.1 Connector/ODBC Architecture . 53.2 ODBC Driver Managers . 7ODBC (Open Database Connectivity) provides a way for client programs to access a wide range ofdatabases or data sources. ODBC is a standardized API that enables connections to SQL databaseservers. It was developed according to the specifications of the SQL Access Group and defines a set offunction calls, error codes, and data types that can be used to develop database-independent applications.ODBC usually is used when database independence or simultaneous access to different data sources isrequired.For more information about ODBC, refer to http://support.microsoft.com/kb/110093.Open Database Connectivity (ODBC) is a widely accepted application-programming interface (API) fordatabase access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC fordatabase APIs and uses Structured Query Language (SQL) as its database access language.A survey of ODBC functions supported by Connector/ODBC is given at Section 7.1, “Connector/ODBC APIReference”. For general information about ODBC, see http://support.microsoft.com/kb/110093.3.1 Connector/ODBC ArchitectureThe Connector/ODBC architecture is based on five components, as shown in the following diagram:Figure 3.1 Connector/ODBC Architecture Components Application:5

Connector/ODBC ArchitectureThe Application uses the ODBC API to access the data from the MySQL server. The ODBC API in turncommunicates with the Driver Manager. The Application communicates with the Driver Manager usingthe standard ODBC calls. The Application does not care where the data is stored, how it is stored, oreven how the system is configured to access the data. It needs to know only the Data Source Name(DSN).A number of tasks are common to all applications, no matter how they use ODBC. These tasks are: Selecting the MySQL server and connecting to it. Submitting SQL statements for execution. Retrieving results (if any). Processing errors. Committing or rolling back the transaction enclosing the SQL statement. Disconnecting from the MySQL server.Because most data access work is done with SQL, the primary tasks for applications that use ODBC aresubmitting SQL statements and retrieving any results generated by those statements. Driver manager:The Driver Manager is a library that manages communication between application and driver or drivers.It performs the following tasks: Resolves Data Source Names (DSN). The DSN is a configuration string that identifies a givendatabase driver, database, database host and optionally authentication information that enables anODBC application to connect to a database using a standardized reference.Because the database connectivity information is identified by the DSN, any ODBC-compliantapplication can connect to the data source using the same DSN reference. This eliminates the need toseparately configure each application that needs access to a given database; instead you instruct theapplication to use a pre-configured DSN. Loading and unloading of the driver required to access a specific database as defined within theDSN. For example, if you have configured a DSN that connects to a MySQL database then the drivermanager will load the Connector/ODBC driver to enable the ODBC API to communicate with theMySQL host. Processes ODBC function calls or passes them to the driver for processing. Connector/ODBC Driver:The Connector/ODBC driver is a library that implements the functions supported by the ODBC API. Itprocesses ODBC function calls, submits SQL requests to MySQL server, and returns results back tothe application. If necessary, the driver modifies an application's request so that the request conforms tosyntax supported by MySQL. DSN Configuration:The ODBC configuration file stores the driver and database information required to connect to theserver. It is used by the Driver Manager to determine which driver to be loaded according to the6

ODBC Driver Managersdefinition in the DSN. The driver uses this to read connection parameters based on the DSN specified.For more information, Chapter 5, Configuring Connector/ODBC. MySQL Server:The MySQL database where the information is stored. The database is used as the source of the data(during queries) and the destination for data (during inserts and updates).3.2 ODBC Driver ManagersAn ODBC Driver Manager is a library that manages communication between the ODBC-aware applicationand any drivers. Its main functionality includes: Resolving Data Source Names (DSN). Driver loading and unloading. Processing ODBC function calls or passing them to the driver.Most ODBC Driver Manager implementations also include an administration application that makes theconfiguration of DSN and drivers easier. Examples and information on ODBC Driver Managers for differentoperating systems are listed below: Windows: Microsoft Windows ODBC Driver Manager (odbc32.dll). It is included in the Windowsoperating system. See http://support.microsoft.com/kb/110093 for more information. macOS: ODBC Administrator is a GUI application for macOS. It provides a simplified configurationmechanism for the iODBC Driver Manager. You can configure DSN and driver information either throughODBC Administrator or through the iODBC configuration files. This also means that you can test ODBCAdministrator configurations using the iodbctest command. See http://support.apple.com/kb/DL895for more information. Unix: unixODBC Driver Manager for Unix (libodbc.so). See http://www.unixodbc.org, for moreinformation. iODBC Driver Manager for Unix (libiodbc.so). See http://www.iodbc.org, for more information.7

8

Chapter 4 Connector/ODBC InstallationTable of Contents4.1 Installing Connector/ODBC on Windows .4.1.1 Installing the Windows Connector/ODBC Driver Using the Zipped DLL Package .4.2 Installing Connector/ODBC on Unix-like Systems .4.2.1 Installing Connector/ODBC Using the MySQL Yum Repository .4.2.2 Installing Connector/ODBC from a Binary Tarball Distribution .4.2.3 Installing Connector/ODBC from a DEB Distribution .4.2.4 Installing Connector/ODBC from an RPM Distribution .4.3 Installing Connector/ODBC on macOS .4.4 Building Connector/ODBC from a Source Distribution on Windows .4.5 Building Connector/ODBC from a Source Distribution on Unix .4.6 Building Connector/ODBC from a Source Distribution on macOS .4.7 Installing Connector/ODBC from the Development Source Tree .101113131314151517182021This section explains where to download Connector/ODBC, and how to run the installer, copy the filesmanually, or build from source.Where to Get Connector/ODBCYou can get a copy of the latest version of Connector/ODBC binaries and sources from our website .Choosing Binary or Source Installation MethodYou can install the Connector/ODBC drivers using two different methods: The binary installation is the easiest and most straightforward method of installation. You receive allthe necessary libraries and other files pre-built, with an installer program or batch script to perform allnecessary copying and configuration. The source installation method is intended for platforms where a binary installation package is notavailable, or in situations where you want to customize or modify the installation process or Connector/ODBC drivers before installation.If

Chapter 1 Introduction to MySQL Connector/ODBC The MySQL Connector/ODBC is the name for the family of MySQL ODBC drivers (previously called MyODBC drivers) that provide access to a MySQL database using the industry standard Open Database