6 MySQL Client Library Driver for Oracle

Introduction to MySQL Client Library Driver for Oracle

MySQL Client Library Driver for Oracle Database 12c Release 1, liboramysql, is a drop-in replacement for MySQL Commercial Connector/C 6.0 client library. The liboramysql driver implements a similar API, enabling C-based applications and tools developed for MySQL to connect to Oracle Database. The driver may be used to migrate applications from MySQL to Oracle Database with minimal changes to the application code.

The liboramysql driver uses Oracle Call Interface (OCI) to connect to Oracle Database.

Figure 6-1 MySQL Application Code Using liboramysql Driver to Connect to Oracle

Description of "Figure 6-1 MySQL Application Code Using liboramysql Driver to Connect to Oracle"

The C code snippet in Example 6-1 demonstrates how to connect to MySQL and how to insert a row into a table. After updating the connection credentials, this code can run unchanged against Oracle Database when the executable is linked using the liboramysql library, instead of the libmysqlclient library.

c = mysql_init(NULL);
mysql_real_connect(c, "myhost", "myun", "mypw", "mydb", 0, NULL, 0);
mysql_query(c, "insert into mytable values (1,2)");
mysql_close(c);

Although the database schema and data must be migrated to Oracle separately, and although the liboramysql library does not translate SQL statements, considerable amount of effort is conserved when migrating to Oracle Database because no changes have to be made to the application code.

Custom C applications can use the liboramysql library to easily migrate to Oracle Database.

Installation and First Use of MySQL Client Library Driver for Oracle

The MySQL Client Library Driver for Oracle is provided as a file in the liboramysql.so shared library for Linux and as the oramysql.dll dynamic link library (DLL) for Windows. The driver is also packaged as part of the Oracle Instant Client Basic and Basic Lite packages for download from OTN. See http://www.oracle.com/technetwork/topics/linuxsoft-082809.html and http://www.oracle.com/technetwork/topics/winsoft-085727.html.

The driver must be installed in the same directory as the Oracle Client Shared Library, that is, libclntsh.so for Linux and oci.dll for Windows. Typically, you must set the operating system environment variable (LD_LIBRARY_PATH on Linux or PATH on Windows) to include this installation directory.

For ORACLE_HOME installations, the driver library is installed in the $ORACLE_HOME/lib directory for Linux and the %ORACLE_HOME%\bin directory for Windows. For Instant Client ZIP files, the library is in the instantclient_12_1 directory. For Instant Client RPM installations, the library is in the /usr/lib/oracle/12.1/client/lib or /usr/lib/oracle/12.1/client64/lib directory on 32-bit and 64-bit Linux platforms, respectively.

Overview of Migration with MySQL Client Library Driver for Oracle

Migrating a C-based MySQL application to Oracle Database involves the following steps:

1. Confirm that the application runs against MySQL Database.

   This ensures that the migration process starts at a known baseline of functionality.

2. Replace the libmysqlclient library with the liboramysql library.

   The application must be relinked to use the liboramysql library instead of the libmysqlclient library.

3. Migrate the application schema to Oracle Database.

   The schema must be migrated to use Oracle DDL and types. Oracle SQL Developer assists in this process.

   See Oracle SQL Developer User's Guide for further details.

4. Review all SQL statements used by the application.

   If necessary, change the SQL statements of the application to use Oracle syntax, or implement a SQL Translator to automatically perform the conversion at application run time. Rewrite any logic that depends on MySQL features that are not supported by Oracle Database.

   See Chapter 4, "SQL Translation of JDBC and ODBC Applications".

5. Update the connection string of the application to connect to Oracle Database.

   Use Oracle Easy Connect syntax or a tnsnames.ora connect identifier in the host parameter of the connection call.

6. Test the application with Oracle Database.

   Verify the application against Oracle Database.

Using MySQL Client Library Driver for Oracle

The liboramysql API is compatible with MySQL Commercial Connector/C 6.0. MySQL Driver for Oracle Database, liboramysql, translates MySQL API calls to Oracle Call Interface (OCI) calls, and between Oracle and MySQL data types.

Existing MySQL-based applications may be relinked to use the liboramysql driver, making Oracle Database the new data source. Note that the liboramysql driver supports connections only to Oracle Database. Simultaneous connections to both MySQL Database and Oracle Database in the same application are not possible.

See Chapter 7, "API Reference for Oracle MySQL Client Library Driver" for details on data type mapping and API compatibility. Additional information may also be found in Oracle SQL Developer User's Guide.

The liboramysql driver does not translate SQL statements. You must rewrite the statements that are not valid for Oracle Database. You can do this directly in the application, or by using a SQL Translator. The application schema and data must also be migrated separately. Oracle SQL Developer automates this process.

Whenever cross-version OCI connectivity exists for older versions of Oracle Database, you can use the liboramysql driver to connect to these older versions.

The following sections discusses these topics:

• Relinking the Application with the liboramysql Driver

• Connecting to Oracle Database

• Supported Platforms

• Error Handling

• Globalization

• Expected Differences

mysql_real_connect(c, "localhost/pdborcl", "myun", "mypw", NULL, 0, NULL, 0);