Hi,
in my application using H2 (1.4.196) i need to insert very large CLOB
values.
Unfortunately H2 fails here with the following error:
org.h2.jdbc.JdbcSQLException: Out of memory. [90108-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:289)
at org.h2.server.TcpServerThread.sendError(TcpServerThread.java:220)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:160)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2367)
at java.lang.String.<init>(String.java:168)
at org.h2.value.Transfer.readValue(Transfer.java:673)
at org.h2.server.TcpServerThread.setParameters(TcpServerThread.java:251)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:350)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
... 1 more
at org.h2.engine.SessionRemote.done(SessionRemote.java:629)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:209)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:164)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:150)
at ClobInsert.insertClob(ClobInsert.java:82)
at ClobInsert.main(ClobInsert.java:36)
The H2Server was started like this:
java.exe -Xmx512M -cp ".\h2\bin\h2-1.4.196.jar" org.h2.tools.Server
-tcpPort 9092 -tcp
The connect String is:
jdbc:h2:tcp://localhost:9092/C:\temp\testdb
My sample program for reproduction (attached at the end) is capable of
inserting a 50MB CLOB, for 100MB it fails.
Increasing XMX only has a little impact, and especially for 32bit I cannot
increase XMX to much more than 1200M, so that I am unable to insert a 200MB
CLOB on 32bit java running the H2 Server.
Is there any setting to work around this problem or is this simply a bug?
Sample Program for reproduction:
public class ClobInsert {
/**
* Main
* @param args Unused
*/
public static void main(String[] args) {
String url = "jdbc:h2:tcp://localhost:9092/C:\\temp\\testdb";
try (Connection connection = DriverManager.getConnection(url, "user"
, "psw")) {
createTable(connection);
try {
insertClob(connection, 50);
insertClob(connection, 100);
insertClob(connection, 200);
insertClob(connection, 300);
insertClob(connection, 400);
insertClob(connection, 500);
insertClob(connection, 600);
insertClob(connection, 700);
} finally {
dropTable(connection);
}
} catch (SQLException | IOException ex) {
ex.printStackTrace();
}
}
/**
* Inserts a single CLOB of the given size
* @param connection H2 Connection
* @param mb The amount of MB to insert as CLOB
* @throws IOException If file writing failed
* @throws SQLException If database error occurred
*/
private static void insertClob(Connection connection, int mb) throws
IOException, SQLException {
System.out.print("Attempting to insert " + mb + " MB of data...");
File temp = File.createTempFile("tmp", ".clob");
temp.deleteOnExit();
writeBytes(temp, mb);
try {
Reader reader = new InputStreamReader(new FileInputStream(temp
));
try (PreparedStatement st = connection.prepareStatement(
"insert into CLOBTAB (ID, value) values (?, ?)")) {
st.setInt(1, mb);
try {
st.setCharacterStream(2, reader);
} catch (AbstractMethodError err) {
/*
* As of JDBC 1.0, only setCharacterStream(int index,
Reader
* reader, int length) is specified. The method without
an
* integer length is specified by JDBC 4.0, and might
not be
* implemented by the driver.
*/
st.setCharacterStream(2,
reader,
(int)temp.length());
}
st.executeUpdate();
}
connection.commit();
System.out.println(" done.");
} catch (SQLException ex) {
System.out.println(" failed.");
throw ex;
}
}
/**
* Writes sample bytes into the given file
* @param temp Afile
* @param mb The abount of MB to write
* @throws IOException If file writing failed
*/
private static void writeBytes(File temp, int mb) throws IOException {
char[] buffer = new char[1_000_000];
for (int j = 0; j < 1_000_000; j++) {
buffer[j] = ((char)(35 + (j % 90)));
}
try (FileWriter writer = new FileWriter(temp)) {
for (int i = 0; i < mb; i++) {
writer.write(buffer);
}
writer.flush();
}
}
/**
* Creates tes ttable
* @param connection H2 Connection
* @throws SQLException If table cration failed
*/
private static void createTable(Connection connection) throws
SQLException {
try (PreparedStatement st = connection.prepareStatement(
"create table if not exists CLOBTAB (ID integer, value
CLOB)")) {
st.executeUpdate();
}
}
/**
* Drops test table
* @param connection H2 Connection
* @throws SQLException If tablecould not be dropped
*/
private static void dropTable(Connection connection) throws SQLException
{
try (PreparedStatement st = connection.prepareStatement(
"drop table if exists CLOBTAB")) {
st.executeUpdate();
}
}
}
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
