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 23:38:55 GMT
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