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

Hi!

<opinion>
If it makes your life difficult, you should use DAO pattern, or a persistence api such as castor.
</opinion>

Laszlo Hornyak

On Thu, Nov 14, 2002 at 01:42:00PM -0600, 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:
>
> * 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.

And doesn`t have boolean, and many more :)

>
> * 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@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nic Ferrier
> Sent: Thursday, November 14, 2002 1:31 PM
> To: Barry Lind
> Cc: snpe; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] streaming result sets: progress
>
>
> Barry Lind <blind@xythos.com> writes:
>
> > nferrier@tapsellferrier.co.uk wrote:
> > > snpe <snpe@snpe.co.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@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster