db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <ma...@Remulak.Net>
Subject Re: CREATE FUNCTION with SQL examples ?
Date Sat, 18 Dec 2004 00:47:55 GMT
Hi Ali,

I was wondering about the same but then I see most of the derby
tests which use jdbc:default:connection, close it before leaving the
function.

Couple tests that do this are
org.apache.derbyTesting.fucntionTests.tests.derbynet.callable.java
org.apache.derbyTesting.fucntionTests.tests.lang.outparams.java
org.apache.derbyTesting.fucntionTests.util.ProcedureTests.java

There are more.

Mamta

Suavi Ali Demir wrote:

> 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