is there a tutorial anywhere on how to use prepared statements with H2 ?

Hi, I've now been using H2 for almost a year, extremely happy about its ease of use and performances. I'm currently trying to import lot of data into several tables, using always similar insert commands. I read somewhere that prepared statements could help speeding up partially (I've already

Re: is there a tutorial anywhere on how to use prepared statements with H2 ?

, 2012 10:39:32 PM UTC+2, choces wrote: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html 2012/3/30 Noel Grandin noelgran...@gmail.com google for jdbc preparedstatement On Fri, Mar 30, 2012 at 20:13, Christian MICHON christian.mic...@gmail.com wrote: Hi, I've now been

Re: is there a tutorial anywhere on how to use prepared statements with H2 ?

') stmt.executeUpdate INSERT INTO TEST VALUES(3, 'World') prep = conn.prepareStatement DELETE TEST WHERE ID = ? prep.setInt(1,2) prep.executeUpdate() conn.close() Thanks again for the google hints and this great db! -- Christian On Friday, March 30, 2012 11:07:53 PM UTC+2, Christian MICHON wrote: Thanks guys

How to calculate md5sum in a computed column?

Hi, I need to detect repetitions and updates in (relatively) small H2 tables. As such, I currently implemented using ruby and an external ORM a md5sum calculation of all the keys concatenated into a string following their natural order, which is stored back into a specific key. This involves

Re: How to calculate md5sum in a computed column?

On Friday, April 13, 2012 9:51:52 PM UTC+2, Christian MICHON wrote: Hi, I need to detect repetitions and updates in (relatively) small H2 tables. As such, I currently implemented using ruby and an external ORM a md5sum calculation of all the keys concatenated into a string following

Re: How to calculate md5sum in a computed column?

On Thursday, April 19, 2012 6:04:36 PM UTC+2, Thomas Mueller wrote: Hi, H2 supports SHA256 natively, I would use it instead of MD5: http://h2database.com/html/functions.html#hash Regards, Thomas On Saturday, April 14, 2012, Christian MICHON wrote: On Friday, April 13, 2012 9:51:52

Re: How to calculate md5sum in a computed column?

, Christian On Thursday, April 19, 2012 6:37:01 PM UTC+2, Christian MICHON wrote: Yes, I knew about it. Yet, this gives me 2 problems: - increase of 32 char to 64 char for storing the hash. Maybe I should store this in a different way, like binary to save half the space? - I cannot figure out how

Re: How to calculate md5sum in a computed column?

Christian On Friday, April 20, 2012 12:54:39 PM UTC+2, Steve McLeod wrote: Hi Christian, Is disk space/database size really a concern for your application? If not, just storing in a varchar(64) makes your code simple. On Thursday, 19 April 2012 18:50:29 UTC+2, Christian MICHON wrote

Re: [h2] How to use a multi-character fieldSep with csvread?

Thanks for your answer Noel. I see: now that I just reverse-compiled org.h2.tools.Csv#readValue and how fieldSeparatorRead is used, I understand why it cannot be done using the standard parser. Ok, I might generate a middleware to pre-transform the odd looking CSV files and keep using the

Re: [h2] How to use a multi-character fieldSep with csvread?

Well 1.3.176 is not listed at https://code.google.com/p/h2database/downloads/list. I can see the sources are indeed in http://www.h2database.com/h2-2014-04-05.zip: my bad... On Mon, Nov 17, 2014 at 3:20 PM, Noel Grandin noelgran...@gmail.com wrote: I see: now that I just reverse-compiled

[h2] Import an embedded H2 database to an in-memory H2 database.

Allocate more memory to the jvm: your data is stored in the same heap space used by sql operations. Go for 1.5Gb if you're in 32 bits jvm or above 2Gb if in 64 bits jvm. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this

[h2] Re: ram usage in h2

Are you using some commands like create table mytable as select * from csvread('myfile.csv') ? If so, I've few possible pieces of advice: - create a schema first, with the right types expected for attributes. otherwise each attribute will be casted as text with 2Gb max allocation. - insert like

[h2] H2 Database Engine: New version 1.4.188 available

Hi The changelog is missing. I did not find it on github either. Thanks for this new release. Regards Christian -- 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

Re: [h2] H2 InMemory DB experiences with Large Databases

What would be the JDBC url for such nio in-memory node? Could not find a way to make it work... On Monday, October 19, 2015 at 8:40:58 PM UTC+2, Noel Grandin wrote: > > You might want to try the "nio" in-memory mode, it's likely to be more > space efficient and easier on the GC, since it stores

Re: [h2] H2 InMemory DB experiences with Large Databases

Cool. New jdbc URLs to play with... Thanks, I've a use case just for this evaluation ;-) On Tuesday, October 20, 2015 at 8:49:20 AM UTC+2, Noel Grandin wrote: > > Ah, the one that I was talking about is > > jdbc:h2:nioMemFS:test > and > jdbc:h2:nioMemLZF:test > > I just haven't gotten around

Re: [h2] H2 error [90011-187] - Need a way to use relative dir

On Wed, Aug 26, 2015 at 1:44 PM, Christian MICHON christian.mic...@gmail.com wrote: I'll second that, but maybe a setting could be added (unless it already exists) to switch this on or off. Like this we could use this switch to ease legacy issues while preserving the directions chosen

Re: [h2] H2 error [90011-187] - Need a way to use relative dir

I'll second that, but maybe a setting could be added (unless it already exists) to switch this on or off. Like this we could use this switch to ease legacy issues while preserving the directions chosen by the lead devs of H2. As for moving to 1.4.188, some times you have no choice but to move

Re: [h2] What version to pick for production

I would tend to disagree that the mvstore performance is almost at the level of pagestore: please find below some ETL jobs benchmarks on ITIS taxonomy database. FWIW, we use 1.3.176 on production servers, especially in server mode. We even had one application running 24/7 for more than a year

[h2] Re: connecting to h2 from non-java languages

follwoing: > - path to DB:port > -user name, password of h2 database > - embedded or TCP (if embedded, the http server would run H2, else it > will simply connect to an existing H2 TCP server over JDBC > - ip whitelist - list of ips that can access the http server > -http port >

[h2] Re: connecting to h2 from non-java languages

Jruby was used to write an proxy data API server using Sequel and Roda. Since it's ruby inside java, connections to H2 are fully supported via Sequel. For the container, I used Puma. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To

[h2] Re: connecting to h2 from non-java languages

on point 2: it can be easily done with spark (sparkjava.com not apache spark) + jooq You'll need to ensure security, using json web tokens for example. On Thursday, February 23, 2017 at 3:47:33 AM UTC+1, Adam McMahon wrote: > > Hi Group, > > i am considering a project that may require me to

Re: [h2] Re: Very slow calculateCost

I beg to differ: I ran the DDL into DBVisualizer (references mode, circular view) and from the look of it this is a real database with obfuscated names. Redesigning the database is not an option if it's not from the original author: the query itself has to be redesigned and this leads to the

Re: [h2] Re: Very slow calculateCost

it does, especially when the database is > empty ! (I am not expecting the same performance from H2 that I get on > Oracle but like to get something acceptable). > > > 2016-12-08 13:30 GMT+01:00 Christian MICHON <christia...@gmail.com > >: > >> I beg to dif

Re: [h2] Very slow calculateCost

Can you please share the database with H2 community? It's hard to tell what is happening without a concrete dataset or at least the complete DDL. Please do so if the above suggested solution does not work for you. -- You received this message because you are subscribed to the Google Groups

[h2] Re: CSVREAD not requiring admin rights

this > function as a non-admin user. The documentation says that this function as > well as CSVREAD require admin capabilities to execute. > > But when I try to execute CSVREAD as a non-admin H2 user, the function > succeeds when per the documentation it should fail. > > >

[h2] Re: CSVREAD not requiring admin rights

Who created the folder c:\temp ? If it was not created by your standard user account, you may have no rights to write into it. Please check the r/w rights access to it, even a simple file copy into it will give you a clue. For me this works as I created the folder c:\temp myself with a

[h2] Migration from an Oracle Database to a H2 Database

Hi >From a pure java technology, you would need to first recreate the complete DDL >from the source DB to the destination DB. Then use for example an ETL to load >all records. Another possible route is to use linked tables. But you'll miss out on indexes and referential constraints unless you

[h2] Re: Problem migrating from 1.3.176 to 1.4.195

Hi, could you share please the jdbc url used? Did you use a relative or absolute path when updating to 1.4.x? On Friday, August 18, 2017 at 4:01:36 PM UTC+2, DecebalICT wrote: > > I only saw today that there was a new stable version. So of-course I > updated from 1.3.176 to 1.4.195. But when

[h2] Re: What's the recommended way to migrate from H2 1.3.175 to 1.4.195

mechanism. I cannot confirm, I'm not part of the h2 dev team. On Tuesday, May 9, 2017 at 10:42:50 AM UTC+2, hendrik wrote: > > > > On Monday, May 8, 2017 at 2:02:04 PM UTC+2, Christian MICHON wrote: >> >> Yes you can use the 1.4 jar to export the script. I've done this alread

[h2] Re: Can't migrate h2 db, db unresponsive

1 doesn't improve anything. > Now I'm copying the rows based on their id, using "where id > ... and id < > ...", which works. > > I suppose h2 isn't made for large data sets. > > Am Montag, 8. Mai 2017 16:50:03 UTC+2 schrieb Christian MICHON: >>

[h2] Re: What's the recommended way to migrate from H2 1.3.175 to 1.4.195

Hi Yes you can use the 1.4 jar to export the script. I've done this already. I would advice to use the default settings per version. 1.3 uses page store, 1.4 the newer store. Do not tweak the defaults settings except the cache size. Try to keep also regular backups using SCRIPT TO. Good

[h2] Can't migrate h2 db, db unresponsive

Did you have a look at Scriptella? I suggest to pay attention to fetch size and batch size as jdbc parameters. You might want also to export to another h2 first to see if any potential issues with index sizes or data corruption. -- You received this message because you are subscribed to the

[h2] What's the recommended way to migrate from H2 1.3.175 to 1.4.195

Hi, As simple as possible. Use SCRIPT to export SQL. Use RUNSCRIPT into a freshly created db with 1.4.195 Christian -- 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

[h2] How to use IS NULL in H2 database

C='null' or C=null? The single quotes might have strong effect here... -- 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.

Re: [h2] appears to have started a thread named [H2 File Lock Watchdog (Socket) HELP

Use a context listener for this closing H2 without stopping tomcat. Have a look at stackoverflow.com on how to do this with H2 -- 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,

[h2] Re: Database size explodes when exporting to SQL

Hi, how many indexes do you have inside your database? On Thursday, June 14, 2018 at 1:24:34 PM UTC+2, Silvio wrote: > > I tried connecting to the database from the H2 query tool and executing > "script". Same thing happens there. > -- You received this message because you are subscribed to

[h2] How can I set sysdate in H2? (for testing)

Please share the alias you wrote and how you invoked it. Christian -- 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

Re: [h2] Performance of large updates

Have you tried putting the update in a dedicated transaction? This one might help quickly. Is the updated column indexed too? Is the primary key a standard integer? This last point is tricky but worth investigating. I had a db with a unique indexed string. Performing any update on it would

Re: [h2] Performance of large updates

I meant this by "dedicated transaction": set autocommit off; UPDATE test SET c = 200 WHERE e; commit; On your small testcase, it does not make much difference. I would be curious what you'd get. Rgds Christian On Tuesday, July 24, 2018 at 3:35:36 PM UTC+2, Philipp Cornelius wrote: > > Thanks

[h2] Re: Redhat warning about production use of H2

How many times were you hit by a database corruption and couldn't recover any data at all? How many releases of H2 were tagged as stable and not as alpha/beta? Not matter how much I personally love using H2 for personal or professional projects, these 2 questions above always pop up in the mind

[h2] Re: H2 DB size grows always with version 1.4.197

The db you sent as sample for review is corrupted and recovery tool cannot salvage the situation. Christian On Tuesday, October 9, 2018 at 1:31:11 PM UTC+2, Surendhar Studies wrote: > > Hi Thomas, > > I have chosen h2 for our project and we are using latest version 1.4.197, > my DB size

Re: [h2] Re: H2 Database Engine: New version released

at least using scriptella ETL, it is still possible to use a memory database. But it's less convenient indeed to fire up a memory database and demo a quick SQL script... On Saturday, February 23, 2019 at 4:11:33 PM UTC+1, Noel Grandin wrote: > > > > On Sat, 23 Feb 2019 at 16:52, Evgenij

[h2] Re: H2 Database Engine: New version released

ok thanks for this valuable information. I will try with some scriptella jobs to see if my ETL scripts require to keep old H2 engines. On Saturday, February 23, 2019 at 11:57:32 AM UTC+1, Evgenij Ryazanov wrote: > > Standalone H2 Console does not allow creation of new databases from a web >

[h2] Re: H2 Database Engine: New version released

Hi, is it normal one cannot connect to memory databases anymore using H2 console web interface? jdbc:h2:mem: yields: Database "mem:" not found [90013-198] On Friday, February 22, 2019 at 4:00:13 PM UTC+1, Thomas

Re: [h2] Re: H2 Database Engine: New version released

I also tried with a given name On Saturday, February 23, 2019 at 11:22:53 AM UTC+1, Noel Grandin wrote: > > you need to give it a name > > jdbc:h2:mem:test1 > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and

[h2] Re: Using H2 with application server for tests

any chance that using savepoint/rollback could solve your issue? On Thursday, June 20, 2019 at 11:32:38 AM UTC+2, Niklas Mehner wrote: > > Hi, > > We are using H2 for end-to-end tests for our JEE application. > > So basically the setup is: > > * start H2 (in memory) and application server > * run

[h2] Re: Using H2 with application server for tests

Argh... only application until a transaction is committed or rolledback. No go, forget my suggestion On Thursday, June 20, 2019 at 4:45:16 PM UTC+2, Christian MICHON wrote: > > any chance that using savepoint/rollback could solve your issue? > > On Thursday, June 20, 2019 at 11:32

Re: [h2] Re: Recent drops in performance

Yes in memory only for now. I can move it to embedded files and tcp as well. What I saw is a sudden drop of performance between 196 and 197. That drop was halved and partially recovered in 198 and 199. But it's still, for that particular use case (CTE), too slow compared to older versions.

Re: [h2] Re: Recent drops in performance

jdbc:h2:mem:tsn it's a memory db. Keep in mind that the standalone test case is a load test. You'll need JMeter to run it and other versions of H2 to compare via individual runs. I'll get this ready soon On Monday, April 29, 2019 at 3:28:22 AM UTC+2, andre...@gmail.com wrote: > > I wonder how

Re: [h2] Re: Recent drops in performance

I'll do that and keep you posted -- 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

Re: [h2] Re: Recent drops in performance

This script is to initialize the db and test only once the CTE query. If you just do this with one user and no volume, the difference of performance is difficult to notice. You might notice something if your system is under heavy cpu or io load. The measurement of CTE is done by the jmx file

Re: [h2] Re: Recent drops in performance

You would need to build a thread engine sending these queries through jdbc. Not impossible. I used JMeter as it's a java product already available I use on daily basis. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this

Re: [h2] Re: Recent drops in performance

Correct -- 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

Re: [h2] Re: Recent drops in performance

No. It's the init.sql that populates the db. JMeter only opens the pre-existing populated db, and then indeed run CTE for all IDs in population.csv. I wish it could have been in a shape of an automated testscase, but it's not. This one highlights the performance issue only. I will try to do

Re: [h2] Re: Recent drops in performance

Are you doing 1 times a CTE or just a simple SELECT? 200 users concurrency is needed else indeed some versions will feel similar. I also built a ramp up of 4 seconds. It's a steep ramp up but allows to measure how h2 recovers from sudden increase of traffic. I would like to emphasise that

Re: [h2] Re: Recent drops in performance

have worse timings, especially 1.4.197. Here's the graph: I keep the TCP analysis aside for now. Rgds Christian On Wednesday, May 1, 2019 at 9:19:20 AM UTC+2, Christian MICHON wrote: > > Yes in memory only for now. > > I can move it to embedded files and tcp as well. > >

[h2] Re: Recent drops in performance

On Friday, April 26, 2019 at 4:28:00 PM UTC+2, Evgenij Ryazanov wrote: > > Hello. > > CTEs are experimental in H2 and have known bugs, recent versions contain > some fixes, so this performance drop may be somehow related with them. But > older versions of H2 do not support CTEs at all, so I

Re: [h2] Re: Recent drops in performance

On Friday, April 26, 2019 at 4:30:49 PM UTC+2, Noel Grandin wrote: > > > > On 2019/04/26 4:15 PM, Christian MICHON wrote: > > @Evgenij : it's not rocket science to build such scenario, if you need > such performance test scenario, I'll share it on > > gi

[h2] Re: 2021 State of H2 development

Have a look at h2 homepage and you'll see the 2.0.202 is out since November 25th! Great job and many thanks to the contributors! On Saturday, November 27, 2021 at 11:35:16 AM UTC+1 mps wrote: > Under the hood: > > https://github.com/h2database/h2database/pull/3210 > > :) >