#417 Connection doesn't close when ending session

2.2final
closed-fixed
Rob Manning
Core (461)
5
2007-12-21
2006-08-16
Jeff Moody
No

Once a db connection is made, there's no way to
disconnect without closing the app.
I would think that either there would be a menu item,
button, etc. to disconnect from the db or that the
disconnect would happen automatically when closing the
session.

I'm using an Apache Derby db and Java 1.4.2_11-b06.

Discussion

  • Rob Manning
    Rob Manning
    2006-09-04

    Logged In: YES
    user_id=1287991

    Are you running in Derby in embedded mode? If so, this has
    more to do with the fact that the JVM that SQuirreL is
    running in *is* the one that Derby is also running in. In
    that case, it is not possible to "disconnect" in the normal
    sense as the driver is accessing data in-memory and not via
    a network socket. I run the Derby network server and use
    the client driver (org.apache.derby.jdbc.ClientDriver) to
    access it. In this way, I can access it with multiple
    connections from different instances of SQuirreL. How
    are you determining that SQuirreL hasn't disconnected from
    the derby database when the session is closed?

    Rob

     
  • Jeff Moody
    Jeff Moody
    2006-09-08

    Logged In: YES
    user_id=1576556

    I am running Derby in embedded mode.
    I know that SQuirreL hasn't disconnected when the session is
    closed because only one connection is allowed and I am
    unable to connect to the db by other means (through our
    application or via the Database Explorer that is built into
    Eclipse). After closing SQuirrel, I can connect to the db.
    Using the Derby network server is not an option for our
    development.

     
  • Rob Manning
    Rob Manning
    2006-09-08

    • assigned_to: colbell --> manningr
     
  • Rob Manning
    Rob Manning
    2006-09-08

    Logged In: YES
    user_id=1287991

    I believe that when running in embedded mode, only one
    application (JVM) may access the database files. This is
    the essence of running a database in embedded mode -
    very fast access, very limited scope - one process.
    I've also seen this behavior from the Axion database
    driver. If you come across a mechanism to allow the
    JVM to "disengage" the database files from embedded
    mode using JDBC, by all means, please enlighten us :)
    As you can see, closing the connection is not enough.

    Rob

     
  • Logged In: NO

    If you are running Apache derby, then an interesting compromise is to run the database as wha t they call an "embedded server". Simply start the database in your application as an embedded database, then start the network server on another thread.
    Once you do this, you can connect to the database using the Apache client module of Squirrel. In the mean time your application continues to access the database as an embedded one. Thus you sort of get what you want.

     
  • Alex Pivovarov
    Alex Pivovarov
    2007-12-20

    Logged In: YES
    user_id=1636838
    Originator: NO

    patch which fix it

    diff -cBb old/DerbyPlugin.java new/DerbyPlugin.java
    *** old/DerbyPlugin.java Tue Jul 17 23:08:24 2007
    --- new/DerbyPlugin.java Thu Dec 20 09:45:16 2007
    ***************
    *** 18,23 ****
    --- 18,28 ----
    * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    */

    + import java.net.MalformedURLException;
    + import java.sql.Driver;
    + import java.sql.SQLException;
    + import java.util.Properties;
    +
    import net.sourceforge.squirrel_sql.client.IApplication;
    import net.sourceforge.squirrel_sql.client.gui.session.ObjectTreeInternalFrame;
    import net.sourceforge.squirrel_sql.client.gui.session.SQLInternalFrame;
    ***************
    *** 26,37 ****
    --- 31,47 ----
    import net.sourceforge.squirrel_sql.client.plugin.PluginSessionCallback;
    import net.sourceforge.squirrel_sql.client.session.IObjectTreeAPI;
    import net.sourceforge.squirrel_sql.client.session.ISession;
    + import net.sourceforge.squirrel_sql.client.session.event.SessionAdapter;
    + import net.sourceforge.squirrel_sql.client.session.event.SessionEvent;
    import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.expanders.TableWithChildNodesExpander;
    import net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.DatabaseObjectInfoTab;
    import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory;
    import net.sourceforge.squirrel_sql.fw.gui.GUIUtils;
    + import net.sourceforge.squirrel_sql.fw.id.IIdentifier;
    import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
    import net.sourceforge.squirrel_sql.fw.sql.IQueryTokenizer;
    + import net.sourceforge.squirrel_sql.fw.sql.ISQLDriver;
    + import net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager;
    import net.sourceforge.squirrel_sql.fw.util.StringManager;
    import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
    import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
    ***************
    *** 50,56 ****
    */
    public class DerbyPlugin extends DefaultSessionPlugin {

    ! private static final StringManager s_stringMgr =
    StringManagerFactory.getStringManager(DerbyPlugin.class);

    /** Logger for this class. */
    --- 60,66 ----
    */
    public class DerbyPlugin extends DefaultSessionPlugin {

    ! static final StringManager s_stringMgr =
    StringManagerFactory.getStringManager(DerbyPlugin.class);

    /** Logger for this class. */
    ***************
    *** 97,103 ****
    */
    public String getVersion()
    {
    ! return "0.11";
    }

    /**
    --- 107,113 ----
    */
    public String getVersion()
    {
    ! return "0.12";
    }

    /**
    ***************
    *** 116,122 ****
    * @return Contributors names.
    */
    public String getContributors() {
    ! return "";
    }

    /**
    --- 126,132 ----
    * @return Contributors names.
    */
    public String getContributors() {
    ! return "Alex Pivovarov";
    }

    /**
    ***************
    *** 159,164 ****
    --- 169,176 ----
    public synchronized void initialize() throws PluginException
    {
    super.initialize();
    + //Add session ended listener -- needs for Embedded Derby DB
    + _app.getSessionManager().addSessionListener(new SessionListener());
    }

    /**
    ***************
    *** 221,227 ****
    return DialectFactory.isDerby(session.getMetaData());
    }

    ! private void updateTreeApi(ISession session) {

    _treeAPI = session.getSessionInternalFrame().getObjectTreeAPI();

    --- 233,239 ----
    return DialectFactory.isDerby(session.getMetaData());
    }

    ! void updateTreeApi(ISession session) {

    _treeAPI = session.getSessionInternalFrame().getObjectTreeAPI();

    ***************
    *** 252,255 ****
    --- 264,320 ----

    }

    + /**
    + * A session listener that shutdown Embedded Derby when
    + * session and connection are already closed
    + * @author Alex Pivovarov
    + */
    + class SessionListener extends SessionAdapter {
    + @Override
    + public void sessionClosed(SessionEvent evt) {
    + ISession session = evt.getSession();
    + shutdownEmbededDerby(session);
    + }
    + }
    +
    + /**
    + * Shutdown Embedded Derby DB and reload JDBC Driver
    + *
    + * @param session Current session.
    + *
    + * @author Alex Pivovarov
    + */
    + protected void shutdownEmbededDerby(ISession session) {
    + try {
    + ISQLDriver iSqlDr = session.getDriver();
    + if (!(iSqlDr.getDriverClassName().startsWith("org.apache.derby.jdbc.EmbeddedDriver"))) {
    + return;
    + }
    + //the code bellow is only for Embedded Derby Driver
    + IIdentifier drId = iSqlDr.getIdentifier();
    + SQLDriverManager sqlDrMan = _app.getSQLDriverManager();
    + //Gatting java.sql.Driver to run shutdown command
    + Driver jdbcDr = sqlDrMan.getJDBCDriver(drId);
    + //Shutdown Embedded Derby DB
    + try {
    + jdbcDr.connect("jdbc:derby:;shutdown=true", new Properties());
    + } catch (SQLException e) {
    + //it is always thrown as said in Embedded Derby API.
    + //So it is not error it is info
    + s_log.info(e.getMessage());
    + }
    + //Rereginstering driver is necessary for Embedded Derby
    + sqlDrMan.registerSQLDriver(iSqlDr);
    + } catch (RuntimeException e) {
    + s_log.error(e.getMessage());
    + } catch (MalformedURLException e) {
    + s_log.error(e.getMessage());
    + } catch (IllegalAccessException e) {
    + s_log.error(e.getMessage());
    + } catch (InstantiationException e) {
    + s_log.error(e.getMessage());
    + } catch (ClassNotFoundException e) {
    + s_log.error(e.getMessage());
    + }
    + }
    }

     
  • Rob Manning
    Rob Manning
    2007-12-21

    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2007-12-21

    Logged In: YES
    user_id=1287991
    Originator: NO

    Thank you, Alex! It seems to work very well. I tested it out and see no room for improvement, so I committed to the trunk. Well done!

    Rob

     
  • Rob Manning
    Rob Manning
    2007-12-21

    Logged In: YES
    user_id=1287991
    Originator: NO

    Thank you, Alex! It seems to work very well. I tested it out and see no room for improvement, so I committed to the trunk. Well done!

    Rob