Hi,
(1) add a manual command to clean them up
>>
>
Yes, that would be an option, and we would not have to change behavior by
default (which is a risk as it could break existing applications).
> (2) change it so that the client can only read from a BLOB while a
>> transaction is open. Flush any temporary BLOBs on commit. This is how other
>> databases work, but it would be a change for us. So maybe we would have to
>> hide this option behind a flag for now.
>>
>
I didn't know that. I made some tests (see below), and at least Apache
Derby seems to do that. I'm not sure about other databases however;
PostgreSQL doesn't seem to support CLOB(?) and HSQLDB didn't remove the
CLOB on commit. That should be possible to implement; we already have an
array for LOB entries to remove on commit, in Session.unlinkLobMap. I think
adding the LOBs there should work. Yes, we need to be a bit careful with
compatibility (with other database and with previous versions of H2); I
guess we could change the default behavior in version 1.4.x as it is marked
beta.
Test case results:
jdbc:h2:mem:
while reading: aaa
after next: aaa
after result set close: aaa
after delete: aaa
jdbc:hsqldb:/tmp/db/test
while reading: aaa
after next: aaa
after result set close: aaa
after delete: aaa
jdbc:derby:/tmp/db/test2;create=true
while reading: aaa
after next: aaa
after result set close: java.sql.SQLException: You cannot invoke other
java.sql.Clob/java.sql.Blob methods after calling the free() method or
after the Blob/Clob's transaction has been committed or rolled back.
after delete: java.sql.SQLException: You cannot invoke other
java.sql.Clob/java.sql.Blob methods after calling the free() method or
after the Blob/Clob's transaction has been committed or rolled back.
Test case:
package db;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
public class TestMultiDb {
public static void main(String[] a) throws Exception {
DeleteDbFiles.execute("~/temp", "test", true);
// Server s = Server.createTcpServer().start();
// try {
// test("org.h2.Driver", "jdbc:h2:tcp://localhost/~/temp/test",
"sa", "sa");
// } finally {
// s.stop();
// }
test("org.h2.Driver", "jdbc:h2:mem:", "sa", "sa");
// test("org.h2.Driver", "jdbc:h2:~/temp/test", "sa", "sa");
test("org.hsqldb.jdbcDriver", "jdbc:hsqldb:/tmp/db/test", "sa",
"sa");
test("org.apache.derby.jdbc.EmbeddedDriver",
"jdbc:derby:/tmp/db/test2;create=true", "sa", "sa");
// test("com.mysql.jdbc.Driver",
"jdbc:mysql://localhost:3306/test", "sa", "sa");
// test("oracle.jdbc.driver.OracleDriver",
// "jdbc:oracle:thin:@192.168.0.100:1521:XE", "sa", "sa");
// test("org.postgresql.Driver", "jdbc:postgresql:test", "sa",
"sa");
}
static void test(String driver, String url, String user, String
password) throws Exception {
System.out.println(url);
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
Statement stat =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
try {
stat.execute("drop table test");
} catch (SQLException e) {
// ignore
}
try {
stat.execute("create table test(id int primary key, data
clob)");
} catch (SQLException e) {
stat.execute("create table test(id int primary key, data
varchar(255))");
}
PreparedStatement prep = conn.prepareStatement("insert into test
values(?, ?)");
prep.setInt(1, 1);
prep.setString(2, "aaa" + new String(new char[1024 *
16]).replace((char) 0, 'x'));
prep.execute();
prep.setInt(1, 2);
prep.setString(2, "bbb" + new String(new char[1024 *
16]).replace((char) 0, 'x'));
prep.execute();
ResultSet rs = stat.executeQuery("select * from test order by id");
rs.next();
Clob c1 = rs.getClob(2);
System.out.println("while reading: " + c1.getSubString(1, 3));
rs.next();
System.out.println("after next: " + c1.getSubString(1, 3));
rs.close();
try {
System.out.println("after result set close: " +
c1.getSubString(1, 3));
} catch (SQLException e) {
System.out.println("after result set close: " + e);
}
stat.execute("delete from test");
try {
System.out.println("after delete: " + c1.getSubString(1, 3));
} catch (SQLException e) {
System.out.println("after delete: " + e);
}
conn.close();
System.out.println();
}
}
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
