Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [h2]

Database Engine built in Java.

0
votes
1answer
35 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 ( ...
3
votes
0answers
22 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 ...
0
votes
1answer
562 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 ...
0
votes
0answers
319 views

H2: Drop index which belongs to a constraint

We have a schema "Logger" that contains two tables: LogEntry (PK: Id) LogExceptions (Combined PK:LogEntryId,LineIndex) LogProperties (Combined PK:LogEntryId,PropertyKey) The schema has a foreign key ...
1
vote
1answer
38 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 ...
0
votes
1answer
53 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
2answers
31 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....
0
votes
1answer
809 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 ...
2
votes
0answers
395 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 ...
1
vote
2answers
330 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: ...
2
votes
1answer
826 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 ...
0
votes
0answers
374 views

DIRECT and SORTED in H2 and How to insert data with SORTED technique in H2

I was working on H2 Database and read Insert Statement Grammar and found that there are 2 types of insertion DIRECT and SORTED @ http://www.h2database.com/html/grammar.html#insert. As I know that ...
1
vote
3answers
9k 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 ...
2
votes
0answers
595 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 ...
0
votes
0answers
470 views

H2 database binary file full of @^@^@^@^

I am investigating H2 database performance and was curious to why the database file was several megabytes long when only storing hundreds kilos of text, I therefore opened the database file with vim ...
3
votes
1answer
19k 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 ...
1
vote
0answers
50 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 ...
0
votes
1answer
1k 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 ...
2
votes
1answer
173 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 ...