db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernd Ruehlicke" <BRuehli...@lgc.com>
Subject RE: CREATE FUNCTION with SQL examples ?
Date Fri, 17 Dec 2004 22:34:08 GMT
Brillant response from you all - thanx a lot!

B-) 

> -----Original Message-----
> From: Rajesh Kartha [mailto:kartha@Source-Zone.Org] 
> Sent: Friday, December 17, 2004 4:06 PM
> To: Derby Discussion
> Subject: Re: CREATE FUNCTION with SQL examples ?
> 
> 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