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:35:39 GMT
Sending again: Yahoo has funny keyboard bindings,
previous was sent while typing...

To clarify: You should 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 sql
statement.

Example function:

  static private void signal_when_exists(String sql,
String[] params, String sqlState, String reason)throws
SQLException{
    //get the current connection:
    Connection con = DriverManager.getConnection(
"jdbc:default:connection");
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    try{
      pStmt = con.prepareStatement(sql);
      pStmt.setFetchSize(1);
      for(int i=0; i<params.length; i++){
        pStmt.setString(i+1, params[i]); //what if
null?
      }
      rs = pStmt.executeQuery();
      if(rs.next()){ 
        signal(sqlState, reason);      
      }
    }
    finally{
      Util.close(rs);
      Util.close(pStmt);
    }
  }


Regards,
Ali

--- 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