Обсуждение: Writing portable SQL code in Java

Hi,

Are there any guidelines for writing portable Java code using different SQL
DBMS engines? Specifically, I'm writing code that will be ultimately
deployed on Oracle. I don't really care for encumbering my disk with 300 MB
of Oracle stuff, just to have access to a bad ui (sqlplus) and a few
libraries. So I've started to do the code using Postgres but now, I'm at a
situation where I'm looking at being forced to install oracle. Here are some
of the issues I'm finding:

- setBytes() doesn't work as I expect. I have to use Oracle's RAW type and I
  haven't been able to use setBytes correctly on Postgres. I'm not sure why,
  yet. I get cast exceptions, for one but I don't know why that would
  happen.

- sequences don't work the same. In my Java code, I have to get sequence
  numbers. It's fairly easy in Postgres (select nextval ('seq')) but the
  syntax in Oracle is different (select seq.nextval from DUAL).

I have to write Oracle-centric code in my Java stuff and I'm trying to see
if it's worth it to do some extra work to make it work or not. Anyone have
any comments on this?

L

--
Laurent Duperval <mailto:lduperval@microcelli5.com>

D'obscures manoeuvres ont eu lieu dans l'ombre et je ferai toute la lumière
sur ces ténébreux agissements! C'est du marché noir!
                                             -Achille Talon

You might try using a class structure which abstracts out various problems,
and has unique methods for each different implementation


ie a base class which would be something like SQLDatabase which implements
all of the various calls. Then you could extend this for each unique db such
as Oracle. So for oracle you would implement getUnique one way, and for
postgres you would implement the getUnique the postgres way.

This would probably have to be a singleton, and be created at run time.

Of course you could forgo all of the above and just use a persistence layer
such as castor and forget about sql all together ;)

If this is too brief let me know, and I will expand a little more

--dc--

----- Original Message -----
From: "Laurent Duperval" <lduperval@microcelli5.com>
To: <pgsql-jdbc@postgresql.org>
Sent: Wednesday, May 16, 2001 9:59 AM
Subject: [JDBC] Writing portable SQL code in Java


> Hi,
>
> Are there any guidelines for writing portable Java code using different
SQL
> DBMS engines? Specifically, I'm writing code that will be ultimately
> deployed on Oracle. I don't really care for encumbering my disk with 300
MB
> of Oracle stuff, just to have access to a bad ui (sqlplus) and a few
> libraries. So I've started to do the code using Postgres but now, I'm at a
> situation where I'm looking at being forced to install oracle. Here are
some
> of the issues I'm finding:
>
> - setBytes() doesn't work as I expect. I have to use Oracle's RAW type and
I
>   haven't been able to use setBytes correctly on Postgres. I'm not sure
why,
>   yet. I get cast exceptions, for one but I don't know why that would
>   happen.
>
> - sequences don't work the same. In my Java code, I have to get sequence
>   numbers. It's fairly easy in Postgres (select nextval ('seq')) but the
>   syntax in Oracle is different (select seq.nextval from DUAL).
>
> I have to write Oracle-centric code in my Java stuff and I'm trying to see
> if it's worth it to do some extra work to make it work or not. Anyone have
> any comments on this?
>
> L
>
> --
> Laurent Duperval <mailto:lduperval@microcelli5.com>
>
> D'obscures manoeuvres ont eu lieu dans l'ombre et je ferai toute la
lumière
> sur ces ténébreux agissements! C'est du marché noir!
>                                              -Achille Talon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

On 16 May, Dave Cramer wrote:
> You might try using a class structure which abstracts out various problems,
> and has unique methods for each different implementation
>
>
> ie a base class which would be something like SQLDatabase which implements
> all of the various calls. Then you could extend this for each unique db such
> as Oracle. So for oracle you would implement getUnique one way, and for
> postgres you would implement the getUnique the postgres way.
>

Taht was also suggested to me by private email. I think that's what I'd do.

> This would probably have to be a singleton, and be created at run time.
>
> Of course you could forgo all of the above and just use a persistence layer
> such as castor and forget about sql all together ;)
>

Oh, I didn't know about Castor. Yes, that's something we eventually want to
do but we can't in the short term. We've started looking at TopLink and
CocoBase also.

Thanks,

L

--
Laurent Duperval <mailto:lduperval@microcelli5.com>

On prend le bon air qui vivifie et qui se répand dans les cavités thoraciques
de l'honnête homme comme du miel sur la tartine d'un affamé!
                                             -Achille Talon