var/char memory usage

Hi, I am new to this list. H2 seems like a great program and I am highly considering using it in my current web app. In the documentation on var/chars and I saw that The whole text is kept in memory when using this data type. I was confused as to what this statement meant? a) Does it mean

Re: var/char memory usage

Thanks for the quick answer! H2 is great! -Adam On Oct 25, 10:18 am, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, In the documentation on var/chars and I saw that The whole text is kept in memory when using this data type.  I was confused as to what this statement meant? I

closing embedded database with DB_CLOSE_DELAY=-1

Hi everyone, I am using an embedded Database with DB_CLOSE_DELAY=-1. I could not find a simple way to close this type of database from within Java code. I would like H2 to be able to close a bit more graceful, currently I am getting the tomcat warning: appears to have started a thread named [H2

Re: closing embedded database with DB_CLOSE_DELAY=-1

On Wednesday, October 26, 2011, Adam McMahon wrote: Hi everyone, I am using an embedded Database with DB_CLOSE_DELAY=-1.  I could not find a simple way to close this type of database from within Java code. I would like H2 to be able to close a bit more graceful, currently I am getting

order of SQL operations

Hi, Quick question. As I understand it, H2 handles the synchronization of SQL commands. If a number of SQL commands hit the database from a variety of connections, do they execute in the order that H2 receives them? If not, can a brief explanation be provided? Thanks, -Adam -- You received

Re: order of SQL operations

://www.h2database.com/html/features.html#multiple_connections Adam McMahon wrote: Hi, Quick question.  As I understand it, H2 handles the synchronization of SQL commands.  If a number of SQL commands hit the database from a variety of connections, do they execute in the order that H2 receives

Re: order of SQL operations

Grandin noelgran...@gmail.com wrote: seehttp://www.h2database.com/html/features.html#multiple_connections Adam McMahon wrote: Hi, Quick question.  As I understand it, H2 handles the synchronization of SQL commands.  If a number of SQL commands hit the database from a variety

Re: Best approach for using H2 over public internet

I have not used H2 too long, but so far it is running great in tomcat (embedded). It flies on my dataset. I would recommend just creating an jsp/servlet interface to it. On Nov 1, 4:10 pm, Rinse Lemstra r.lems...@xso.nl wrote: What would be the best approach to use H2 over a public internet,

Re: Criteria if a database should be embedded

I don't know if there are any strict criteria for embedded vs non- embedded. I am running a web app using H2 in embedded mode, and it is working fine. I chose embedded mode for the following reasons: a) it is faster, as connections are within the same JVM and not over another protocol b) simple

backup encrypted database

Hi H2 Group, I have an H2 database that I use the built in AES file encryption. I would like to find a good backup solution in which the backup is also encrypted using the same file password I could copy the database file, but the database will always be running, so simply copying the file may

Re: backup encrypted database

://h2database.com/html/grammar.html#script It's true that the backup statement doesn't encrypt the output file directly; in theory you could use the encrypting file system implementation, but this implementation is not complete yet. Regards, Thomas On Mon, Feb 20, 2012 at 8:21 PM, Adam McMahon

closing old connections from h2 console

Hi, I am using H2 embedded in a web app with db_close_delay=-1. The code in my app closes connections fine. However, on occasion we use the h2 web console to manage things. Since it is in a web browser, on occasion we close the browser while forgetting to hit the disconnect button (I know,

Re: closing old connections from h2 console

. See also http://h2database.com/html/grammar.html?highlight=DB_CLOSE_DELAYsearch=db_close#set_db_close_delay Regards, Thomas On Thu, Sep 27, 2012 at 3:28 PM, Adam McMahon ad...@cs.miami.edujavascript: wrote: Hi, I am using H2 embedded in a web app with db_close_delay=-1. The code

Making certain transactions more durable

I am trying to understand H2's durability and how to get improved durability for certain transactions. In general I think that the default durability of H2 is quite appropriate for my use case (knowing that in a rare crash I could lose about one second of data). But there are certain

Re: Making certain transactions more durable

:09 AM, Adam McMahon ad...@cs.miami.edujavascript: wrote: By putting ACCESS_MODE_DATA=rws in a connection url, does that only affect the this connection or does that affect all connections? If it only affects If it is the first connection i.e. the one that causes the database

getting the ROWNUM of a specific row with a WHERE

Hi, I am trying to get the rank of a user's score in an H2 Database. The following query gives me all the scores ordered descending with a corresponding row number (this part works): * *select *, rownum as rn from (select userId, score from nw_data ORDER BY score DESC) But I only want

[h2] Re: getting the ROWNUM of a specific row with a WHERE

that is as simple as possible? Regards, Thomas On Wed, Feb 27, 2013 at 7:20 PM, Adam McMahon ad...@cs.miami.edujavascript: wrote: Hi, I am trying to get the rank of a user's score in an H2 Database. The following query gives me all the scores ordered descending with a corresponding row

Re: [h2] Understanding Query Cache

UTC-5, Noel Grandin wrote: On 2013-11-04 03:39, Adam McMahon wrote: On a similar note, it seems like a global query cache, as opposed to a separate one for each session, would be useful. Can't do that, because of differences in access rights, etc, etc. between sessions. So we use

[h2] H2 database on multiple threads/cores

Hi H2 Group, I am trying to get my head around H2's use of multiple threads and cores. I know this topic has been dealt with before, but I am still a little confused on the topic. My questions are with regard to the default mode of H2 (not MVCC or multithreaded=true). A) the documentation

Re: [h2] H2 database on multiple threads/cores

Noel, As always, thanks for your insights. It is good to know that H2 at its cores is single threaded while at the same time multithreaded at outer layers. , this will help me as I think about my design. SSD is a good option, looking to use Digital Ocean https://www.digitalocean.com/.

Re: [h2] H2 Clusters- load balancing SELECT querries

:40 schrieb Adam McMahon ad...@cs.miami.edujavascript: : H2 Group, I am checking out the clustering features of H2. I know that the clustering features are fairly barebones. I am interested in using clusters/replication for increased performance, as opposed to simply having a failsafe

[h2] inserrting/updating CLOB using prepared statements

Hi, I would like to use the Clob data type for my variable length strings that average around 300 characters. I see in the doc that I should use PreparedStatement.setCharacterStream to store values. Instead I am simply using PreparedStatment.setString(). This seems to work. Is there

[h2] web console Servlet - how to not allow others to create new databases

Hi, We use embedded H2 in a webapp with tomcat. We use the servlet webconsole to manage the database. Our database is password protected, which should allow some security. But I don't see how to prevent an unauthorized user from creating a new database and filling it with junk if they visit

[h2] understanding preparestatement reuse

Hi, I frequently use PreparedStatements, but I am a bit unsure how to properly reuse them. I create a statement String sql = select * from users where score?; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, n); ResultSet rs = ps.executeQuery(); // do stuff rs.close();

Re: [h2] understanding preparestatement reuse

, 2014 5:55:42 AM UTC-4, Noel Grandin wrote: On 2014-06-03 08:37 PM, Adam McMahon wrote: Now lets say that I want to later reuse that prepared statement, which is more accurate: 1) do I need to keep a reference to the actual PreparedStatment object (in this case ps). Yes

[h2] using h2 servlet console with embedded derby

Hi, We are building our web-app to be compatible with both embedded H2 and embedded Derby, with the idea that we can switch out one for the other at any time. Though we plan on using H2 in our final app (if everything works as expected). We use the H2 console (as a servlet) to manage the

Re: [h2] using h2 servlet console with embedded derby

Thomas, I am using h2/derby (both embedded) within a tomcat app. The H2 console is exposed via the WebServlet. The h2 and derby jars are placed in tomcat's lib directory. I know derby.jar is loaded because the web app works fine with derby - except derby does not work with the H2 console.

[h2] preserve capitalization of column names in returned meta data

Hi, When we get the meta data of a result set, it appears that the capitalization of the column names is not preserved. Is there anyway to preserve it the returned meta results? -Adam -- You received this message because you are subscribed to the Google Groups H2 Database group. To

[h2] does LIMIT improve performane

Hi, I have a large table in which I need to find only one matching record from the table. If I put in a LIMIT 1, could that improve performance? If there is a limit, does the engine stop searching once the LIMIT has been found? Or is the LIMIT applied as a filter after the search has been

[h2] deadlock with clob data types?

Hi, I am using H2 to store text as clobs. The operations go like this: The clobs are read into a String from the DB The String is transformed into a json ojbect the json object is modified the object is transformed into a json string and saved back into H2 as a clob Multiple threads are

Re: [h2] deadlock with clob data types?

I guess another route would be to change the datatype to varchar. Is this possible? Is it safe? -Adam On Sunday, June 14, 2015 at 12:46:55 PM UTC-4, Adam McMahon wrote: Noel, Thanks for the suggestion. When you say latest version, do you mean latest stable version, or the latest build

Re: [h2] deadlock with clob data types?

AM UTC-4, Noel Grandin wrote: We have fixed a bunch of stuff around LOB'S since then. I suggest you update to the latest version but run with mvstore disabled On Sun, 14 Jun 2015 at 02:31, Adam McMahon ad...@cs.miami.edu javascript: wrote: Hi, I am using H2 to store text as clobs

[h2] setFetchSize with large ResultSet embdded mode

H2 Group, I am trying to understand Statement.setFetchSize with large ResultSets. The documentation says that it this function sets the number of rows suggested to read in one step. What does it mean by "suggested". In what situations is the setFetchSize value not honored? My the

Re: [h2] MVMap multikey

I don't know exactly how this maps to H2's MV. but can't you just have only Strings as keys. When you need a Long as a key, just take the string value of the Long. -Adam On Wednesday, December 9, 2015 at 4:31:50 PM UTC-5, Jan Juno wrote: > > Hi > > Each entry has 2 types of keys Long and a

[h2] Are order of results always the same?

Hi, It looks like the results of a querry are always given back in the same order. Is this always true? I know the order of a returned result is not the same as the insertion order. But if a query is performed, and then repeated, will the result set be in the order (assuming the relevant

Re: [h2] Are order of results always the same?

Thanks for your pointer on this. What about this scenario: I have a rather large table that I would like to process in the application in batches using a paging mechanism with OFFSET and LIMIT. I plan to do this in a simple loop which gets results N at a time, and each time increasing

[h2] performance of updating rows with indexes

H2 Group, I have read that there is sometimes a performance hit when updating a row that has an index. But, I imagine that this performance hit only typically comes when the indexed column is changed, and that there will likely be no performance hit if a non-index column is updated. For the

[h2] embedded vs sever mode stability of h2?

Hi, We currently use h2 embedded in a tomcat app. Works great. There may be a time in which we have to move h2 to a separate server, and thus use h2 server mode. I get the impression that most people use h2 in embedded mode. Is there any reason to think that h2 server mode is less stable

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

> SSL and keystores. > > I might contribute code if you open a github project. I usually handle > this "proxy" access to H2 using jruby, but it's straight forward to make > the concept you described in java. > > Christian > > On Thursday, February 23, 2017 at 7:19:28 P

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

>> The solution you propose would involve would establish a connection for each query? I'd imagine that would not perform too well. I agree that it would not perform too well to establish a new connection at each query. Instead, I plan on using "long-lived" jdbc connections. At a client's

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

curity, 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 connect to an H2 >> database froma non-JVM language (in this case php). I know

[h2] connecting to h2 from non-java languages

Hi Group, i am considering a project that may require me to connect to an H2 database froma non-JVM language (in this case php). I know the postgres driver might work and querus is an option, but I would like to consider a more general option: an http connector. It should be pretty easy to

[h2] Re: Query Optimizer limitations

Hi, when you say performance is very bad, what do you mean? What is slow to you? By slow, do you mean slow compared to other databases? Does it use index? Do you expect the querry to use an index that it is not using (try the explain method to learn about what the query is doing behind the

[h2] Re: Query Optimizer limitations

As far as I am aware, the problem with the query optimizer is that it does not use indexs in every situation that one might expect. But yours seems to be using the index. So, I don't know what else it could be (but I am no expert at H2). How long does it take to run the query? How many rows

[h2] Re: Query Optimizer limitations

. On Thursday, January 19, 2017 at 3:26:21 PM UTC-5, Adam McMahon wrote: > > As far as I am aware, the problem with the query optimizer is that it does > not use indexs in every situation that one might expect. But yours seems > to be using the index. So, I don't know what else it could

[h2] how are subqueries executed in an IN Select

Hi, When a SELECT is used as a sub-query of an IN, is the sub-query only executed once? Consider the following made up query that finds all staff members who are in the state of California who are also a student majoring in Computer Science. select * from students where major='CSC' and id IN

[h2] Re: how are subqueries executed in an IN Select

Hi, I put in a logger via a user defined function, and it seems to in fact re-execute the IN sub-query for every matching outer row. Am I interpreting this correctly? Any work around? -Adam On Sunday, September 18, 2016 at 8:25:20 PM UTC-4, Adam McMahon wrote: > > Hi, > > W

Re: [h2] Re: how are subqueries executed in an IN Select

Noel, Thanks for the suggestion. I opted for a JOIN for the time-being. I am a little hesitant to upgrade h2 versions, because we are using it in production. When I have time, I hope to locally test the newest stable h2 to see if the IN Select is now optimized to not repeat a sub-query.

[h2] Re: H2 embeded User-Defined class problem spring hibernate unit test

; However, you do not have any static methods, that will be something to look into. Also, the ALIAS is to the method, not the class. On Tuesday, September 27, 2016 at 1:28:27 PM UTC-4, Adam McMahon wrote: > > The jar with your user defined class needs to be in your class path

[h2] H2 embeded User-Defined class problem spring hibernate unit test

The jar with your user defined class needs to be in your class path, that seems to be the first place to check -- 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] order of updates

Hi, If updates are being sent to H2 (embedded) from several threads, is there any known order in which the updates occur? Are they queued in the order the update operations arrive to H2? Example: ThreadA - updateA at T1 ThreadB - updateB at T2 ThreadC - updateC at T3 (T1

[h2] MVstore - off heap storage

Hi, I am a long time user of H2, but I am now just exploring the MVstore map api. Great stuff, a few questions: [1] Off heap storage - is there any way to designate the size of the offheap store, or does it just continue to fill up until it runs out of memory? [2] Off heap storage - Can

[h2] Re: MVstore - off heap storage

ead of being built into the OffHeapStore class. -Adam On Monday, October 17, 2016 at 7:27:43 PM UTC-4, Adam McMahon wrote: > > Hi, > > I am a long time user of H2, but I am now just exploring the MVstore map > api. Great stuff, a few questions: > > [1] Off heap st

[h2] help picking H2 version

Thanks for clarification -- 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] help picking H2 version

Hi Group, Sorry if I am being repetitive here, but I am still a little confused about which H2 version to pick. I have the following needs: [1] JSBC with PageStore [2] MVStore Map API with OffHeapBuffer what is the recommended stable version for those two features? -Adam -- You received

[h2] Re: Main differeces between 1.3 and 1.4 versions

Good info here So, what version of 1.4x would you use in your production environment. I use pagestore for JDBC, but am also going to be integrating MVSTORE via the map api (not JDBC) using its OffHeapStore. What version would you recommend. Thanks, -Adam On Tuesday, October 18, 2016 at

[h2] Re: Honest Opion

You may want to clarify what you mean by reliable. But here is my 2 cents: I have several small h2 databases that have run continuously for several years as part of a web app. No problems at all. As with any database, you will want to perform regular backups, and understand well how to manage

[h2] question about mixed mode

I have an embedded database that I would like to open up to other servers. It seems like mixed mode is the way to go. But, I see in the docs for automatic mixed mode that "All processes need to have access to the database files". By this I read that the remote servers all need to be able to

[h2] Re: question about mixed mode

about right. -Adam On Saturday, November 12, 2016 at 6:39:35 PM UTC-5, Adam McMahon wrote: > > I have an embedded database that I would like to open up to other servers. > It seems like mixed mode is the way to go. But, I see in the docs for > automatic mixed mode that "All proce

[h2] Version 1.3.176 not on download page

Hi, I am using H2 Version 1.3.176. But I went to the download page and I no longer see it listed. Was this version removed for some reason? I would like to have the latest 1.3x, and I thought Version 1.3.176 was that version, but now that I see it is no longer online, I am wondering if it

[h2] Any negative effects of a large varchar type?

I have this table with a varchar of size 10K create table doc( id BIGINT, name VARCHAR(50), body VARCHAR(1) ); More often than not, the table will only store small values (less than 200 characters) in the "body" column. However, I want it to be big just in case. My

[h2] Select data from range between conversational openings and closings (chat log analysis using SQL / H2 Database)

My first instinct would be do do this at the application level. Not with a single query. It will be easy to do this in the application. So long as your data is not too big. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from

[h2] how do you store you documents?

Hi, This is a little off topic, but I thought I would get feedback from the H2 group. When you need to store JSON in a database, how do you go about it? Do you use H2, if not what do you use? I was looking for a dependable embedded JVM based DB that handles docs, but came up empty.

[h2] mvstore api - index?

Hi, I am interested in the MVStore KeyValue store API. It is not clear to me how indexes work for this. Are the indexes implicitly created on each key for fast lookup? I get the impression that the key are stored in a set of pages, and likely those pages will be accessed more frequently,

[h2] Define a limit size for a table

You can easily handle this at the application level by checking the size of table before insert. -- 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] embedded server (like derby?)

Hi H2 Group, Derby has a mode called "embedded server". In this mode, Derby accepts embedded connecions internally within the same JVM and also external connections via the network server protocol. Is there something similar to this in H2? Best, -Adam -- You received this message because

[h2] Re: embedded server (like derby?)

Hi, Thanks that is exactly what I was looking for. Perhaps I should have tried that before I asked my question. That being said, is this fact mentioned in the docs on the H2 site (I did not see it). If it is not mentioned I think it would be good to add a section about this. This embedded

[h2] Re: is it save to use H2 in production for applications like Accounting or Stock?

Hi, I do not write accounting apps. But I use H2 in production for HR type apps for a major university. H2 (embedded in tomcat) has been running 24/7for several years with no corruption. We use the latest 2.3x version of H2. We have built a custom layer that replicates the data in realtime

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

2. > > And it's the same with us, fervent users: we always look forward to the > next release of H2, it's hard for us to tell which specific release was > good enough in terms of speed and stability. > > On Wednesday, March 7, 2018 at 4:46:43 AM UTC+1, Adam McMahon wrote: >&

[h2] Redhat warning about production use of H2

Redhat has a warning about H2 in several places in their docs. The H2 database should *not* be used in a production environment. This is a very small, self-contained datasource that supports all of the standards needed for testing and building applications, but is not robust or scalable enough

[h2] Re: How to import a .db file into h2 database?

Hi, I .db file is a native h2 database file. It does not need to be imported, as it is simply the database itself, it simply needs to be opened. The are several ways to open and read an h2 .db file, but the easiest way is to use the web interface console. The quick start guide will be your

[h2] mvstore api vs berkeleydb JE?

Hi, I am looking for an embeddable key-value store. MVStore and Berkeley DBJE seem to be nice fits. Aside from the licensing differences, are there any major considerations when deciding between these two systems? The API of MVStore seems to have much less bloat, but any experience with

[h2] Re: How to import a .db file into h2 database?

Hi, You can put the h2 db files wherever you want. in the h2 web console, point to the h2 db files in the *JDBC Url*. Look at this link on how to format the url to reference your file: http://www.h2database.com/html/features.html#database_url You will likely want a url for an embedded

[h2] order of operations in a where cluase

Hi group, I have a user defined function MyF which is pretty slow. In a where clause, I would like MyF to be run last. example select * from users where age>10 AND weight>100 AND MyF(data)==true considering MyF is slow, I only want it to run if it first passes the initial conditions (which

[h2] Connecting remotely to H2

Start h2 in server mode. Connect to the h2 server in java using the jdbc. Look at the url jdbc examples to connect to a server. Use normal jdbc to query the database. Adam -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from

[h2] autocommit=false slower than autocommit=true??

Hi, I have a need to batch together a number of inserts/updates into a single transaction. I am using autocomit=false to make them a single transaction and then call commit() at the end. When I do this, the performance drops significantly (about 10-15 times slower) as opposed to doing each

[h2] Re: autocommit=false slower than autocommit=true??

Andrei, Thanks for the information that autocomiit=false is not expected to be slower. I looked closer at my application code and there seemed to be an unrelated problem in my application code that was causing the issue. So, the error was on my side. I am now getting expected performance.

[h2] Does H2's built in connection pool work with other JDBC dbs?

Hi, Does H2's built in connection pool work with other JDBC databases? I am specifically talking about org.h2.jdbcx.JdbcConnectionPool? I assumed it would work with any JDBC database, but I could not get it to work with MYSQL Thanks, -Adam -- You received this message because you are