db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: creating a SQL function in derby
Date Thu, 13 Aug 2009 19:59:45 GMT
Hi Thomas,

I apologize for misleading you on the topic of updating data inside the 
function. You are right: the CONTAINS SQL clause won't help you. If you 
can use a procedure rather than a function, then that would be a better 
solution. The procedure should be able to send back scalar values in OUT 
parameters. You would use a java.sql.CallableStatement to execute the 
procedure and then use the CallableStatement.getXXX() methods to 
retrieve the output scalar values. See the following section in the 
Reference Guide for information on how to code a method signature which 
can be bound to a procedure having an output parameter: 

Hope this helps,

Thomas Hill wrote:
> Hi Rick,
> thanks for that. It already helped a lot and I am starting to understand.
> I successfully wrote my first function reading the value stored on my ID table
> and returning it. What I can NOT figure out is: you advised that I need to
> create a function (rather than a procedure) and you stated to use the 'CONTAINS
> SQL' function element. Actually when I use 'CONTAINS SQL' when defining the
> function, I am getting an error that I am trying to read data, but have not
> specified 'READS SQL DATA'. When I change 'CONTAINS SQL' to 'READS SQL DATA' my
> function works and returns the result. However I need to not only select, but
> also update the data by incrementing the ID value on the ID table (see the
> select for update & update in my pgsql sample). But it seems that using a
> function I can not update data (and the explanation in the derby manual which is
> given for 'contains data' honestly doesn't make sense to me) and when using a
> procedure I could update data, but with a procedure not return any value.
> However I need to update data and return the new value so that I know which ID
> has been assigned. Can you help with the solution of this please?
> Thanks again
> Thomas

View raw message