Questions tagged [h2]

Database Engine built in Java.

Filter by
Sorted by
Tagged with
1 vote
0 answers
866 views

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 ...
munHunger's user avatar
  • 121
0 votes
1 answer
196 views

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 ( ...
Cyriac George's user avatar
3 votes
0 answers
72 views

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 ...
Basil Bourque's user avatar
5 votes
1 answer
11k views

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 ...
Basil Bourque's user avatar
1 vote
1 answer
44 views

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 ...
Herr Derb's user avatar
  • 113
0 votes
1 answer
168 views

H2 Database - Alter multiple databases

I need to alter a table in H2 db. But we have millions of databases with the same same table. What may be the feasible way to alter such databases?
user3111540's user avatar
0 votes
2 answers
87 views

Regex rules engine based on SQL

I have a table which I use to determine actions based on conditions provided. This is what it looks like right now. IPAddress, MACAddress, Hostname, ScriptName, Argument1, Argument2, Argument3 10.2.1....
McJagger's user avatar
1 vote
1 answer
2k views

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 ...
Edmund's user avatar
  • 703
2 votes
0 answers
862 views

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 ...
Basil Bourque's user avatar
1 vote
2 answers
423 views

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: ...
whysoserious's user avatar
3 votes
1 answer
2k views

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 ...
Gili's user avatar
  • 1,009
2 votes
3 answers
14k views

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 ...
lumo's user avatar
  • 445
2 votes
0 answers
1k views

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 ...
Bri's user avatar
  • 195
4 votes
1 answer
22k views

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 ...
dendini's user avatar
  • 385
1 vote
0 answers
52 views

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 ...
user1009908's user avatar
0 votes
1 answer
3k views

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 ...
dendini's user avatar
  • 385
2 votes
1 answer
195 views

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 ...
Kshitiz Sharma's user avatar