Ok Sam, I have got the message: I just switch to BLOB's. Thanks for your help!
Regards, Ronald On Jan 20, 5:16 pm, Sam Van Oort <[email protected]> wrote: > Hi again rmuller, > > The BINARY data type should not be used for large objects (more than a > few kB) unless you have a lot of memory, and the documentation makes > this clear. H2 doesn't put arbitrary limits on BINARY or VARCHAR > size like MySQL or many other engines, but this is for convenience and > will cause problems if abused. > > You need to switch to BLOB if you want to do multi-row updates on MB- > sized objects. OR, you can split big transactions into very small > ones to fit in memory. To migrate, you'll want to add the BLOB column > and then migrate BINARY data to blob in one-row transactions to avoid > memory issues. This can easily be done using JDBC, or by running a > number of one-row (or few-row) UPDATE statements with WHERE conditions > that use the primary key to limit transaction size. > > Regards, > Sam > > On Jan 20, 2:43 am, rmuller <[email protected]> wrote: > > > > > Hi Sam, > > > Thanks for the quick reply! > > > Sorry, i am not clear enough (now I read the title again): the point > > is that processing 100 files of 1MB in one transaction with 128MB > > memory is not possible with VARBINARY's even if i set > > "MAX_MEMORY_UNDO" to a (very) low value. > > > Regards, > > > Ronald > > > On Jan 19, 10:52 pm, Sam Van Oort <[email protected]> wrote: > > > > Hi, > > > > This behavior is normal and expected -- it's one of the main > > > differences between BINARY and BLOB types. > > > See the > > > documentation:http://www.h2database.com/html/datatypes.html#binary_type > > > > Cheers, > > > Sam Van Oort > > > On Jan 19, 10:56 am, rmuller <[email protected]> wrote: > > > > > Hi, > > > > > If i store binary data (actual photo's) into H2 in *one* transaction i > > > > run into memory problems when using VARBINARY. If i use BLOB's > > > > everything goes well. See code sample. Is this a H2 issue or do I miss > > > > a configuration option? > > > > > final Properties props = new Properties(); > > > > props.setProperty("USER", "admin"); > > > > props.setProperty("PASSWORD", "admin"); > > > > props.setProperty("MAX_MEMORY_UNDO", "100"); > > > > > Class.forName("org.h2.Driver"); > > > > final Connection c = DriverManager.getConnection("jdbc:h2:./ > > > > test", props); > > > > c.setAutoCommit(false); > > > > > final Statement stm = c.createStatement(); > > > > //stm.executeUpdate("create table TEST (id IDENTITY, data > > > > BLOB)"); // OK > > > > stm.executeUpdate("create table TEST (id IDENTITY, data > > > > VARBINARY)"); // OOM > > > > stm.close(); > > > > > final PreparedStatement pstm = c.prepareStatement( > > > > "INSERT INTO test (data) VALUES (?)"); > > > > > // H2 keeps the undo log in memory by default, MAX_MEMORY_UNDO > > > > must be set > > > > // to a low value if memory is limited > > > > // When using VARBINARY and -Xmx128M OoM always occurs > > > > int count = 0; > > > > while (++count < 100) { > > > > pstm.setBytes(1, new byte[1024 * 1024]); > > > > pstm.executeUpdate(); > > > > } > > > > > c.commit(); > > > > c.close(); > > > > > Regards, > > > > > Ronald
-- 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.
