Re: CREATE FUNCTION with SQL examples ?

Rajesh Kartha 17 Dec 2004 23:37:16 -0000

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

Re: CREATE FUNCTION with SQL examples ?

Reply via email to