Using a function that reads AND modifies data

[Sean Kelly <ke...@seankelly.biz>]

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

Re: Using a function that reads AND modifies data

[Satheesh Bandaram <sa...@Sourcery.Org>]

Sean, Derby functions don't allow modifying SQL data. You could use
procedures to modify data instead. Take a look at Derby referece manual
for a description of what each of those modifiers mean.

http://incubator.apache.org/derby/manuals/reference/sqlj27.html#CREATE+PROCEDURE+Statement

Satheesh

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
>
>
>

Re: Using a function that reads AND modifies data

[Jeremy Boynes <jb...@apache.org>]

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
>