db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Closing connection in table function
Date Wed, 19 Nov 2008 14:26:24 GMT
Here are two possible solutions which may help. Note that I haven't 
test-driven these solutions, so some fine-tuning would be needed.

1) Add a database procedure to EmployeeTable to manage your connections. 
For instance, EmployeeTable could maintain a hashtable of Connections to 
the foreign database, keyed by a user-supplied handle (maybe a user 
name, maybe a sequence counter). You could then add this handle as an 
argument to the read() method and register a procedure which closes the 
handle when you're done with it. So EmployeeTable would have the 
following public static methods:

public static ResultSet read( String connectionHandle ) throws 
SQLException {...}

public static void commitConnection( String connectionHandle ) throws 
SQLException {...}

You would register these with Derby like this:

CREATE FUNCTION externalEmployees
( connectionHandle VARCHAR( 1000 ) )
  employeeId    INT,
  lastName      VARCHAR( 50 ),
  firstName     VARCHAR( 50 ),
  birthday      DATE
EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.read'

CREATE PROCEDURE commitConnection
( connectionHandle VARCHAR( 1000 ) )
EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.commitConnection'

You would invoke these in your program like this:

select * from table( externalEmployees( 'janet_connection' ) ) s
call commitConnection( 'janet_connection' )

2) Another solution would be to wrap a user-coded ResultSet around the 
ResultSet returned by EmployeeTable.read(). Mostly, the user-coded 
ResultSet would just forward its methods to the foreign ResultSet which 
it wraps. However, the close() method would also commit or close the 
foreign connection. So, for instance, EmployeeTable.read() would look 
like this:

    public  static  ResultSet   read()
        throws SQLException
        Connection          conn = getConnection();
        PreparedStatement   ps = conn.prepareStatement( "select * from 
hrSchema.EmployeeTable" );

        return new WrapperResultSet( conn, ps );

And WrapperResultSet would look something like this:

public class WrapperResultSet implements ResultSet
   private Connection _conn;
   private PreparedStatement _ps;
   private ResultSet    _raw;

   public WrapperResultSet( Connection conn, PreparedStatement ps ) 
throws SQLException
      _conn = conn;
     _ps = ps;
     _raw = ps.executeQuery();


  public boolean next() throws SQLException { return _raw.next(); }


  public void close() throws SQLException
      try { _raw.close(); }


Thanks for the feedback on the example. I can see that the example 
(which was supposed to be simple) raises some thorny issues. Those 
issues could use more discussion.

Hope this helps,

Reda134 wrote:
> Hi, 
> I've used the Java sample programm provided in the section " Example
> Derby-style table function " of the developper guide, to create table
> functions.
> this works fine, however i got problems if i want to join table functions
> with each other. The reason is that the JDBC connection to the external
> RDBMS, used in the read  method of the table function, will not be closed
> after derby calls the table function.
> So my question is, is threre any way to close the connection, after the
> result set is read ??
> Thank you in advance 

View raw message