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