Re: Using a function that reads AND modifies data

[Jeremy Boynes]

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