I am still having a hard time get the event listener to provide useful
feedback. I understand, I think, all the conceptual issues, but not
how to correctly write the code to do it.
First, I begin running H2 in server mode from a console window:
java -cp h2-latest.jar;VAriskForTesting.jar org.h2.tools.Server -tcp
-tcpPort 9091 -tcpAllowOthers
TCP server running at tcp://192.168.0.102:9091 (others can connect)
Second, I connect to the server from an application. It's currently
running in Eclipse, but that ought to be irrelevant. Here's the
connection URL:
jdbc:h2:tcp://192.168.0.102:9091/file://K:\uncompressed\VARiskDB
(File name and ip address are, of course, not hard-coded.)
Now then:
I want to get feedback from the H2 database during long processes,
such as creating an index. I don't want the listener connected all the
time. I want to add a listener, run a process, use the listener to
give the user feedback on the process, then, when it's done, stop
listening.
My problems arise because I can only pass the listener as a CLASS. I
can't pass an instance with pre-set variables. I can't "get" the
instance of the listener H2 has created and set variables in it, or
read its state. My ability to communicate data to/from the listener is
very, very, limited.
So here's what I've been trying. I have a "test code" button that does this:
Connection conn=DatabaseLinks.projectData.getConn();
Statement s = conn.createStatement();
s.execute("SET DATABASE_EVENT_LISTENER
'"+H2DBListener.class.getName()+"'");
s.execute("CREATE INDEX SCENARIOCOLUMN_IDX ON
SCENARIOVALUE(COLUMNID)");
//ResultSet rs1=s.executeQuery("SELECT * FROM
ALL_COMMASREMOVED
WHERE PROPERTYSTATEFK='KS'");
ResultSet rs=s.executeQuery("SELECT * FROM
TEMPTABLE");
while(rs.next())
{
System.out.println(rs.getInt(1)+"
"+rs.getString(2));
}
This gets a connection, using the URL I mentioned above, sets the
DBEventListener on it, and then begins a long process.
Now, here's what the relevant parts of my DBListener class looks like.
This class is written to a JAR that's added to the H2 classpath.
@Override
public void init(String url)
{
System.out.println("Listener initialized.");
try
{
System.out.println(url);
String filePart=url.substring(url.indexOf("file:"));
url="jdbc:h2:tcp://192.168.0.102:9091/"+filePart;
System.out.println("Modified URL:"+url);
this.tempDBName = url;
conn=DriverManager.getConnection(tempDBName, "sa", "");
s=conn.createStatement();
s.execute("CREATE MEMORY TABLE IF NOT EXISTS
TEMPTABLE(ID INT
PRIMARY KEY, NAME VARCHAR(255))");
System.out.println("Mem table created.");
}
catch (Exception e)
{
e.printStackTrace();
}
}
The URL that this creates is identical to the URL used to connect to
the DB in the app in the first place. As you see, it creates a very
generic memory table. My understanding, perhaps wrong, is that this
memory table exists as part of the DB specified in the "File:" part of
the URL.
Later in the EventListener, we have this:
@Override
public void setProgress(int state, String name, int x, int max)
{
try
{
String st="INSERT INTO TEMPTABLE
VALUES(null,'"+name+"_"+x+"_"+max+"')";
s.execute(st);
System.out.println(st);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
As you see, my goal is currently to have the event listener write
progress events to the memory table. I figure if I can do that, I can
figure out how to read from the table, possibly in another thread, to
drive a dialog box. All I'm trying to do now is see if my action
(creating the index) is being tracked by SetProgress.
What happens is, when I execute my code, things seem to hang. It
appears, after some very slow testing, that it's creating the index
properly, but the code in setProgress is never being executed. The
code in init(), however, IS being executed.
I have considered the problem of threading -- the event listener code
can't do anything until the index is created -- but that doesn't seem
likely. The event listener should be in a different thread than the
code which is creating the index. (It happens to be, for testing
purposes ONLY, attached to a click event on a form; this is not how I
normally write code.)
I considered that, when I create the connection in the listener.init()
code, I should specify a listener, but, that would seemingly lead to
an infinite loop as each listener inited itself.
Suggestions? Clearly, there's something fairly basic I'm missing in my
understanding, my implementation, or both.
Once I get past this basic hurdle, what I imagine doing is something like:
User initiates a long process.
I toss up a dialog box.
The dialog box calls some other classes which create a DB connection
with a listener, and launch a thread which actually executes the
long-running query.
While that thread is alive, the dialog box loops, querying the memory
DB the listener created and populates, and uses the data it retrieves
from those queries to drive the displayed progress.
I'm going to keep experimenting, so it's possible I may find the
solution before anyone read this. However, I figure at this point it's
best to get the ball rolling, help-wise.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
