db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Hill <thomas.k.h...@t-online.de>
Subject best practice - SQL Routines - return value handling
Date Tue, 27 Nov 2012 14:29:31 GMT
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. 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

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