db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <kar...@Source-Zone.Org>
Subject Re: CREATE FUNCTION with SQL examples ?
Date Fri, 17 Dec 2004 22:06:02 GMT
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-)
>
>
>
>
>  
>


Mime
View raw message