db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rhys Campbell" <rhys.campb...@da-systems.co.uk>
Subject RE: No current connection
Date Fri, 03 Mar 2006 22:53:22 GMT
Mike,

Thanks for your suggestions. I think I am going to try to rewrite some sections of code to
pass the database connection in the constructor as it seems to make sense for my application
to do this (as some classes will be created and destroyed many times).

I'm not convinced it's due to scope, for example (bit messy, do intend to sort it out a bit
better)

[code]
	/**
	 *  Description of the Method
	 */
	private void recreateViews()
	{
		String noView = "X0X05";
		// Derby specific Error code when trying to drop a view that does not exist
		DBTools database = new DBTools();
		
		try
		{
			database.executeUpdate(connection, "DROP VIEW view_active_logins");
			Logger logger = new Logger("Dropped view view_active_logins");
		}
		catch (SQLException excep)
		{
			if (excep.getSQLState().equals(noView))
			{
				Logger logger = new Logger("Normal Error thrown by Derby when attempting to drop a view
that does not exist: " + excep.getMessage());
				//logger.sendLogMessage("Normal Error thrown by Derby when attempting to drop a view that
does not exist: " + excep.getMessage());
			}
			else
			{
				Logger logger = new Logger(excep.getMessage());
				//logger.sendLogMessage("Error: " + excep.getMessage());
			}
		}
		try
		{
			database.executeUpdate(connection, "DROP VIEW view_inactive_logins");
			Logger logger = new Logger("Dropped view view_inactive_logins");
		}
		catch (SQLException excep)
		{
			if (excep.getSQLState().equals(noView))
			{
				Logger logger = new Logger("Normal Error thrown by Derby when attempting to drop a view
that does not exist: " + excep.getMessage());
			}
			else
			{
				Logger logger = new Logger(excep.getMessage());
			}
		}
		try
		{
			database.executeUpdate(connection, "DROP VIEW view_logged_out");
			Logger logger = new Logger("Dropped view view_logged_out");
		}
		catch (SQLException excep)
		{
			if (excep.getSQLState().equals(noView))
			{
				Logger logger = new Logger("Normal Error thrown by Derby when attempting to drop a view
that does not exist: " + excep.getMessage());
			}
			else
			{
				Logger logger = new Logger(excep.getMessage());
			}
		}
		try
		{
			// Creates the View showing active logins
			database.executeUpdate(connection, "CREATE VIEW view_active_logins AS " +
					"SELECT * " +
					"FROM logins " +
					"WHERE lgn_lastactive BETWEEN {fn TIMESTAMPADD(SQL_TSI_SECOND, " + "-" + GLOBAL_OPTIONS.INACTIVE
+ ", CURRENT_TIMESTAMP)} " +
					"AND CURRENT_TIMESTAMP");
			Logger logger = new Logger("Created view view_active_logins");
		}
		catch (SQLException excep)
		{
			Logger logger = new Logger(excep.getMessage());
		}
		try
		{
			// Creates the view showing inactive logins (Logged in but considered inactive)
			database.executeUpdate(connection, "CREATE VIEW view_inactive_logins AS " +
					"SELECT * " +
					"FROM logins " +
					"WHERE lgn_lastactive BETWEEN {fn TIMESTAMPADD(SQL_TSI_SECOND, " + "-" + GLOBAL_OPTIONS.LOGGED_OUT
+ ", CURRENT_TIMESTAMP)} " +
					"AND {fn TIMESTAMPADD(SQL_TSI_SECOND, " + "-" + (GLOBAL_OPTIONS.INACTIVE - 1) + ", CURRENT_TIMESTAMP)}");
			Logger logger = new Logger("Created view view_inactive_logins");
		}
		catch (SQLException excep)
		{
			Logger logger = new Logger(excep.getMessage());
		}
		try
		{
			// Creates the view showing units logged out
			database.executeUpdate(connection, "CREATE VIEW view_logged_out AS " +
					"SELECT * " +
					"FROM logins " +
					"WHERE lgn_lastactive < {fn TIMESTAMPADD(SQL_TSI_SECOND, " + "-" + GLOBAL_OPTIONS.LOGGED_OUT
+ ", CURRENT_TIMESTAMP)}");
			Logger logger = new Logger("Created view view_logged_out");
		}
		catch (SQLException excep)
		{
			Logger logger = new Logger(excep.getMessage());
		}
	}
[/code]

The best I have got this method to do is 4/6 of the SQl statements (the 3 DROPS and a single
create). Ihave another class called GLOBAL_OPTIONS and Idid put the DB connection stuff in
their but that had the same result. Ieven wrote a little method called checkDB() that I put
before each attempted SQL statement. This would check if the connection was null and re-initialise
it if so.

If I'm understanding things correctly, if it was a question of scope then everything in the
method would fail? Anyway, I'm going to start a rewrite in the morning as I think I'll make
a worse mess of things if I continue. I've attached some of my relevant code it would be great
if you could have a quick look. There are some methods which probably should be there as I'm
flipping between ways of doing things.

Thanks,

Rhys




-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com]
Sent: Fri 3/3/2006 5:58 PM
To: Derby Discussion
Subject: Re: No current connection
 
On Friday 03 March 2006 9:41 am, Rhys Campbell wrote:
> Initially I was doing it the start of each class and reusing the same
> connection variable. But Derby seemed to auto-close the connection after
> each statement.
>
Uhmm. No. Derby shouldn't be doing an auto-close after each connection.
I think the reason that you see that, is because you're not managing your 
scope correctly. 

Try this...

In your main java class, instantiate a reference to a Connection object and 
set it to null, as a class level variable. 
Then in your initialization method, open a Derby connection using your 
Connection object.

For each of your "business" classes, create an object reference of a 
Connection class. 
Then in each "business" object that you instantiate, pass in a reference to 
your connection object and assign it to the connection object that you just 
created.

As I'm writing this, I realize that the description may be a tad confusing. 
Lets see if I can give you a better example....
NOTE: This is just an example, to show an idea. please ignore the syntax as I 
am writing this free hand from memory....

You have a Main.java class:
import blah blah blah
public class Main {

    Connection con = null;

   public Main(){
      // Instantiation code
   }

    public static void main(){
        initDBConnection();  // method of this class....
        YourClassFoo foo = new YourClassFoo(blah, blah, con); 
        YourClassBar bar = new YourClassBar(blah, con, blah, blah, and, blah);
    }

   private void Connection initDBConnection(){
        // Build your URL and open a derby connection;
        con = DriverManager.getConnection("URL");
  }
} // End of Main class

Now here's your YourClassFoo.java 
public class YourClassFoo {
    Connection con = null;
    // Instantiate references to other class variables....

    public YourClassFoo(blah, blah, Connection con_in){
         ...
        con = con_in;
        ...
    }

    // now the rest of your class code...
}

In this example,  a reference to a Connection object con, is created at the 
time that the Main object is instantiated.  It then gets assigned a reference 
to an instantiation of a Connection object when your main() method calls 
initDBConnection() in your Main class.

Then when you instantiate your object foo of YourClassFoo, you pass in a 
reference to an object of type Connection, which gets assigned to the 
reference to its own internal Connection object.

So the actual Connection object is instantiated when you call 
DriverManager.getConnection() and then your classes maintain a reference to 
this object. As long as you have a reference to the instantiated object, it 
will not be sent off to the Garbage Collector. 

Does this make sense?

I realize that this is a really basic example with a couple of gaps in it, but 
it should give you a good idea about scope. 

Now a caveat... In a real life production example, you'd want to create a 
public method setDBConnection(Connection con_in)  so that if you lost your 
connection, or had to recreate or re-establish the connection, you could then 
reset it within your "business" objects without having to reinstantiate a 
whole new business object.

HTH,
-Mikey

PS. Anyone can write crap that works. Its only the true artisan/craftman, who 
will take the time to polish their skills and write code that is functional, 
efficent, and easy to maintain.

> I was burning the midnight oil on it last night. I realize it's not the
> most efficient way of doing things, but wouldn't this eliminate problems
> of scope?
>
> I'll be looking at it with a fresher head tonight!
>
> Rhys
>
> -----Original Message-----
> From: Michael Segel [mailto:msegel@segel.com]
> Sent: 03 March 2006 14:46
> To: Derby Discussion
> Subject: Re: No current connection
>
> On Friday 03 March 2006 7:58 am, Rhys Campbell wrote:
> > Hi,
> >
> > I have a method called initDatabase() which returns the Connection
> > variable. When I execute any query against the database I call another
> > method of which the first parameter is the Connection (i.e. I place
> > initDatabase() as the parameter.
> >
> > i.e. ResultSet rs = database.executeQuery(initDatabase(), "SELECT *
>
> FROM
>
> > myTable"); (Code from memory!)
>
> Eeewww.
>
> Uhm... initDatabase() is a method within your current class. Ok...
> By the name of the function, you're implying that you're opening up a
> data
> connection each time initDatabase is called....
>
> Lots of overhead that could be adverted if you change the scope of the
> instance of the Connection object.
>
> > Connections are not passed between the main (business logic) classes.
> > They all have access to a little class called DBTools which I wrote to
> > simplify DB access.
> >
> > I have a play with your ideas this evening and maybe post my code.
> > Thanks for your thoughts.
> >
> > Regards,
> >
> > Rhys
> >
> > -----Original Message-----
> > From: Michael Segel [mailto:msegel@segel.com]
> > Sent: 03 March 2006 13:36
> > To: Rhys Campbell
> > Subject: Re: No current connection
> >
> > On Thursday 02 March 2006 7:20 pm, you wrote:
> > > Hi,
> > >
> > > I'm using Derby for a current project for the first time. Got on
>
> with
>
> > it
> >
> > > pretty well until tonight.
> > >
> > > My app starts up a database, drops a few views and then recreates
> >
> > them.
> >
> > > When another one of my Java classes moves on to do a small SELECT
> >
> > (that
> >
> > > should yield a single result). The exception "No current connection"
> >
> > is
> >
> > > raised with the query obviously failing.
> > >
> > > I have checked the Derby.log but I am warned that the file is
>
> binary,
>
> > when
> >
> > > it opens in my editor, and a load of "junk" is displayed. I did use
>
> a
>
> > > little tool called "fd" which is a oct / hex character editor. This
> >
> > did
> >
> > > make some sections of text legible but I couldn't see anything
>
> useful
>
> > to
> >
> > > explain my issue.
> > >
> > > Can anyone offer me any pointers?
> > >
> > > Many thanks,
> > >
> > > Rhys
> >
> > Just to update the post I made...
> >
> > Since you mention multiple classes...
> >
> > 1) Check the scope of the connection declaration and are you passing
>
> it
>
> > in as
> > a parameter of your new class when you instantiate it?
> >
> > 2) Are you overwritting your connection in your new class, losing the
> > connection for that instance of the class variable?
> >
> > Again, just shooting from the hip.





Mime
View raw message