Hi ,
Here is an example, hope that helps :
(Note: I am using a class Functions.java. The compiled class needs to be in the CLASSPATH)
ij> create table table_tmp(col1 int,col2 char(2)); 0 rows inserted/updated/deleted ij> insert into table_tmp values(10,'ca'); 1 row inserted/updated/deleted ij> select max(col1) from TABLE_TMP; 1 ----------- 10
1 row selected
ij> CREATE FUNCTION MYFUNC() returns BIGINT PARAMETER STYLE JAVA reads sql data language JAVA EXTERNAL NAME 'Functions.return
BigInt' ;
0 rows inserted/updated/deleted
ij> drop table abc;
0 rows inserted/updated/deleted
ij> create table abc(id bigint);
0 rows inserted/updated/deleted
ij> insert into abc values(myfunc());
1 row inserted/updated/deleted
ij> insert into abc values(myfunc());
1 row inserted/updated/deleted
ij> select * from abc;
ID
--------------------
10
10
-------------------------------------------------------------------------------
The Functions.java looks like:
public class Functions{public static long returnBigInt() throws Exception
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
Statement s1 = conn.createStatement();
ResultSet rs=s1.executeQuery("select max(col1) from TABLE_TMP ");
rs.next();
return rs.getInt(1);
}
}
-------------------------------------------------------------------------------
-Rajesh**************************************************************************** Bernd Ruehlicke wrote:
Hi there,
still Derby newby which is digging through the documentatins but cannot find wht he needs ...
trying to make a dum funtion MYFUNC without any parameters returning a number it gets via soem sql lookups. Any examples out there of how a function is to be declared without parameters and how the java program can do SQL against Derby ? So that I can use this function like
ij> values myfunc;
For the declaration I tried: CREATE FUNCTION MYFUNC RETURNS BIGINT PARAMETER STYLE JAVA CONTAINS SQL LANGUAGE JAVA EXTERNAL NAME 'com.xyz.DerbyFunctions.myfunc'
and got ij> CREATE FUNCTION TEST1 RETURNS BIGINT PARAMETER STYLE JAVA CONTAINS
SQL LANGUAGE JAVA EXTERNAL NAME 'com.xyz.DerbyFunctions.myfunc'; ERROR
42X01: Syntax error: Encountered "RETURNS" at line 1, column 23.
So QUESTION:
1) How to make this CREATE FUNC call correctly ? 2) Is it possible to make a function so I do not need to add () for no parameters 3) How to I do SQL against Derby in my Java static method ?!?!
Thanx B-)
