db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suavi Ali Demir <dem...@yahoo.com>
Subject Re: CREATE FUNCTION with SQL examples ?
Date Fri, 17 Dec 2004 22:56:47 GMT
Rajesh, do you not need to close your statement and
result set objects in a try-finally block? rs.close
and s1.close() would be needed, no? But you should NOT
close the connection, right?

Regards,
Ali

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 Kartha <kartha@Source-Zone.Org> wrote:
> 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