Closing connection in table function

[Reda134 <re...@gmail.com>]

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 this message in context: http://www.nabble.com/Closing-connection-in-table-function-tp20577567p20577567.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Closing connection in table function

[Reda134 <re...@gmail.com>]

Hi Rick, 

Thanks a lot for your precious tips. I opted for the the second alternativ
(Creating a ResultSet wrapper), because it's less error-prone than the first
one.

Thank you again for your help.

-Reda
-- 
View this message in context: http://www.nabble.com/Closing-connection-in-table-function-tp20577567p20679258.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Closing connection in table function

[Rick Hillegas <Ri...@Sun.COM>]

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 ) )
RETURNS TABLE
(
  employeeId    INT,
  lastName      VARCHAR( 50 ),
  firstName     VARCHAR( 50 ),
  birthday      DATE
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.read'
;

CREATE PROCEDURE commitConnection
( connectionHandle VARCHAR( 1000 ) )
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL
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(); }
      finally
      {
          _raw.close();
          _ps.close();
          _conn.commit();
      }
  }

  ...
}


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,
-Rick


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