db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: Using a function that reads AND modifies data
Date Mon, 04 Apr 2005 17:48:41 GMT
The grammar does not allow for FUNCTIONs that modify data - MODIFIES SQL 
DATA can only be used with procedures. I believe other databases (e.g. 
Oracle) have a similar restriction.

The spec defines various restrictions on when such functions can be 
used, I believe so that you get consistency depending on how the 
optimizer decides to run the query.

--
Jeremy

Sean Kelly wrote:
> I defined a Derby function that calls a static method as follows:
> 
> CREATE FUNCTION x() RETURNS INTEGER
>   PARAMETER STYLE JAVA CONTAINS SQL LANGUAGE JAVA
>   EXTERNAL NAME 'com.huge.Funcs.x';
> 
> This function both reads and writes to tables using a nested 
> connection.  When called, Derby complains, rightfully:
> 
> "The routine attempted to read data, but the routine was not defined as 
> READS SQL DATA."
> 
> So, I changed the CREATE FUNCTION to read:
> 
> CREATE FUNCTION x() RETURNS INTEGER
>   PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA
>   EXTERNAL NAME 'com.huge.Funcs.x';
> 
> This time, when called, Derby complains:
> 
> "The routine attempted to modify data, but the routine was not defined 
> as MODIFIES SQL DATA."
> 
> So, I again changed it again to read:
> 
> CREATE FUNCTION x() RETURNS INTEGER
>   PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA
>   EXTERNAL NAME 'com.huge.Funcs.x';
> 
> But this time, Derby complains about the CREATE FUNCTION itself:
> 
> "Syntax error: MODIFIES SQL DATA."
> 
> So, Derby says I have to use MODIFIES SQL DATA, but it's a syntax error 
> to do so?  What's up with that?
> 
> Thanks for any insight.
> --Sean
> 


Mime
View raw message