<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-bean" prefix="bean" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-logic" prefix="logic" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-nested" prefix="nested" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> Converting between Oracle and MySql databases

University of Portsmouth

SUMS

CAM Student & Unit Management System v5.0

SUMS home page

Converting between Oracle and MySql databases

Introduction

Oracle and MySQL are both SQL-compatible database management systems. In principle, this should mean that it is possible to connect a web application to either, unchanged. However, while compatability between the two is high, there are some differences that need to be noted.

The following lists the areas of difference that have been noted to date.

Unless noted, we are talking about Oracle v10 and MySQL v5.

CLOBs

In MySQL, a CLOB (character large object) can be treated effectively as an infinite-length string. In Oracle, it is best considered as a text file located within the database.

Recommendation

You can avoid using a CLOB if your data is never going to be more than 4000 characters long. This is the limit on a VARCHAR2 field in Oracle.

If you do need to use a CLOB, ensure that all JDBC references to it are localised (preferably in a single class) so that they can be changed appropriately. See the CLOB-related code in jim.util.SqlUtils for an example of this (for Oracle CLOBs only). Reusing this class is preferred.

Auto-increment

MySQL has a feature whereby the key field of a record is auto-inserted (next sequential value) when a new row is inserted into a table. This is not implemented in Oracle.

The nearest Oracle feature is a Sequence. Each time a value is requested from a sequence, it is automatically incremented. Such a value can then be used as the key to a record. It is possible to either have one sequence per table, or one sequence shared by all tables, or some combination thereof.

Recommendation

Do not use MySQL's auto increment - allocate all unique keys programmatically. A method that can be used to generate random keys (desirable where the order of creation needs to be concealed) is to use jim.util.SqlUtils.newKey(String table, String keyField, int len). Alternatively, to generate sequential numbers, use jim.util.SqlUtils.nextval(String seq) which is implemented using Oracle sequences.