Thanks for starting this thread, Thomas. I am eager to learn more about
the real-life experience of developers who are writing portable
applications. Some comments inline...
On 11/27/12 6:29 AM, Thomas Hill wrote:
have written a couple of SQL Routines for Derby and am recently looking into
possibly re-using some of the code on another DBMS system also supporting Java
(PostgreSQL pl/Java). pl/Java for me is more a playground and subject of
curiosity then a real intention to use it productively at this stage. Need to
say the promise of Java in the data base is - from a portability point of
view - limited as my observations so far show implementations are back end
specific in many cases. Maybe Apache Derby and Oracle would be more
compatible, but Derby and PostgreSQL are not too often.
I would be very interested in learning more about the Derby/Postgres
portability problems you are seeing. Perhaps we can build more support
in Derby to help you bridge this gap.
However when there are
multiple implementation approaches possible, one might be favoured over the
other when portability is considered a requirement.
Specific question:
If there is a need for returning multiple out parameters from a routine (just
one row, not a set of rows), one might choose to implement a procedure in
Derby like this:
CREATE PROCEDURE xy(IN CLIENTID integer, OUT LASTNAME varchar(30), OUT
FIRSTNAME varchar(30)
or
CREATE PROCEDURE xy(IN CLIENTID integer) DYNAMIC RESULT SET 1
No preference on my part, just an observation: The first approach is
more strongly typed and lets you introspect the procedure without having
to call it (via DatabaseMetaData.getProcedureColumns()). Conversely, the
second approach is more dynamically typed and lets you return
differently shaped results at every invocation; you can only introspect
the result shape at run time (via ResultSetMetaData).
Thanks,
-Rick
Is one of these approaches to be favoured over the other? What is the best
pratice here? Any one to be favoured over the other when having Oracle
portability in mind?
Thanks a lot for sharing your experience and advise.
Kind regards
Thomas
