Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [h2]

Database Engine built in Java.

H2 is a database engine built in Java.

From h2database.com:

The main features of H2 are:

  • Very fast, open source, JDBC API
  • Embedded and server modes; in-memory databases
  • Browser based Console application
  • Small footprint: around 1 MB jar file size
17 questions
6
votes
1 answer

Keep H2 in-memory database between connections

I create and use a H2 database in-memory (no writing to storage) for demos and quick tests using this code in Java: Connection conn = DriverManager.getConnection( "jdbc:h2:mem:example_db" ) ; This works the first time, but then the database seems…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
4
votes
1 answer

CTE returns an empty set even though anchor is non-empty

I'm got an adjacency list consisting of two tables: CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY); CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL, UNIQUE KEY (parent_id,…
Gili
  • 1,049
  • 1
  • 16
  • 31
4
votes
1 answer

H2 ALTER TABLE ADD COLUMN BEFORE/AFTER

According to H2 syntax documentation available here I should be able to execute a query like: ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME; However apparently only this query works: ALTER TABLE EMPLOYEE ADD COLUMN…
dendini
  • 395
  • 2
  • 6
  • 13
3
votes
0 answers

On the INSERT statement in H2, what does `DIRECT` mean?

I noticed on the grammar documentation for INSERT in the H2 Database Engine, the optional word DIRECT. The documentation says: When using DIRECT, then the results from the query are directly applied in the target table without any intermediate…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
2
votes
3 answers

Alternatives for a LEFT OUTER JOIN query

Problem I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS. SQL Query SELECT * FROM tSysNls WHERE nlsGuid…
lumo
  • 445
  • 2
  • 6
  • 13
2
votes
0 answers

Is there a way to detect if there is an uncommitted transaction in H2?

I am using an H2 database with JDBC to create a connection with setAutoCommit(false). I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering if there was a way to directly query H2 for…
Bri
  • 195
  • 2
  • 7
2
votes
1 answer

How to aggregate datapoints in a table?

Suppose I have following table - CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER); I want to aggregate the data by calculating average of every 10 points in the table. i.e. If table has 20 data points the result is two aggregate…
Kshitiz Sharma
  • 3,357
  • 9
  • 33
  • 35
2
votes
0 answers

Does H2 Database support pessimistic locking?

Does the H2 Database support pessimistic locking? If so, how does its use interact with H2’s MVCC implementation? I'm not looking for advice on whether or not to use pessimistic locking. I have a specific need to emulate the behavior of another…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
1
vote
2 answers

Application testing: using multiple users in Oracle instead of mem H2. How to make it as fast as possible?

I decided to stop using H2 for obvious reasons (I have Oracle on production, compatibility mode is a fake). So, I wrote simple test framework which for each test in my application does the following: Generate random username (in example below it's…
whysoserious
  • 113
  • 4
1
vote
0 answers

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query engine decompose #1 into one of #2 or #3, i.e. pick…
user1009908
  • 111
  • 2
1
vote
0 answers

converting/adapting time postgres SQL to H2

I have a quite large SQL that runs towards postgres in production today, and due to some performance reasons, I am converting our integration tests to unit tests. The integration tests ran towards a docker postgres image, but it is simply way to…
munHunger
  • 121
  • 3
1
vote
1 answer

Are many two column indexes a bad smell?

I have table that contains failures. (15000 rows + on a abstract embedded system) A failure has the following fields: id PK BIGINT errorNumber INT source VARCHAR raisedAt BIGINT clearedAt BIGINT cleared (computed column for sorting purposes =…
Herr Derb
  • 113
  • 4
1
vote
1 answer

Will Derby, H2, or SQLite give faster load time and/or smaller file size than HSQL?

I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column. Each file is around 1.8GB in size with around 38 million…
Edmund
  • 733
  • 3
  • 10
  • 23
0
votes
1 answer

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition. However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the NOT NULL columns and I don't seem to find…
dendini
  • 395
  • 2
  • 6
  • 13
0
votes
1 answer

Recursive CTE throws temp tablespace is empty error

I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do. CREATE TABLE SPD_OWNER.EMP ( EMPID NUMBER, NAME VARCHAR2(20), MGRID…
Cyriac George
  • 3
  • 1
1
2