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 Sat, 18 Dec 2004 00:34:04 GMT
To clarify: You shoul NOT close the connection that
you get from the URL jdbc:default:connection. This is
not a new connection. This is the connection that your
stored proc or function is running in the context of.
It was created by the code that is executing the sq





--- Rajesh Kartha <kartha@Source-Zone.Org> wrote:

> Hi,
> 
> The snippet was just a simple example. Sure...
> close() is needed (a good 
> practice),  I think it is safe to close the
> connection too,
> since  the function is  supposed to return the value
> and won't be 
> needing the connection.
> 
> In fact I did try the following out and it worked
> fine.
> 
> import java.sql.*;
> public class Functions{
> 
>     public static long returnBigInt() throws
> Exception
>     {
>         Connection conn=null;
>     try{
>         conn =
>
DriverManager.getConnection("jdbc:default:connection");
> 
>         Statement s1 = conn.createStatement();
>         ResultSet rs=s1.executeQuery("select
> max(col1) from TABLE_TMP ");
>         rs.next();
>         long l= rs.getInt(1);
>         rs.close();
>         s1.close();
>         return l;
>     }catch(Exception e){
>         conn.close();
>         throw e;
>     }
>     finally{
>             conn.close(); //can add try and catch
> here too, should you need
>     }
> }
> 
> Do let me know if it is otherwise
> 
> -Rajesh
> 
> 
> 
> Suavi Ali Demir wrote:
> 
> >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