This chapter discusses MySQL Client Library Driver for Oracle Database, and its use in migrating applications from MySQL to Oracle.
This chapter contains the following topics:
See Also:Chapter 7, "API Reference for Oracle MySQL Client Library Driver" for more information about MySQL programmatic support
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.
liboramysql driver uses Oracle Call Interface (OCI) to connect to Oracle Database.
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
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.
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 Lite packages for download from OTN. See
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.
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/client64/lib directory on 32-bit and 64-bit Linux platforms, respectively.
Migrating a C-based MySQL application to Oracle Database involves the following steps:
Confirm that the application runs against MySQL Database.
This ensures that the migration process starts at a known baseline of functionality.
libmysqlclient library with the
The application must be relinked to use the
liboramysql library instead of the
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.
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.
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.
Test the application with Oracle Database.
Verify the application against Oracle Database.
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.
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:
The fundamental step of using the
liboramysql library is to relink the application to use the new library. The
liboramysql library is compatible with the
libmysqlclient.so library from MySQL Commercial Connector/C 6.0.2 package, so you must build and verify version-sensitive applications with MySQL Commercial Connector/C 6.0.2 before migrating to Oracle Database.
The installation scripts of public software compiled from source code typically expect MySQL components to follow a predefined system directory structure. You can use the
setuporamysql.sh script in the
demo directory of Instant Client SDK to achieve this.
Depending on the application, you can use one or more of the following ways to relink the application with the
Build directly with the
You can update your build scripts to use the
liboramysql library and build custom applications directly with this Oracle library.
liboramysql library to emulate a MySQL Commercial Connector/C directory
setuporamysql.sh library in the Instant Client SDK shows how a directory structure emulating a MySQL Commercial Connector/C installation can be created. You may build applications using this emulated directory.
LD_PRELOAD environment variable.
Preconfigured programs may be able to use the
LD_PRELOAD environment variable to link with the
liboramysql library. However, changing the value of this environment variable may not work if the program uses the
Perform the following steps to rename the
liboramysql library to the MySQL client library name used by the application:
ldd command to identify the MySQL library with which the application is linked:
$ ldd yourprogram ... libmysqlclient.so.16 => /usr/lib/libmysqlclient.so.16 (0x00007f9004e7f000) ...
Create the following symbolic link as the Oracle software owner user:
$ ln -s $ORACLE_HOME/lib/liboramysql12.so $ORACLE_HOME/lib/libmysqlclient.so.16
$ORACLE_HOME/lib to the
LD_LIBRARY_PATH environment variable for any application that formerly used the
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Replace the system MySQL client library.
Rename the target system MySQL client library and link the new library in its place. Because this option affects every application on the system that uses MySQL, and should be done only if absolutely necessary.
# mv /usr/lib64/libmysqlclient.so.16 /usr/lib64/libmysqlclient.so.16.backup # ln -s $ORACLE_HOME/lib/liboramysql12.so /usr/lib64/libmysqlclient.so.16
If MySQL applications are not rebuilt from the source code, then you must first link the applications against the
libmysqlclient.so library from MySQL Commercial Connector/C 6.0.2 package. This ensures binary compatibility with the data structures in the
To connect to Oracle Database with the
liboramysql library, use Oracle Easy Connect syntax or a
tnsnames.ora connect identifier in the
host parameter of the connection call:
mysql_real_connect(c, "localhost/pdborcl", "myun", "mypw", NULL, 0, NULL, 0);
MySQL Client Library Driver for Oracle is available on platforms that support the Oracle Instant Client.
See the list of supported platforms on the Oracle Support Certification site:
All errors generated by OCI client code or the Oracle server are passed to the application when either the
mysql_errno() method or the
mysql_error() method is invoked after an error.
The date format expected by the application may be set using
NLS_DATE_FORMAT environment variable of Oracle Database, or changed with the equivalent
ALTER SESSION command after connecting. The
NLS_DATE_FORMAT environment variable is only used if
NLS_LANG is also set in the environment.
Some APIs in the
liboramysql library necessarily return different results because of the underlying differences between MySQL Database and Oracle Database. Existing applications that use these APIs may require logic changes. For details of these differences, see Chapter 7, "API Reference for Oracle MySQL Client Library Driver".