db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: best practice - SQL Routines - return value handling
Date Tue, 27 Nov 2012 15:38:42 GMT
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
>
>
>


Mime
View raw message