PostgreSQL/Oracle/MSSQL differences (was: streaming result sets: progress)

["David Hooker" <dhooker(at)a4networks(dot)com>]

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:

* MSSQL requires that ResultSet.getXXX methods, when used with column
names, are called in column order - PostgreSQL doesn't care. (BTW, the
JDBC javadoc suggests to do this).

* PostgreSQL and MSSQL both treat a trailing semicolon as optional.
Oracle requires that there be NO semicolon.

* 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).

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

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

* 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).

Those are just the differences that bit me.  Hope my trial helps you
guys.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Nic Ferrier
Sent: Thursday, November 14, 2002 1:31 PM
To: Barry Lind
Cc: snpe; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] streaming result sets: progress


Barry Lind <blind(at)xythos(dot)com> writes:

> nferrier(at)tapsellferrier(dot)co(dot)uk wrote:
> > snpe <snpe(at)snpe(dot)co(dot)yu> writes:
> >
> > Yes. But the reason I send:
> >
> > DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
FROM JDBC_CURS_1;
> >
> > is because the SQL statement you supply is _supposed_ to end with a
> > ";". The code for the above is actually doing:
> >
> >    DECLARE JDBC_CURS_1 CURSOR FOR $userquery  FETCH FORWARD 100 FROM
JDBC_CURS_1;
> >
> > where $userquery is what comes in from the client code, eg:
> >
> >    Statement st = con.createStatement();
> >    ResultSet rs = con.executeQuery("select * from tab;");
> >
> > then "select * from tab;" is inserted as $userquery. It must always
> > end with ";" because that's how PGSQL's normal query processing
works.
> >
> 
> The the ; is not required for the rest of the jdbc driver.  In fact in
> other areas of the code (like server prepared statements, batch
updates)
> the requirement is that the user supplied sql statement does *not* end
> in a ;.
> 
> This is also consistent with other jdbc drivers.  In fact oracle gives
> you an error if a sql statement ends with a ;.

Apologies to everyone paying attention to this thread... in my
experience postgres has always complained when I haven't supplied the
";".

I will investigate and fix the patch.


Nic


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)