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:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> > 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.
<<winmail.dat>>
