You are not logged in.

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

1

Saturday, January 11th 2014, 6:19pm

h2 database - DATABASE_EVENT_LISTENER

Hey,
ich komme hier grad nicht weiter! Vielleicht kann mir ja jemand helfen.
Ich möchte auf eine bestehende h2 Datenbank einen DATABASE_EVENT_LISTENER registrieren, um mit der setProgress()
Methode z.B. den Fortschritt einer umfangreichen Sql MERGE Anweisung abzufragen.
In den h2 Docs gibt es dazu folgendes Java Beispiel; ABER wie Setze ich das jetzt In Jabaco um?

Source code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
/*
 * Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License,
 * Version 1.0, And under the Eclipse Public License, Version 1.0
 * (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.samples;

Import java.sql.Connection;
Import java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import java.sql.SQLException;
Import java.sql.Statement;

Import org.h2.api.DatabaseEventListener;
Import org.h2.jdbc.JdbcConnection;

/**
 * This example application Implements a database Event listener.
 * This Is useful To display progress information While opening a large database,
 * Or To log database exceptions.
 */
Public class ShowProgress Implements DatabaseEventListener {

    Private final Long start;
    Private Long last;

    /**
     * Create a New instance of this class, And start the timer.
     */
    Public ShowProgress() {
        start = last = System.currentTimeMillis();
    }

    /**
     * This method Is called when executing this sample application from the
     * command line.
     *
     * @param args the command line parameters
     */
    Public Static void main(String... args) throws Exception {
        New ShowProgress().test();
    }

    /**
     * Run the progress test.
     */
    void test() throws Exception {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:test", "sa", "");
        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))");
        Long Time;
        Time = System.currentTimeMillis();
        int len = 1000;
        For (int i = 0; i < len; i++) {
            Long now = System.currentTimeMillis();
            If (now > Time + 1000) {
                Time = now;
                System.out.println("Inserting " + (100L * i / len) + "%");
            }
            prep.setInt(1, i);
            prep.execute();
        }
        Boolean abnormalTermination = True;
        If (abnormalTermination) {
            ((JdbcConnection) conn).setPowerOffCount(1);
            try {
                stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))");
            } catch (SQLException e) {
                // ignore
            }
        } Else {
            conn.close();
        }

        System.out.println("Open connection...");
        Time = System.currentTimeMillis();
        conn = DriverManager.getConnection("jdbc:h2:test;DATABASE_EVENT_LISTENER='" + getClass().getName() + "'", "sa", "");
        Time = System.currentTimeMillis() - Time;
        System.out.println("Done after " + Time + " ms");
        prep.close();
        stat.close();
        conn.close();

    }

    /**
     * This method Is called If an exception occurs In the database.
     *
     * @param e the exception
     * @param sql the SQL statement
     */
    @Override
    Public void exceptionThrown(SQLException e, String sql) {
        System.out.println("Error executing " + sql);
        e.printStackTrace();
    }

    /**
     * This method Is called when opening the database To notify about the progress.
     *
     * @param state the current state
     * @param name the Object name (depends On the state)
     * @param current the current progress
     * @param max the 100% mark
     */
    @Override
    Public void setProgress(int state, String name, int current, int max) {
        Long Time = System.currentTimeMillis();
        If (Time < last + 5000) {
            Return;
        }
        last = Time;
        String stateName = "?";
        switch (state) {
        Case STATE_SCAN_FILE:
            stateName = "Scan " + name;
            break;
        Case STATE_CREATE_INDEX:
            stateName = "Create Index " + name;
            break;
        Case STATE_RECOVER:
            stateName = "Recover";
            break;
        default:
            Return;
        }
        try {
            Thread.sleep(1);
        } catch (InterruptedException e) {
            // ignore
        }
        System.out.println("State: " + stateName + " " + (100 * current / max) + "% (" + current + " of " + max + ") "
                + (Time - start) + " ms");
    }

    /**
     * This method Is called when the database Is closed.
     */
    @Override
    Public void closingDatabase() {
        System.out.println("Closing the database");
    }

    /**
     * This method Is called just after creating the instance.
     *
     * @param url the database URL
     */
    @Override
    Public void init(String url) {
        System.out.println("Initializing the event listener for database " + url);
    }

    /**
     * This method Is called when the database Is open.
     */
    @Override
    Public void opened() {
        // Do Nothing
    }

}


Bin immer noch nicht ganz so fit mit Java!!
Ich hab einfach mal 1:1 die Subs umgeschrieben, um einen Anfang zu haben.

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
Option Explicit

'   Import java.sql.Connection
'   Import java.sql.DriverManager
'   Import java.sql.PreparedStatement
'   Import java.sql.SQLException
'   Import java.sql.Statement

'   Import org.h2.api.DatabaseEventListener
'   Import org.h2.jdbc.JdbcConnection
'   Implements org#h2#api#DatabaseEventListener 'implements the following methods

Dim conn As Connection 
Dim qry As String 
Dim stat As java#sql#Statement 
Dim rs As ResultSet
Dim last As Long, start As Long
   
Public Sub init(arg2 As String)
   System.out.println("Initializing the event listener for database " & arg2)
End Sub

Public Sub opened()
   System.out.println("Database opened")
End Sub

Public Sub exceptionThrown(arg2 As SQLException, arg3 As String)
   System.out.println("Error executing " & arg3)
   arg2.printStackTrace()
End Sub

Public Sub setProgress(arg2 As Integer, arg3 As String, arg4 As Integer, arg5 As Integer)
   Dim Time As Long
   Time = System.currentTimeMillis()
   If (Time < last + 5000) Then Exit Sub 'Return
   last = Time
   Dim stateName As String = "?"
   Select Case arg2
      Case STATE_SCAN_FILE:
         stateName = "Scan " & arg3
      '               break;
      Case STATE_CREATE_INDEX:
         stateName = "Create Index " & arg3
      '               break;
      Case STATE_RECOVER:
         stateName = "Recover"
      '               break;
   End Select
   On Error Resume Next
   Thread.sleep(1)
   System.out.println("State: " & stateName & " " & (100 * arg4 / arg5) & "% (" & arg4 & " of " & arg5 & ") " & (Time - start) + " ms")
End Sub

Public Sub closingDatabase()
   System.out.println("Closing the database")
End Sub

Public Sub diskSpaceIsLow(arg2 As Long)
   System.out.println("diskSpaceIsLow stillAvailable=" & arg2)
End Sub

Public Sub ShowProgress()
		last = System.currentTimeMillis()
		start = last
End Sub
    
Public Sub Form_Load()
   java#lang#Class.forName("org.h2.Driver")
   Dim MyDatabaseListener As org#h2#api#DatabaseEventListener

   conn = DriverManager.getConnection("jdbc:h2:" & "C:\test", "sa", "")
   stat = conn.createStatement()
   stat.execute("DROP TABLE IF EXISTS TEST")
   stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)")
   Dim prep As java#sql#PreparedStatement
   prep = conn.prepareStatement("INSERT INTO TEST VALUES(?,'Test' || SPACE(100))")
   
   Dim Time As Long
   Time = System.currentTimeMillis()
   Dim len1 As Integer = 1000
   Dim i As Integer 
   Dim now1 As Long
   Do While i < len1
      now1 = System.currentTimeMillis()
      If (now1 > Time + 10) Then
          Time = now1
          DoEvents
          System.out.println("Inserting " & (100 * i / len1) & "%")
      End If
      prep.setInt(1, i)
      prep.execute()
      i = i + 1
   Loop
   Dim abnormalTermination As Boolean = True
   If abnormalTermination Then
      On Error Resume Next
      Cast(conn, org#h2#jdbc#JdbcConnection).setPowerOffCount(1)
      stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))")
   Else 
      conn.close()
   End If

   DoEvents
   System.out.println("Open connection...")
   Time = System.currentTimeMillis()
   conn = DriverManager.getConnection("jdbc:h2:" & "C:\test;DATABASE_EVENT_LISTENER='" & MyDatabaseListener & "'", "sa", "")
'   conn = DriverManager.getConnection("jdbc:h2:" & "C:\test;DATABASE_EVENT_LISTENER='" & getClass().getName() & "'", "sa", "")   
   Time = System.currentTimeMillis() - Time
   System.out.println("Done after " + Time + " ms")
   prep.close()
   stat.close()
   conn.close()
End Sub


Nur wie weise ich der Datenbank jetzt das Interface zu?


Dani

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

2

Saturday, January 11th 2014, 7:57pm

Hi!

Habe bisher nur ein Teil des Programms, das Du von
http://code.google.com/p/h2database/sour…ess.java?r=4647
hast portiert:

Module1:

Jabaco Source

1
2
3
4
5
6
7
8
9
Public Sub main(ByJava args() As String)
   Dim myArgs() As String
   myArgs = args
   ' [Your Source]
   
   Dim c As New Class1
   c.test
   
End Sub


Klassendatei Class1:

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
'
' Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License,
' Version 1.0, And under the Eclipse Public License, Version 1.0
' (http://h2database.com/html/license.html).
' Initial Developer: H2 Group

'Import java.sql.Connection;
'Import java.sql.DriverManager;
'Import java.sql.PreparedStatement;
'Import java.sql.SQLException;
'Import java.sql.Statement;
'
'Import org.h2.api.DatabaseEventListener;
'Import org.h2.jdbc.JdbcConnection;
'
'/**
' * This example application Implements a database Event listener.
' * This Is useful To display progress information While opening a large database,
' * Or To log database exceptions.
' */
Implements DatabaseEventListener
'Public class ShowProgress Implements DatabaseEventListener {
'
Private start As Long
Private last As Long

' Create a New instance of this class, And start the timer.

Public Sub ShowProgress()
  start = last = System.currentTimeMillis()
End Sub
'
'    /**
'     * This method Is called when executing this sample application from the
'     * command line.
'     *
'     * @param args the command line parameters
'     */
'    Public Static void main(String... args) throws Exception {
'        New ShowProgress().test();
'    }
'
'    /**
'     * Run the progress test.
'     */

Public Sub test
  Class.forName("org.h2.Driver")
  Dim conn As Connection = java#sql#DriverManager.getConnection("jdbc:h2:test", "sa", "")
  
  Dim stat As Statement = conn.createStatement
  stat.execute("DROP TABLE IF EXISTS TEST")
  stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)")
  Dim prep As PreparedStatement = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))")
  Dim myTime As Long
  myTime = System.currentTimeMillis
  Dim len As Integer = 1000
  Dim i As Integer
  For i = 0 To len-1
    Dim now As Long = System.currentTimeMillis
    If (now > myTime + 1000) Then
      myTime = now
      System.out.println("Inserting " & (100 * i / len) & "%")
    End If
    prep.setInt(1, i)
    prep.execute
  Next i
  Dim abnormalTermination As Boolean = True
  If abnormalTermination = True Then
    Dim jdbcConn As org#h2#jdbc#JdbcConnection
    jdbcConn = Cast (conn, org#h2#jdbc#JdbcConnection)
    jdbcConn.setPowerOffCount(1)
    stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))")
  Else
    conn.close
  End If
  System.out.println("Open connection...")
  myTime = System.currentTimeMillis()
  conn = DriverManager.getConnection("jdbc:h2:test;DATABASE_EVENT_LISTENER='" + getClass().getName() + "'", "sa", "")
  myTime = System.currentTimeMillis - myTime
'        System.out.println("Done after " + Time + " ms");
'        prep.close();
'        stat.close();
'        conn.close();
'
End Sub
'
'    /**
'     * This method Is called If an exception occurs In the database.
'     *
'     * @param e the exception
'     * @param sql the SQL statement
'     */
'    Public void exceptionThrown(SQLException e, String sql) {
'        System.out.println("Error executing " + sql);
'        e.printStackTrace();
'    }
'
'    /**
'     * This method Is called when opening the database To notify about the progress.
'     *
'     * @param state the current state
'     * @param name the Object name (depends On the state)
'     * @param current the current progress
'     * @param max the 100% mark
'     */
'    Public void setProgress(int state, String name, int current, int max) {
'        Long Time = System.currentTimeMillis();
'        If (Time < last + 5000) {
'            Return;
'        }
'        last = Time;
'        String stateName = "?";
'        switch (state) {
'        Case STATE_SCAN_FILE:
'            stateName = "Scan " + name;
'            break;
'        Case STATE_CREATE_INDEX:
'            stateName = "Create Index " + name;
'            break;
'        Case STATE_RECOVER:
'            stateName = "Recover";
'            break;
'        default:
'            Return;
'        }
'        try {
'            Thread.sleep(1);
'        } catch (InterruptedException e) {
'            // ignore
'        }
'        System.out.println("State: " + stateName + " " + (100 * current / max) + "% (" + current + " of " + max + ") "
'                + (Time - start) + " ms");
'    }
'
'    /**
'     * This method Is called when the database Is closed.
'     */
'    Public void closingDatabase() {
'        System.out.println("Closing the database");
'    }
'
'    /**
'     * This method Is called just after creating the instance.
'     *
'     * @param url the database URL
'     */
'    Public void init(String url) {
'        System.out.println("Initializing the event listener for database " + url);
'    }
'
'    /**
'     * This method Is called when the database Is open.
'     */
'    Public void opened() {
'        // Do Nothing
'    }
'
'}
'



'Public Sub main(ByJava args() As String)
'   Dim myArgs() As String
'   myArgs = args
'   ' [Your Source]
'
'End Sub
'
Public Sub init(arg2 As String)
   
End Sub

Public Sub opened()
   
End Sub

Public Sub exceptionThrown(arg2 As SQLException, arg3 As String)
   
End Sub

Public Sub setProgress(arg2 As Integer, arg3 As String, arg4 As Integer, arg5 As Integer)
   
End Sub

Public Sub closingDatabase()
   
End Sub



Bin gerade im Internmetcafé und gehe nun ins Kino. Daher momnentan keine Zeit für weiteres...

Grüße
theuserbl

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

3

Sunday, January 12th 2014, 3:53am

Ok, hier ist das Programm nun vollständig zu Jabaco portiert:

Module1:

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
' This method is called when executing this sample application from the
' command line.
'
' @param args the command line parameters

Public Sub main(ByJava args() As String)
   Dim myArgs() As String
   myArgs = args
   ' [Your Source]

   Dim c As New Class1
   c.test
End Sub


Und das KlassenModul Class1:

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
' Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License,
' Version 1.0, and under the Eclipse Public License, Version 1.0
' (http://h2database.com/html/license.html).
' Initial Developer: H2 Group
Import java#sql#Connection
Import java#sql#DriverManager
Import java#sql#PreparedStatement
Import java#sql#SQLException
Import java#sql#Statement

Import org#h2#api#DatabaseEventListener
Import org#h2#jdbc#JdbcConnection

' This example application implements a database event listener.
' This is useful to display progress information while opening a large database,
' or to log database exceptions.
Implements DatabaseEventListener

Private start As Long
Private last As Long

' Create a New instance of this class, And start the timer.
Public Sub Class1()
  start = last = System.currentTimeMillis()
End Sub

' Run the progress test.
Public Sub test
  java#lang#Class.forName("org.h2.Driver")
  Dim conn As Connection = java#sql#DriverManager.getConnection("jdbc:h2:test", "sa", "")
  
  Dim stat As java#sql#Statement = conn.createStatement
  stat.execute("DROP TABLE IF EXISTS TEST")
  stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)")
  Dim prep As PreparedStatement = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))")
  Dim myTime As Long
  myTime = System.currentTimeMillis
  Dim len As Integer = 1000
  Dim i As Integer
  For i = 0 To len-1
    Dim now As Long = System.currentTimeMillis
    If (now > myTime + 1000) Then
      myTime = now
      System.out.println("Inserting " & (100 * i / len) & "%")
    End If
    prep.setInt(1, i)
    prep.execute
  Next i
  Dim abnormalTermination As Boolean = True
  If abnormalTermination = True Then
    Dim jdbcConn As org#h2#jdbc#JdbcConnection
    jdbcConn = Cast (conn, org#h2#jdbc#JdbcConnection)
    jdbcConn.setPowerOffCount(1)
    On Error Goto z
      stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))")
    z:
  Else
    conn.close
  End If
  System.out.println("Open connection...")
  myTime = System.currentTimeMillis()
  conn = DriverManager.getConnection("jdbc:h2:test;DATABASE_EVENT_LISTENER='" + getClass().getName() + "'", "sa", "")
  myTime = System.currentTimeMillis - myTime
  System.out.println("Done after " + myTime + " ms")
  prep.close
  stat.close
  conn.close
End Sub

' This method is called if an exception occurs in the database.
'
' @param e the exception
' @param sql the SQL statement

Public Sub exceptionThrown(e As SQLException, sql As String)
  System.out.println("Error executing " & sql)
  e.printStackTrace
End Sub

' This method is called when opening the database to notify about the progress.
'
' @param state the current state
' @param name the object name (depends on the state)
' @param current the current progress
' @param max the 100% mark

Public Sub setProgress(state As Integer, name As String, current As Integer, max As Integer)
  Dim myTime As Long = System.currentTimeMillis()
  If (myTime < last + 5000) Then
    Exit Sub
  End If
  last = myTime
  Dim stateName As String = "?"
  Select Case state
    Case STATE_SCAN_FILE
      stateName = "Scan " & name
    Case STATE_CREATE_INDEX
      stateName = "Create Index " & name
    Case STATE_RECOVER
      stateName = "Recover"
    Case Else
      Exit Sub
  End Select

  Thread.sleep(1)
  System.out.println("State: " & stateName & " " & (100 * current / max) & "% (" + current + " of " + max + ") " & _
                (myTime - start) & " ms")
   
End Sub


' This method is called when the database is closed.

Public Sub closingDatabase()
  System.out.println("Closing the database")
End Sub


' This method is called just after creating the instance.
'
' @param url the database URL

Public Sub init(url As String)
   System.out.println("Initializing the event listener for database " & url)
End Sub


' This method is called when the database is open.

Public Sub opened()
  ' do nothing
End Sub


Grüße
theuserbl

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

4

Sunday, January 12th 2014, 9:30am

Hey theuserbl,

erstmal vielen Dank, daß Du Dir die Mühe gemacht hast; das Programm läuft so natürlich!

Wenn ich nun aber die Routine test() nach Form_Load() verschiebe (und entsprechend Modul1 verändere) bekomme ich nach Aufruf des Programms eine Rekursion und die Methoden der Klasse Class1 werden nicht aufgerufen.

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Public Sub Form_Load()

   Dim c As New Class1
'   c.test
  java#lang#Class.forName("org.h2.Driver")
  Dim conn As Connection = java#sql#DriverManager.getConnection("jdbc:h2:test", "sa", "")
  
  Dim stat As java#sql#Statement = conn.createStatement
  stat.execute("DROP TABLE IF EXISTS TEST")
  stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)")
  Dim prep As PreparedStatement = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))")
  Dim myTime As Long
  myTime = System.currentTimeMillis
  Dim len As Integer = 1000
  Dim i As Integer
  For i = 0 To len-1
    Dim now As Long = System.currentTimeMillis
    If (now > myTime + 1000) Then
      myTime = now
      System.out.println("Inserting " & (100 * i / len) & "%")
    End If
    prep.setInt(1, i)
    prep.execute
  Next i
  Dim abnormalTermination As Boolean = True
  If abnormalTermination = True Then
    Dim jdbcConn As org#h2#jdbc#JdbcConnection
    jdbcConn = Cast (conn, org#h2#jdbc#JdbcConnection)
    jdbcConn.setPowerOffCount(1)
    On Error Goto z
      stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))")
    z:
  Else
    conn.close
  End If
  System.out.println("Open connection...")
  myTime = System.currentTimeMillis()

'########## hier jetzt der Verweis auf Class1 -> c
  conn = DriverManager.getConnection("jdbc:h2:test;DATABASE_EVENT_LISTENER='" + c + "'", "sa", "")

  myTime = System.currentTimeMillis - myTime
  System.out.println("Done after " + myTime + " ms")
  prep.close
  stat.close
  conn.close
End Sub


Ich möchte ja daß, wenn ich die Connection conn in einem beliebigem Form oder Dialog Objekt verwende der DATABASE_EVENT_LISTENER quasi wie ein MouseListener reagiert.

Wie binde ich den DATABASE_EVENT_LISTENER hier also ein?

Dani

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

5

Sunday, January 12th 2014, 4:51pm

Das Problem ist wieder einmal die Form_Load()-Methode.
Mach es mit einem Button und es geht:

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
' Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License,
' Version 1.0, and under the Eclipse Public License, Version 1.0
' (http://h2database.com/html/license.html).
' Initial Developer: H2 Group
Import java#sql#Connection
Import java#sql#DriverManager
Import java#sql#PreparedStatement
Import java#sql#SQLException
Import java#sql#Statement

Import org#h2#api#DatabaseEventListener
Import org#h2#jdbc#JdbcConnection

' This example application implements a database event listener.
' This is useful to display progress information while opening a large database,
' or to log database exceptions.
Implements DatabaseEventListener

Private start As Long
Private last As Long

Public Sub Form_Load()
  start = last = System.currentTimeMillis()
End Sub

' This method is called if an exception occurs in the database.
'
' @param e the exception
' @param sql the SQL statement

Public Sub exceptionThrown(e As SQLException, sql As String)
  System.out.println("Error executing " & sql)
  e.printStackTrace
End Sub

' This method is called when opening the database to notify about the progress.
'
' @param state the current state
' @param name the object name (depends on the state)
' @param current the current progress
' @param max the 100% mark

Public Sub setProgress(state As Integer, name As String, current As Integer, max As Integer)
  Dim myTime As Long = System.currentTimeMillis()
  If (myTime < last + 5000) Then
    Exit Sub
  End If
  last = myTime
  Dim stateName As String = "?"
  Select Case state
    Case STATE_SCAN_FILE
      stateName = "Scan " & name
    Case STATE_CREATE_INDEX
      stateName = "Create Index " & name
    Case STATE_RECOVER
      stateName = "Recover"
    Case Else
      Exit Sub
  End Select

  Thread.sleep(1)
  System.out.println("State: " & stateName & " " & (100 * current / max) & "% (" + current + " of " + max + ") " & _
                (myTime - start) & " ms")
   
End Sub


' This method is called when the database is closed.

Public Sub closingDatabase()
  System.out.println("Closing the database")
End Sub


' This method is called just after creating the instance.
'
' @param url the database URL

Public Sub init(url As String)
   System.out.println("Initializing the event listener for database " & url)
End Sub


' This method is called when the database is open.

Public Sub opened()
  ' do nothing
End Sub


Public Sub Command1_Click()
  java#lang#Class.forName("org.h2.Driver")
  Dim conn As Connection = java#sql#DriverManager.getConnection("jdbc:h2:test", "sa", "")
  
  Dim stat As java#sql#Statement = conn.createStatement
  stat.execute("DROP TABLE IF EXISTS TEST")
  stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)")
  Dim prep As PreparedStatement = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))")
  Dim myTime As Long
  myTime = System.currentTimeMillis
  Dim len As Integer = 1000
  Dim i As Integer
  For i = 0 To len-1
    Dim now As Long = System.currentTimeMillis
    If (now > myTime + 1000) Then
      myTime = now
      System.out.println("Inserting " & (100 * i / len) & "%")
    End If
    prep.setInt(1, i)
    prep.execute
  Next i
  Dim abnormalTermination As Boolean = True
  If abnormalTermination = True Then
    Dim jdbcConn As org#h2#jdbc#JdbcConnection
    jdbcConn = Cast (conn, org#h2#jdbc#JdbcConnection)
    jdbcConn.setPowerOffCount(1)
    On Error Goto z
      stat.execute("INSERT INTO TEST VALUES(-1, 'Test' || SPACE(100))")
    z:
  Else
    conn.close
  End If
  System.out.println("Open connection...")
  myTime = System.currentTimeMillis()
  conn = DriverManager.getConnection("jdbc:h2:test;DATABASE_EVENT_LISTENER='" & getClass().getName() & "'", "sa", "")
  myTime = System.currentTimeMillis - myTime
  System.out.println("Done after " + myTime + " ms")
  prep.close
  stat.close
  conn.close
End Sub


Da müßte man Form_Load() irgendwie anders implementieren.

Grüße
theuserbl

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

6

Sunday, January 12th 2014, 8:51pm

Hey theuserbl,

Quoted

Das Problem ist wieder einmal die Form_Load()-Methode.
Mach es mit einem Button und es geht


ja, so geht es wenn ich den code "amStück" laufen lasse; wenn ich allerdings nur den DatabaseEventListener Teil über einen Button ausführe, dann wird eine neue Instanz von Form1 ausgeführt. Der DatabaseEventListener reagiert dann zwar, liefert aber keine Werte für current und max!

Ich hab hier mal ein TestCase, mit einer etwas längeren Beschäftigung für die Datenbank (je nach Cache und Prozessor!!)
Was ich eigentlich will:

Jabaco Source

1
2
3
4
5
6
7
Public Sub Command1_Click() 
   - DatabaseEventListener registrieren
   - langwieriges Sql Statement ausführen
   - Progressbar in setProgress() aktualisieren
   - DatabaseEventListener entfernen
   - Datenbank weiter verwenden
End Sub


Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
Option Explicit

Import java#sql#Connection
Import java#sql#DriverManager
Import java#sql#PreparedStatement
Import java#sql#SQLException
Import java#sql#Statement

Import org#h2#api#DatabaseEventListener
Import org#h2#jdbc#JdbcConnection

Implements DatabaseEventListener

Private start As Long
Private last As Long
Dim myTime As Long

Public Sub Form_Load()
'### Datenbank für DATABASE_EVENT_LISTENER - Test vorbereiten
   start = last = System.currentTimeMillis() 
   java#lang#Class.forName("org.h2.Driver")
   Dim conn As Connection = java#sql#DriverManager.getConnection("jdbc:h2:C:\test", "sa", "")
   Dim stat As java#sql#Statement = conn.createStatement
   stat.execute("DROP TABLE IF EXISTS TEST")
   stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)")
   Dim prep As PreparedStatement = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))")
   myTime = System.currentTimeMillis
   Dim len As Integer = 100000
   Dim i As Integer
   For i = 0 To len-1
      Dim now As Long = System.currentTimeMillis
      If (now > myTime + 1000) Then
         myTime = now
         System.out.println("Inserting " & (100 * i / len) & "%")
      End If
      prep.setInt(1, i)
      prep.execute
   Next i
   prep.close
   stat.close
   conn.close
End Sub

Public Sub Command1_Click()
   Dim conn1 As Connection 
   Dim stat1 As java#sql#Statement 
   Dim rs1 As ResultSet
   
   System.out.println("Open connection...")
   myTime = System.currentTimeMillis()  
   conn1 = DriverManager.getConnection("jdbc:h2:C:\test;DATABASE_EVENT_LISTENER='" & getClass().getName() & "'", "sa", "")
   stat1 = conn1.createStatement
   System.out.println("Starting UPDATE...")
   stat1.executeUpdate("UPDATE TEST SET ID = ID + 1;")
   myTime = System.currentTimeMillis - myTime
   System.out.println("Done after " + myTime + " ms")
End Sub

' This method is called if an exception occurs in the database.
'
' @param e the exception
' @param sql the SQL statement

Public Sub exceptionThrown(e As SQLException, sql As String)
  System.out.println("Error executing " & sql)
  e.printStackTrace
End Sub

' This method is called when opening the database to notify about the progress.
'
' @param state the current state
' @param name the object name (depends on the state)
' @param current the current progress
' @param max the 100% mark

Public Sub setProgress(state As Integer, name As String, current As Integer, max As Integer)
  myTime = System.currentTimeMillis()
  If (myTime < last + 5000) Then
    Exit Sub
  End If
  last = myTime
  Dim stateName As String = "?"
  Select Case state
    Case STATE_SCAN_FILE
      stateName = "Scan " & name
    Case STATE_CREATE_INDEX
      stateName = "Create Index " & name
    Case STATE_RECOVER
      stateName = "Recover"
    Case Else
      System.out.println("state: " & state & " name: " & name & " current: " & current & " max: " & max)
      Exit Sub
  End Select
  Thread.sleep(1)
  System.out.println("State: " & stateName & " " & (100 * current / max) & "% (" + current + " of " + max + ") " & _
                (myTime - start) & " ms")
End Sub

' This method is called when the database is closed.

Public Sub closingDatabase()
  System.out.println("Closing the database")
End Sub

' This method is called just after creating the instance.
'
' @param url the database URL

Public Sub init(url As String)
   System.out.println("Initializing the event listener for database " & url)
End Sub

' This method is called when the database is open.

Public Sub opened()
    System.out.println("Opening the database")
End Sub


...klapt leider noch nicht ganz!


Dani

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

7

Monday, January 13th 2014, 11:12am

Hey,

s.o.
ich habe jetzt die Methode setProgress() ein bischen erweitert und die Anzahl der Datensätze der Tabelle Test auf 1000000 erhöht.
Für current bekomme ich jetzt Werte und der DATABASE_EVENT_LISTENER reagiert wie erwartet. Leider bekomme ich keinen Wert für max.
Das müsste dann ja wohl an H2 liegen, oder?

Nach wie vor bewirkt

Jabaco Source

1
2
3
Command1_Click
bzw.
getClass().getName()


daß eine neue Instanz von Form1, also ein neues Fenster, initialisiert wird!

Jabaco Source

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Public Sub setProgress(state As Integer, name As String, current As Integer, max As Integer)
  Dim myTime As Long = System.currentTimeMillis()
  If (myTime < last + 5000) Then
    Exit Sub
  End If
  last = myTime
  Dim stateName As String = "?"
  
  Select Case state
    '* This state Is used when scanning the database file.
    Case STATE_SCAN_FILE
      stateName = "Scan " & name
      
    '* This state Is used when re-creating an index.
    Case STATE_CREATE_INDEX
      stateName = "Create Index " & name
      
     '* This state Is used when re-applying the transaction log Or rolling back
     '* uncommitted transactions.
    Case STATE_RECOVER
      stateName = "Recover"
      
     '* This state Is used during the BACKUP command.
    Case STATE_BACKUP_FILE
      stateName = "Backup"
      
     '* This state Is used after re-connecting To a database (If auto-reconnect
     '* Is enabled).
    Case STATE_RECONNECTED
      stateName = "Reconnected"
      
     '* This state Is used when a query starts.
    Case STATE_STATEMENT_START
      stateName = "Statement Start"
      
     '* This state Is used when a query ends.
    Case STATE_STATEMENT_END
      stateName = "Statement End"
      
     '* This state Is used For periodic notification during Long-running queries.
    Case STATE_STATEMENT_PROGRESS
      stateName = "Statement Progress"
      
    Case Else
      System.out.println("state: " & state & " name: " & name & " current: " & current & " max: " & max)
      Exit Sub
      
  End Select
  Thread.sleep(1)
  System.out.println("State: " & stateName & " " & (100 * current / max) & "% (" + current + " of " + max + ") " & _
                (myTime - start) & " ms")
End Sub


Dani

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

8

Monday, January 13th 2014, 12:15pm

zu meinem vorigen Post:

ich habe im h2 source Folgendes gefunden, was dann auch max=0 erklärt...

http://h2database.googlecode.com/svn/tru…d/Prepared.java

Source code

1
2
3
4
5
6
7
8
    /**
     * Notifies query progress via the DatabaseEventListener
     */
    private void setProgress() {
        if ((currentRowNumber & 127) == 0) {
            session.getDatabase().setProgress(DatabaseEventListener.STATE_STATEMENT_PROGRESS, sqlStatement, currentRowNumber, 0);
        }
    }


Hm, wie ich jetzt allerdings bei einem MERGE Statement den Fortschritt berechnen soll ist mir ein Rätsel?
Bleibt wohl nur die Sanduhr.

Trotzdem interessiert mich noch die Sache mit der Instanzierung von Form1, s.o.


Dani

Dani

Intermediate

  • "Dani" started this thread

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

9

Tuesday, January 21st 2014, 4:46pm

Hey,

falls es Jemanden interessiert ...
im H2 Forum hab ich auf meine Frage bzgl. der oben geschilderten Problematik folgende Antwort bekommen:

Quoted

...however it only triggers events at statement start and statement end, so there is no way of knowing where inside a statement it is.
And in general there is no good way of implementing such a thing either, given the complexity of SQL...


Die Antwort ist nicht ganz zutreffend, da ich immerhin in setProgress() die Anzahl der bereits verarbeiteten Datensätze bekomme!
Ich müsste also vor der eigentlichen Abfrage die Anzahl der Datensätze abrufen; vorausgesetzt MERGE vergleicht mit allen Datensätzen der Tabelle!

Nach wie vor bewirkt

Jabaco Source

1
2
3
4
5
Command1_Click
bzw.
getClass().getName()

s.o.


daß eine neue Instanz von Form1, also ein neues Fenster, initialisiert wird!

hat hierzu noch jemand einen Gedanken?!


Dani

Rate this thread
WoltLab Burning Board