Questions tagged [h2]
Database Engine built in Java.
17
questions
6
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 ...
- 10.8k
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 ...
- 395
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 ...
- 1,019
3
votes
0
answers
74
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 ...
- 10.8k
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 ...
- 3,247
2
votes
0
answers
878
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 ...
- 10.8k
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 ...
- 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 ...
- 195
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:
...
- 113
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
...
- 113
1
vote
0
answers
886
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 ...
- 121
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 ...
- 713
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 ...
- 111
0
votes
1
answer
197
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 (
...
0
votes
1
answer
169
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?
0
votes
2
answers
89
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....
- 3
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 ...
- 395