SQL API Portability

This document currently lives at http://home.fnal.gov/~dbox/SQL_API_Portability.html

There have been great advances in database portability at the programming language level in recent years. API's such as JDBC Perl::DBI and ODBC (and its OO wrappers odbc++ and RDBC) make porting an application from one database to another fairly easy as long as some care is taken not to use vendor specific SQL constructs. The following a compilation of 'gotchas' that I have run into when porting applications from one vendor to another, this is by no means an exhaustive list.

Dec 10 2004

I have found a wonderful interactive site that discusses the differences between MySQL, Oracle , SQL Server , DB2 , Access and Postgres at http://sqlzoo.net/

It has tutorials and references for in its many dialects all in one convenient place.

Mysql-isms To Be Avoided for API Portability:

Oracle-isms To Be Avoided for API Portability:


Reserved Words

It is extremely common to develop a schema and application, deploy it and use it for years and then discover upon porting to another database that a table name or column name is a reserved word on the target server. There is no excuse not to check for these during initial development, a quick Google search yields:

A better approach is to come up with a naming convention that avoids using reserved words. If you require all tables to end in '_T' and all column names to end with '_C' for instance, you can avoid the reserved word problem entirely.

Case Study: Minos offline database developed w/ MySQL

Case Sensitivity

Some database servers (like MySQL) are generally case sensitive, it is possible to have a table FOO and a separate table Foo. Others, like Oracle do not distinguish this so table Foo is the same table as fOo.

Data Representation and DDL

Most of the datatypes in the left hand column of this table get translated to a vendor-specific type, which works fine in any SQL used for that vendor. To maintain portability, use the equivalent, portable term in the right hand column. An asterisk ( * ) means that no pre-defined data type matches, but the 'Most Portable' column on the far right is a functional equivalent.

ANSI data type Oracle MySqlPostGreSQLMost Portable
integer NUMBER(38) integer(11)integerinteger
smallint NUMBER(38) smallint(6)smallintsmallint
tinyint * tinyint(4)*numeric(4,0)
numeric(p,s) NUMBER(p,s) decimal(p,s)numeric(p,s)numeric(p,s)
varchar(n) VARCHAR2(n) varchar(n)character varying(n)varchar(n)
char(n) CHAR(n) varchar(n)character(n)char(n)
datetime DATE datetime timestamp without time zone have to autodetect
float FLOAT(126) floatdouble precisionfloat
real FLOAT(63) doublerealreal

Operators, Functions

The SQL Standard defines some operators and functions, and every vendor helpfully defines new ones for you which are usually different than other vendors. An example of a non-standard operator is biwise and '&' in mysql. Oracle implements this as a function bitand(a,b).

Miscellaneous Wierdnesses

Suppose a table foo has an index on column bar. Suppose further a user wants to read the entire table out in index sorted order, ie

SELECT * from foo order by bar;

The mysql SQL engine, which is inferior to the oracle one in many ways, is smart enough to notiice the index on bar and start pulling out rows in the already indexed sort order. The industrial strength oracle engine instead pulls the entire contents of foo into memory and then sorts on bar. If foo is very large and the server is not, out of memory errors ensue. Happily, one can give the oracle sql engine a clue by making some condition on bar, like so:

SELECT * from foo where bar >0 or bar <=0 order by bar;

Dennis Box
Last modified: Thu Apr 29 18:06:34 DST 2004