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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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-)
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>
> > >
> > >
> > >
> >
> >
