Re: PostgreSQL/Oracle/MSSQL differences (was: streaming result

David Hooker wrote:

> I've just recently updated my code, which has been using PostgreSQL
> exclusively for a year, to make it able to run using MSSQL Server and
> Oracle.
>
> Some of the differences:

[...]

> * Oracle uppercases table names in the ALL_TABLES view (analogous to
> pg_tables in PostgreSQL), so for code to be portable all table names
> should be created as uppercase. (I just went through my code and
> uppercased all my SQL).

Identifiers are, on PostgreSQL and Oracle:

- folded to the native case if unquoted
- left alone in quoted
(DatabaseMetaData.supportsMixedCaseQuotedIdentifiers() will tell you if
this is true for a specific database)

so all of my table/column names are in native case and I don't have to
do "TABLE_NAME" instead of table_name everywhere. That strikes me as a pain.

In literals, like all_tables, you could just native-case your paremeters
before sending them to the database (DatabaseMetaData will tell you
which is native) or make a stored procedure that does so. "select * from
all_tables where table_name = native_case('foo')". Or just use
DatabaseMetaData instead of directly accessing the (unportable already)
data dictionary; I think it takes care of all stuff like that for you.

On the Java side, ResultSet.getXXX(columnName) is case-insensitive, so
you don't need to worry about it. But ResultSetMetaDAta.getColumnName()
doesn't normalize case; you could toLower() it if your stuff cares.

> * Transactions in MSSQL are handled differently than PostgreSQL and
> Oracle - there is no "BEGIN TRANSACTION" statement; instead you have to
> toggle "SET IMPLICIT_TRANSACTIONS".

This shouldn't be a problem with JDBC - there are functions dealing with
transactions in a general way. An autocommit toggle, a commit/rollback
method, and control over transaction isolation levels. All in the
Connection interface.

> * Oracle doesn't have "text" or "bigint" datatypes.

PostgreSQL's "varchar" and "text" are the same, except that varchar
supports an _optional_ maximum length.

Oracle has clob and long. "clob" is newer and preferred.

Instead of bigint, you can use numeric(N,0), which is standard. It
exists on PostgreSQL and Oracle. I would assume MS SQL as well.

> * MSSQL can't perform string comparisons on "text" datatypes. (i.e.,
> "select * from table where field='value'" won't work if field is a text
> datatype).

Oracle's long has the same limitation. It makes sense, though, because
that would be really, really slow.

And here's a couple others I've hit:

- PostgreSQL supports selecting from no tables. Nothing else does. You
could add a "dual" table with one item for portability. Or use the JDBC
escapes instead of doing a select just to retrieve the result of a function.

- Oracle, previous to 9i, doesn't support SQL-92 syntax. This is really
annoying for outer joins. No way to have a single query that works on
both, except maybe a view. That's a major reason why my code that
handles libraries of SQL statements supports having different SQL for
different databases.

Scott