h2database/h2database

H2 is an embeddable RDBMS written in Java.

Welcome to H2, the Java SQL database.

The main features of H2 are:

• Very fast, open source, JDBC API
• Embedded and server modes; disk-based or in-memory databases
• Transaction support, multi-version concurrency
• Browser based Console application
• Encrypted databases
• Fulltext search
• Pure Java with small footprint: around 2.5 MB jar file size
• ODBC driver

More information: https://h2database.com

Downloads

Download latest version or add to pom.xml:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

Documentation

• Tutorial
• SQL commands
• Functions, aggregate functions, window functions
• Data types

Support

• Issue tracker for bug reports and feature requests
• Mailing list / forum for questions about H2
• 'h2' tag on Stack Overflow for other questions (Hibernate with H2 etc.)

h2database's Issues

CREATE SEQUENCE is strict about the order of its parameters

What steps will reproduce the problem?
CREATE SEQUENCE SEQ_GEN_SEQUENCE INCREMENT BY 50 START WITH 50

What is the expected output? What do you see instead?
The same as with:
CREATE SEQUENCE SEQ_GEN_SEQUENCE START WITH 50 INCREMENT BY 50

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2
1.0.66 (2008-01-18)

Do you know a workaround?
This sql statement is generated by an O/R mapper

How important/urgent is the problem for you?
would be nice

In your view, is this a defect or a feature request?
feature request

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 11 Mar 2008 at 6:14

result can not be acquired using table alias

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. execute a query with table alias: select id from sometable as mytable
2. get result using table alias: rs.getInt("mytable.id")

What is the expected output? What do you see instead?
it should return id, but one exception will be thrown:
org.h2.jdbc.JdbcSQLException: Column mytable.id not found [42122-78]

What version of the product are you using? On what operating system, file
system, and virtual machine?
version: h2-2008-08-28

Do you know a workaround?
No

How important/urgent is the problem for you?
It's very important for me.

In your view, is this a defect or a feature request?
It's a defect.

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 11 Sep 2008 at 4:06

Attachments:

• bug.java

CREATE_BUILD not supported in older version (1.0.0)

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Even connecting to the H2DB from Web-UI.
2. And when trying to call select query from Java code.
3.

What is the expected output? What do you see instead?
want to connect and fire SQL queries :)

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2DB = 1.0 / 1.0.2
JVM = 1.5
OS= Windows XP

Do you know a workaround?
No, but I got the root cause that CREATE_BUILD keyword is not supported in
 1.0.
But strangely when I use the latest H2 jar (1.0.2) from my Java API it
worked first time and then I try to open the same schema from the Web-UI
and it shows this exception: 
--------------
Syntax error in SQL statement SET CREATE_BUILD[*] 70; expected AUTOCOMMIT,
IGNORECASE, PASSWORD, SALT, MODE, COMPRESS_LOB, DATABASE, COLLATION,
CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, DEFAULT_TABLE_TYPE,
CREATE, HSQLDB.DEFAULT_TABLE_TYPE, CACHE_TYPE, FILE_LOCK, STORAGE,
DB_CLOSE_ON_EXIT, RECOVER, SCHEMA, LOGSIZE [42001-36] 
-------------------------

And after I got this exception on Web UI, I'm getting same exception while
trying to access the same schema from Java code. 
In H2 is the version mapping is there at the SCHEMA level or its at the DB
level. 


How important/urgent is the problem for you?
Its urgent as my whole system is working on H2.

In your view, is this a defect or a feature request?
This is a defect.

Please provide any additional information below.
I found in the changeLog that you have added this keyword CREATE_BUILD, but
not able to find from 
where you are generating this keyword. 
And even in the Parser.parseSet(), there is no check for SET CREATE_BUILD
as its there for 
other keyword, so I'm wondering why Driver is generating (if its) and
passing to the Parser.parse() 
method which parses all the SQL statement. 

Original issue reported on code.google.com by [email protected] on 8 Jul 2008 at 12:20

Various documentation errors

1. In the "Tutorial" page, at some point it is claimed that a file 
called .h2.server.properties is created in the user's home upon server 
start. That's not the case, at least not on Linux, with solely the TCP 
server started.

2. In the "Advanced Topics" page, there's a ;ist of properties that can 
be set as Java system properties (via -D). Among them 
is "h2.runFinalizers", but running "SELECT * FROM 
INFORMATION_SCHEMA.SETTINGS" tells something about "h2.runFinalize".

3. The documentation should state very clearly which settings are 
specific to the server (org.h2.tools.Server command line), which of them 
apply to a database, which of them apply to the first connection to a 
database and which of them apply to any connection.
Also, please make it clear whether the property names and values are case 
sensitive or not (in which cases).

4. Could you add some cross links between the Google H2 groups and the 
Google H2 project home? It would be very helpful since these facilities 
are not integrated unfortunately.

Thanks.

Original issue reported on code.google.com by [email protected] on 20 Nov 2007 at 10:09

error when renaming column to existing column name

What steps will reproduce the problem?

CREATE TABLE TEST 
( 
    ID INTEGER DEFAULT NULL NOT NULL, 
    ID2 INTEGER DEFAULT 0 
); 
ALTER TABLE test ALTER COLUMN ID2 RENAME TO ID 

What is the expected output? What do you see instead?

An error message only is expected, but instead the database gets 
corrupted. Later it reports "org.h2.jdbc.JdbcSQLException: Duplicate 
column name ID [42S21-59]". And in the h2 console it doesn't show the test 
table.
When adding a new column the behaviour is ok, but not when renaming to an 
existing name.

What version of the product are you using? On what operating system?

h2 build 1.0.59 and 1.0.61. Windows XP Home sp2. 

Original issue reported on code.google.com by [email protected] on 20 Nov 2007 at 12:00

NPE in TraceObject.toString when using TCP connection

What steps will reproduce the problem?
1. Run the attached Main class

What is the expected output? What do you see instead?
It should run successfully, instead I get:
Exception in thread "main" java.lang.NullPointerException
    at org.h2.message.TraceObject.toString(TraceObject.java:222)
    at org.h2.command.CommandRemote.toString(CommandRemote.java:231)
    at org.h2.jdbc.JdbcPreparedStatement.toString(JdbcPreparedStatement.java:1417)
    at H2Test.main(H2Test.java:13)

What version of the product are you using? On what operating system, file
system, and virtual machine?
I'm using the latest h2 version: 1.0.69

Do you know a workaround?
The problem doesn't occurred if I use the embedded mode.

How important/urgent is the problem for you?
This bug prevent the usage of H2 TCP mode with XAPool.

In your view, is this a defect or a feature request?
It's clearly a bug. 

Please provide any additional information below.
I previously registered
http://code.google.com/p/h2database/issues/detail?id=18 This bug is marked
as fixed but it's not the case.

Original issue reported on code.google.com by [email protected] on 11 Apr 2008 at 11:30

Attachments:

• H2Test.java

Apparent Performance regression from version 2007-12-27 to current

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. create database with number and string fields
2. create non-unique index on those columns
3. perform a lookup with those two parameters

With the earlier version, I am getting lookups of about 1ms, whereas I am
getting 250ms lookups in the current release.

I have checked both databases, and both seem to have the correct indexes
created.

We are using Java 6, hibernate, and I am on a linux system, though the
problem seems to appear on Windows as well.  

I think that we first noticed this issue in the Jan or Feb release.  It
definitely appears in the 3/29 and 4/20 ones.  If you need me to write a
small test program to demonstrate this, please let me know

Original issue reported on code.google.com by [email protected] on 23 Apr 2008 at 4:12

RandomUtils.getSecureRandom() creates a thread that never exits causing an embedded H2 database to hang.

What is the problem?
On a linux machine with sufficiently low entropy, a call to
java.security.SecureRandom.generateSeed(20), as in line 43 of RandomUtils,
will block until it has read 20 bytes from /dev/random. When using H2 as an
embedded database, the RandomUtils.getSecureRandom() function will create a
thread that may never exit. This thread may never exit, causing any JVM
using H2 to hang for an indeterminate amount of time.

What steps will reproduce the problem?
The problem is reproducible on a linux box with sufficiently low amounts of
entropy. The problem occurred on a i86pc running RHEL3. In particular, a
machine that will hang after several calls to "cat /dev/random". 

What version of the product are you using? 
The bug exists in at least 1.0.65, 1.0.66, 1.0.67, and trunk. 

Do you know a fix?
The problem is that the thread created in RandomUtils.getSecureRandom() may
never exit causing any JVM using H2 to run until /dev/random has yeilded 20
bytes to the H2 thread. No guarantee can be made about when this will occur
- it may take several days as our testing has shown. The easiest fix would
be to add "t.setDaemon(true)" to this thread so that it does not prevent
the JVM from exiting. However, this is a rather weak solution that avoids
the heart of the problem. A quick attempt at adding "t.interrupt()" after
line 59 "t.join(400)" did not fix the problem - the InputStream that is
reading from /dev/random does not seem to be interruptable. A true solution
should somehow kill the thread after a timeout (perhaps impossible in java)
or avoid the use of java.security.SecureRandom.generateSeed(20) since no
guarantees can be made for when it will return. 

How important/urgent is the problem for you?
Extremely urgent. This determines whether or not H2 can be used as an
embedded database on all our supported platforms. 

In your view, is this a defect or a feature request?
Defect.

Please provide any additional information below.
H2 has looked really great on all other aspects - its performance blew most
other competition out of the water and scaled very well to handle millions
of rows in a table. We would really love to put it to use as an embedded
database, but we simply can't use it if it will cause the whole JVM to hang
on certain systems. Please let me know if you can find a good fix for this
or if you need help testing your solution. Thanks a lot!

Original issue reported on code.google.com by [email protected] on 26 Feb 2008 at 8:49

Substring function returns zero precision

What steps will reproduce the problem?

    // This is result for H2
    @Test
    public void testSubstringH2() throws Exception {

        Class.forName("org.h2.Driver");

        Connection ca = DriverManager.getConnection("jdbc:h2:mem:", "", "");

        Statement sa = ca.createStatement();

        sa.execute("CREATE TABLE one (C CHARACTER(12))");

        ResultSet rs = sa.executeQuery("SELECT substring(C,2,1) as D from
one;");
        ResultSetMetaData md = rs.getMetaData();
        assertEquals(md.getPrecision(1), 0);  // !!! return 0
        ca.close();
    }

    // This is result for DB2 for similar table
    @Test
    public void testSubstringDB2() throws Exception {

        Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

        Connection ca = DriverManager.getConnection("jdbc:db2:***", "***",
"***");

        Statement sa = ca.createStatement();

        ResultSet rs = sa.executeQuery("SELECT substr(C1,2,1) as D from
another.mytable;");
        ResultSetMetaData md = rs.getMetaData();
        assertEquals(md.getPrecision(1), 1); // !!! returns 1
        ca.close();
    }


What is the expected output? What do you see instead?

I would expect substring return 1 or the width of the first argument if the
third parameter is not constant.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2-02-02-08

Do you know a workaround?

No

How important/urgent is the problem for you?

Will try to fix it myself first

In your view, is this a defect or a feature request?

Defect

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 17 Apr 2008 at 1:05

FK constraint violation

What steps will reproduce the problem?
1. Create a new database and execute this simple script:
drop table b if exists;
drop table a if exists;

create table a (
id integer identity not null,
code varchar(10) not null,
primary key(id)
);

create table b (
name varchar(100) not null,
a integer,
primary key(name),
foreign key(a) references a(id)
);

2. Now launch the two classes attached (they're interactive).
Allow DBTestA to create a new record in table A. Jump into DBTestB's 
console and have it create a new B referencing A. Commit the insertion.
Next, return to DBTestA's console and rollback the creation of the A 
record.

3. You'll be most pleased to discover that in table A there are no 
records, while in table B there's a record pointing to a phantom A!

What's sad, very sad is that this FK violation occurs in an environment 
where transactions are supposed to be isolated at READ_COMMITED level.
Moreover, in DBTestB you get a timeout exception due to a lock on table A 
if you uncomment the commented lines that read from A. So the claims 
regarding MVCC do not verify either (the lock was supposed to be SHARED 
according to the docs)!

What is the expected output? What do you see instead?
I expect an exception to be thrown when the B record is created (since 
the isolation is at READ_COMMITTED level) or at least upon commit (at a 
lower level of isolation).

What version of the product are you using? On what operating system?
Version 1.0.61, Linux.

Original issue reported on code.google.com by [email protected] on 20 Nov 2007 at 5:28

Attachments:

• DBTestA.java
• DBTestB.java

Cannot build

What steps will reproduce the problem?
build.bat compile 

What is the expected output? What do you see instead?
Package com.sun.javadoc doesn't exists.

What version of the product are you using? On what operating system, file
system, and virtual machine?

Windows XP SP3
JDK 1.6.0_06
H2 1.0.76

My JAVA_HOME is C:\Program files\Java\jdk1.6.0_06

I could fix this problem by this.
copy "%JAVA_HOME%\lib\*" "%JAVA_HOME%\..\lib\" 

Original issue reported on code.google.com by yukoba on 12 Aug 2008 at 5:39

unable to insert from CVS file - Column count does not match; SQL statement:

What steps will reproduce the problem?
1. execute the following query
INSERT INTO NORM.VALUE(VALUE_ID, DOM_ID, APPLICATION_ID, DESCRIPTION_ID,
DATEBEGIN, DATEEND)
    VALUES(
        SELECT VALUE_ID, DOM_ID, APPLICATION_ID, DESCRIPTION_ID, DATEBEGIN, DATEEND
        FROM CSVREAD('normValue.csv')
    );

table created with: 
CREATE TABLE IF NOT EXISTS NORM.VALUE(
    VALUE_ID        NORM_ID NOT NULL,
    DOM_ID          NORM_ID NOT NULL,
    APPLICATION_ID  NORM_ID NOT NULL,
    DESCRIPTION_ID  NORM_ID NOT NULL,
    DATEBEGIN       DATE,
    DATEEND         DATE,
    DATEUPDATE      DATE AS CURRENT_DATE(),
    USERUPDATE      NORM_USER AS USER());

date file is:
"VALUE_ID",         "DOM_ID",               "APPLICATION_ID",   "DESCRIPTION_ID",
"DATEBEGIN",    "DATEEND",  "DATEUPDATE",   "USERUPDATE"
"VALIDATE",         "MessageWhyCategory",   "NORM",             "2",                "2007-11-20",   ,       
"2007-11-20",   "SA"
"CREATE",           "MessageWhyCategory",   "NORM",             "4",                "2007-11-20",   ,       
"2007-11-20",   "SA"
"UPDATE-DELETE",    "MessageWhyCategory",   "NORM",             "5",            
"2007-11-20",   ,           "2007-11-20",   "SA"
"SECURITY",         "MessageWhyCategory",   "NORM",             "6",                "2007-11-20",   ,       
"2007-11-20",   "SA"
"SYSTEM",           "MessageWhyCategory",   "NORM",             "7",                "2007-11-20",   ,       
"2007-11-20",   "SA"


What is the expected output? What do you see instead?
no error 

What version of the product are you using? On what operating system?
h2 1.0.61 on windows with Java "1.6.0_02"

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 20 Nov 2007 at 10:25

CSVREAD field separator problem

Hello,

H2 has function to read CSV files. This function has following format:

CSVREAD(fileNameString [, columnNamesString [, charsetString [,
fieldSeparatorString [, fieldDelimiterString
[, escapeCharacterString [, nullString]]]]]]): resultSet

Unfortunately, parameter "fieldSeparatorString" has influence on a format
of "columnNamesString" instead of format of CSV file. Example:

CSVREAD('test.csv', 'a|b|c', 'UTF-8', ',' , '"' , '' , 'NULL');

This will be treated as try to read CSV file with one column with name
"a|b|c". Values should be enclosed into ',' with no escape character.

If fields separator set using API call for Csv instance then behavior is right.

H2 version 1.0.76

Regards,
Vladimir

Original issue reported on code.google.com by antiso on 11 Aug 2008 at 10:53

We are trying to implement h2 for one of our small commercial product.

What steps will reproduce the problem?

We are trying to implement h2 for one of our small commercial product.
And we are using DERBY for our commercial products.
we faced one problem
that:
in derby if we need to save date we  parse the date like ( dd-mm-yyyy)
and derby data base will accept.
But in H2 it is not accepting. can i get solution for this. We can parse 
in reverse. but normally ppl wont enter like this format.
if it is  got solute we can try H2 in our products

What is the expected output? What do you see instead?


What version of the product are you using? On what operating system, file 
system, and virtual machine?
the newer one

Do you know a workaround?
yes
How important/urgent is the problem for you?
urgent
In your view, is this a defect or a feature request?

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 14 Mar 2008 at 12:56

MVCC update after insert throws exception

What steps will reproduce the problem?
>> See attached unit test. Updating a record which has just been inserted
within one tx in mvcc-mode yields an Exception.

org.h2.jdbc.JdbcSQLException: Gleichzeitige Änderung in Tabelle
MITARBEITER: eine andere Transaktion hat den gleichen Datensatz geändert
oder gelöscht
Concurrent update in table MITARBEITER: another transaction has updated or
deleted the same row [90131-63]
    at org.h2.message.Message.getSQLException(Message.java:89)
    at org.h2.message.Message.getSQLException(Message.java:93)
    at org.h2.message.Message.getSQLException(Message.java:71)
    at org.h2.table.TableData.removeRow(TableData.java:268)
    at org.h2.log.UndoLogRecord.undo(UndoLogRecord.java:68)
    at org.h2.engine.Session.rollbackTo(Session.java:258)
    at org.h2.engine.Session.rollback(Session.java:239)
    at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:63)
    at org.h2.command.CommandContainer.update(CommandContainer.java:64)
    at org.h2.command.Command.executeUpdate(Command.java:122)
    at org.h2.jdbc.JdbcConnection.rollbackInternal(JdbcConnection.java:1197)
    at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:353)
    at
de.beebase.sandbox.h2.H2MvccTest.updateAfterInsertWithinTxWithMvcc(H2MvccTest.ja
va:33)
...

What is the expected output? What do you see instead?
>> All tests should pass, but updateAfterInsertWithinTxWithMvcc() fails.

What version of the product are you using? On what operating system, file
system, and virtual machine?
>> H2 1.0.63, Java(TM) SE Runtime Environment (build 1.6.0_03-b05),
OpenSuSE 10.3, Kernel 2.6.22.13-0.3-default

Do you know a workaround?
>> Turn MVCC off, see updateAfterInsertWithinTxWithoutMvcc()

How important/urgent is the problem for you?
>> Not critical in production

In your view, is this a defect or a feature request?
>> Defect

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 16 Dec 2007 at 6:07

Attachments:

• H2MvccTest.java

NPE in TraceObject.toString when using TCP connection

What steps will reproduce the problem?
1. Run the attached Main class

What is the expected output? What do you see instead?
It should run successfully, instead I get:
Exception in thread "main" java.lang.NullPointerException
    at org.h2.message.TraceObject.toString(TraceObject.java:214)
    at org.h2.command.CommandRemote.toString(CommandRemote.java:231)
    at org.h2.jdbc.JdbcPreparedStatement.toString(JdbcPreparedStatement.java:1417)
    at H2Test.main(H2Test.java:13)

What version of the product are you using? On what operating system, file
system, and virtual machine?
I'm using the latest h2 version (h2-2008-03-15.zip Version 1.0.68)

Do you know a workaround?
The problem doesn't occurred if I use the embedded mode.

How important/urgent is the problem for you?
This bug prevent the usage of H2 TCP mode with XAPool.

In your view, is this a defect or a feature request?
It's clearly a bug.

Please provide any additional information below.
I modified the method org.h2.message.TraceObject.toString to check if
p.getParamValue() is null, but the problem is maybe in
org.h2.expression.ParameterRemote.getParamValue().
In case of embedded mode the method
org.h2.expression.Parameter.getParamValue() is called and this method check
if value is null but there is not check in
org.h2.expression.ParameterRemote.getParamValue()

Original issue reported on code.google.com by [email protected] on 23 Mar 2008 at 10:19

Attachments:

• H2Test.java

Patch French localization properties

Hello,

please find attached a patch to update the french localization properties.

Cheers,
Olivier Parent

Original issue reported on code.google.com by [email protected] on 22 Oct 2008 at 12:19

Attachments:

• patch.txt

org.h2.store.Storage.allocate() is slow when inserting large rows

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

Run the attached H2BlobWriteTester which inserts 10,000 10Kb blobs inplace.
Profiling shows that roughly 30% of the test time is spent in
org.h2.store.DiskFile.allocate()

What is the expected output? What do you see instead?

I would expect this method to be faster since it only updates the size of
the file. It doesn't actually write any data to the file.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.0.67
Windows XP with NTFS file system

Do you know a workaround?

No

How important/urgent is the problem for you?

Urgent since our application is making heavy use of BLOBs. 

In your view, is this a defect or a feature request?

Defect

Please provide any additional information below.

I've attached the test code as well as an export of the profiler output.

Original issue reported on code.google.com by [email protected] on 6 Mar 2008 at 6:14

Attachments:

• H2BlobWriteTester.java
• blob-test-profile.html

Slow query

The "select.sql" performed on an attached database is very slow - cca 30
seconds on my computer. We tried to execute ANALYZE multiple times; we
tried EXPLAIN but it seems all relevant columns have indices. We tried the
newest H2 1.0.67. Can you help us?

Original issue reported on code.google.com by [email protected] on 26 Feb 2008 at 10:42

Attachments:

• select.sql
• h2.zip

java.lang.NullPointerException at org.h2.message.TraceSystem.getTrace(TraceSystem.java:83)

Here is the stack trace:
Caused by: java.lang.NullPointerException
    at java.util.LinkedHashMap$Entry.addBefore(LinkedHashMap.java:333)
    at java.util.LinkedHashMap$Entry.access$600(LinkedHashMap.java:309)
    at java.util.LinkedHashMap.createEntry(LinkedHashMap.java:437)
    at java.util.LinkedHashMap.addEntry(LinkedHashMap.java:418)
    at java.util.HashMap.put(HashMap.java:474)
    at org.h2.message.TraceSystem.getTrace(TraceSystem.java:83)
    at org.h2.engine.Database.getTrace(Database.java:382)
    at org.h2.command.Command.<init>(Command.java:87)
    at org.h2.command.CommandContainer.<init>(CommandContainer.java:25)
    at org.h2.command.Parser.prepareCommand(Parser.java:231)
    at org.h2.engine.Session.prepareLocal(Session.java:236)
    at org.h2.engine.Session.prepareCommand(Session.java:218)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1020)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:1217)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:163)
    at
com.j_spaces.sadapter.GenericJDBC.JDBCStorageAdapter.internalUpdateEntry(JDBCSto
rageAdapter.java:4558)


As it seem to me the issue is caused by not synchronized access to global
variable "traces" inside TraceSystem class by multiple threads.

Original issue reported on code.google.com by Barak.Bar on 28 Oct 2008 at 2:35

CSVWRITE causes NullPointerException without a nullString specified for version 1.0.68.

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

1. Create a database with a null value.
2. Call csvwrite() without specifying the "optional" nullString parameter.

Here's a sample program:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class TempNull {
    public static void main(String[] args) throws Exception {
        Class.forName("org.h2.Driver");
        Connection connection = DriverManager.getConnection("jdbc:h2:mem:");
        try {
            Statement statement = connection.createStatement();
            try {
                statement.execute("create table whatever (text varchar(10))");
                statement.execute("insert into whatever values(null)");
                statement.execute("call csvwrite('whatever.csv', 'select *
from whatever')");
            } finally {
                statement.close();
            }
        } finally {
            connection.close();
        }
    }
}


What is the expected output? What do you see instead?

I expect a csv file with an empty string for the nullString. Instead I get
this stack trace:

Caused by: java.lang.NullPointerException
    at org.h2.tools.Csv.writeRow(Csv.java:264)
    at org.h2.tools.Csv.writeResultSet(Csv.java:83)
    at org.h2.tools.Csv.write(Csv.java:128)
    at org.h2.tools.Csv.write(Csv.java:151)
    at org.h2.expression.Function.getValueWithArgs(Function.java:1033)
    at org.h2.expression.Function.getValue(Function.java:369)
    at org.h2.command.dml.Call.query(Call.java:60)
    at org.h2.command.CommandContainer.query(CommandContainer.java:78)
    at org.h2.command.Command.executeQueryLocal(Command.java:139)
    ... 5 more


What version of the product are you using? On what operating system, file
system, and virtual machine?

Java 6 on Windows, with h2database 1.0.68.


Do you know a workaround?

I didn't test specifying the nullString since that requires many other
parameters to be filled in. Instead, I've reverted to 1.0.67 for now.


How important/urgent is the problem for you?

I don't intend to upgrade until this is fixed.


In your view, is this a defect or a feature request?

Defect.


Please provide any additional information below.

n/a

Original issue reported on code.google.com by [email protected] on 21 Mar 2008 at 5:36

Persistent way to configure connections

This is actually an enhancement.
It is rather clumsy to be forced to always configure one's Connection via 
the URL or the properties. In HSQLDB I had the <db>.properties file as a 
form of persistent configuration.
What I suggest is that connections should have default settings persisted 
in the database along with the database-specific settings (via the SET 
commands). Setting transmitted via the Connection properties or the URL 
(which I understand that overwrites the former) should be considered 
temporary, valid only during that connection's life time.

Example: 
I'm only interested in working in MVCC=true mode. The database URL 
resides in a Hibernate configuration file which occasionally can be 
edited by power users of the application. But I do not want to rely on 
their ability to configure my database access. In fact I don't want them 
to configure it.

Original issue reported on code.google.com by [email protected] on 20 Nov 2007 at 10:29

MVCC: Read uncommited

What steps will reproduce the problem?

1. Save ReadOnlyCommitedERROR.java 
2. Run: javac -cp h2.jar ReadUncommitedERROR.java
3. Run: java -cp .;h2.jar ReadUncommitedERROR

What is the expected output? 

START
STOP

What do you see instead?

START
expected: Hello, got: Hallo
STOP

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.0.69
Windows XP SP2
java version "1.6.0_02"
Java(TM) SE Runtime Environment (build 1.6.0_02-b06)
Java HotSpot(TM) Client VM (build 1.6.0_02-b06, mixed mode, sharing)

Do you know a workaround?

No.

How important/urgent is the problem for you?

I think that it is critical.

In your view, is this a defect or a feature request?

This is defect. Definitely.

Please provide any additional information below.

Save ReadOnlyCommitedERROR.java 
Run: javac -cp h2.jar ReadOnlyCommitedOK.java
Run: java -cp .;h2.jar ReadOnlyCommitedOK

Result:
START
STOP

Differences in primary key key definition:
"PRIMARY KEY(ID, NAME)"
and
"PRIMARY KEY(ID)"

Original issue reported on code.google.com by [email protected] on 10 Apr 2008 at 7:24

Attachments:

• ReadOnlyCommitedOK.java
• ReadUncommitedERROR.java

Using JaQu with mysql

When using a java class with a String and using JaQu, the sql for the
CREATE is off as it produces a VARCHAR instead of VARCHAR(30) [pick your
length) 

Original issue reported on code.google.com by [email protected] on 2 Dec 2008 at 7:03

inappropriate index selection

I have a query where H2 appears to make a poor index choice that I didn't
expect.  The tables involved in the query are:

create CACHED TABLE ITEM (
 ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
 REPOSITORY INTEGER NOT NULL, 
 FOLDER VARCHAR(255) NOT NULL, 
 NAME VARCHAR(255) NOT NULL, 
 URL VARCHAR(265) NOT NULL, 
 PRIMARY KEY (ID),
 CONSTRAINT UNIQUE_NAME UNIQUE(REPOSITORY, FOLDER, NAME), 
 CONSTRAINT UNIQUE_URL UNIQUE(REPOSITORY, URL), 
 CONSTRAINT REPOS_ITEM_FK FOREIGN KEY (REPOSITORY) REFERENCES
REPOSITORY(ID) ON DELETE CASCADE
)

create CACHED TABLE CLASS_REFERENCE (
 ID INTEGER NOT NULL,
 NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (ID),
 CONSTRAINT CLASS_REFERENCE_FK FOREIGN KEY (ID) REFERENCES ITEM(ID) ON
DELETE CASCADE
)

Now the ITEM.URL is highly selective:

select  column_name, selectivity from INFORMATION_SCHEMA.Columns  where
Table_name='ITEM';
COLUMN_NAME     SELECTIVITY  
ID  100
REPOSITORY  1
FOLDER  1
NAME    98
URL 99
LAST_MODIFIED   1
PARSEABLE   1
TYPE    1
SECURITY_GROUP  1
ID_ATTRIBUTE    1
VARID_ATTRIBUTE 1
CATEGORIES  1
(12 rows, 10 ms)

So, when I try the query:

explain select cr.name from item i join class_reference cr on (cr.id=i.id)
where i.repository=2 and url = 'confrep:/PEParameter/This_OrderedServAddr.xml'

H2 uses the index created for the UNIQUE_URL constraint. 

SELECT CR.NAME
FROM PUBLIC.ITEM I /* PUBLIC.UNIQUE_URL_INDEX_2: REPOSITORY = 2 AND URL =
'confrep:/PEParameter/This_OrderedServAddr.xml' */ /* WHERE (I.REPOSITORY =
2) AND (URL = 'confrep:/PEParameter/This_OrderedServAddr.xml') */
INNER JOIN PUBLIC.CLASS_REFERENCE CR /* PUBLIC.PRIMARY_KEY_FC: ID = I.ID
AND ID = I.ID */ ON 1=1
WHERE (CR.ID = I.ID) AND ((I.REPOSITORY = 2) AND (URL =
'confrep:/PEParameter/This_OrderedServAddr.xml'))

That is great, and what I expected, but if I instead try querying for 2
URLs, H2 uses the name index,
which is not selective at all:

explain select cr.name from item i join class_reference cr on (cr.id=i.id)
where i.repository=2 and (url =
'confrep:/PEParameter/This_OrderedServAddr.xml' or
url='confrep:/CMParameter/This_OrderedServAddr.xml');

SELECT CR.NAME
FROM PUBLIC.ITEM I /* PUBLIC.UNIQUE_NAME_INDEX_2: REPOSITORY = 2 */ /*
WHERE (I.REPOSITORY = 2) AND ((URL =
'confrep:/PEParameter/This_OrderedServAddr.xml') OR (URL =
'confrep:/CMParameter/This_OrderedServAddr.xml')) */
INNER JOIN PUBLIC.CLASS_REFERENCE CR /* PUBLIC.PRIMARY_KEY_FC: ID = I.ID
AND ID = I.ID */ ON 1=1
WHERE (CR.ID = I.ID) AND ((I.REPOSITORY = 2) AND ((URL =
'confrep:/PEParameter/This_OrderedServAddr.xml') OR (URL =
'confrep:/CMParameter/This_OrderedServAddr.xml')))

These results were obtained with 1.1.104.  (1.0.71, which we are using also
behaves the same).

Original issue reported on code.google.com by [email protected] on 15 Dec 2008 at 4:54

DATE-Problem: 0000-01-01 becomes 0001-01-01 (syntactically)

What steps will reproduce the problem?

1. create table with two rows
CREATE TABLE p(d DATE);
INSERT INTO p VALUES('0000-01-01');
INSERT INTO p VALUES('0001-01-01');

2.
SELECT * FROM p;

yields two identical rows

-----------|-
D          |
-----------|- 
0001-01-01 |
0001-01-01 |
-----------|-

3.
SELECT * FROM p WHERE d='0000-01-01'

at least returns only one row

-----------|-
D          |
-----------|- 
0001-01-01 |
-----------|-

... nevertheless with wrong representation. 

What is the expected output? What do you see instead?
0000-01-01 

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 Version: H2 1.0.67 (2008-02-22)
OS: WIN XP Pro Version 2002
Java: 1.6.0_05

Do you know a workaround?
No

How important/urgent is the problem for you?
minor

In your view, is this a defect or a feature request?
defect

Original issue reported on code.google.com by [email protected] on 12 Mar 2008 at 8:06

Error when using large records

Sometimes an exception 'File ID mismatch' or 'try to add a record twice'
occurs after large records (8 KB or larger) are updated or deleted. Such
large records are created when not using BLOBs or CLOBs, or when using many
columns. 

The problem is that empty space is re-used too early when records are
deleted that span multiple pages (a page is 8 KB).

A workaround is to avoid very large records by using CLOB / BLOB. Another
workaround is to set the system property 'h2.reuseSpaceQuickly' to 'false'
before loading the H2 driver (System.setProperty("h2.reuseSpaceQuickly",
"false") or on the command line (java -Dh2.reuseSpaceQuickly=false ...)

Original issue reported on code.google.com by [email protected] on 21 Apr 2008 at 7:02

Incorrect LEFT JOIN with aggrigated query in the join condition

I am facing problems with a very comlex query. To make it clear I created 
a very easy (but a bit stupid) query. I hope it makes things clear.

What steps will reproduce the problem?
1. I have a database as follows:
CREATE TABLE ORDERS(
    ID int NOT NULL, 
    NAME varchar(100) NOT NULL 
);

CREATE TABLE ORDERDETAILS(
    ID int NOT NULL, 
    ORDERID int NOT NULL, 
    PRODUCT varchar(100) NOT NULL,
    QUANTITY int NOT NULL
);

ALTER TABLE ORDERDETAILS ADD 
FOREIGN KEY (ID)
REFERENCES ORDERS (ID);

2. The content is this:
<dataset>
    <ORDERS ID="120" NAME="First Order"/>
    <ORDERDETAILS ID="501" ORDERID="120" PRODUCT="Cool product" 
QUANTITY="5"/>
    <ORDERDETAILS ID="502" ORDERID="120" PRODUCT="Stupid product" 
QUANTITY="2"/>
    <ORDERS ID="121" NAME="Second Order"/>
    <ORDERDETAILS ID="503" ORDERID="121" PRODUCT="Amazing product" 
QUANTITY="3"/>
    <ORDERS ID="122" NAME="Third Order"/>
    <ORDERDETAILS ID="504" ORDERID="122" PRODUCT="Fun product" 
QUANTITY="5"/>
</dataset>

3. The query is this:
SELECT * 
FROM ORDERS AS o 
LEFT JOIN ORDERDETAILS AS od 
    ON (od.ORDERID = o.ID AND od.QUANTITY = (SELECT MAX(QUANTITY) FROM 
ORDERDETAILS))

(In words: Select all orders and if they have the maximum number of 
products also show the details)

What is the expected output? What do you see instead?
I expect to see three records (Like SQL Server does):
120  First Order   501  120  Cool product  5
121  Second Order  null null null          null
123  Third Order   504  122  Fun product   5

But it only gives First Order and Third Order.
It seems like it is treating it as an INNER JOIN


What version of the product are you using? On what operating system?
Build 61, 2007-11-10. Only the h2.jar file for testing in JUnit. Windows XP


Please provide any additional information below.
-

Original issue reported on code.google.com by [email protected] on 13 Nov 2007 at 1:49

Patch StartBrowser

Hello,

here's a patch for the start browser utility:

- Enables the JAVA 1.6 desktop browser launch method (StartBrowserDesktop
class), which uses the user's preferences on every platform it is implemented. 
- Adds a property (h2.browserUserAgent) to launch a specific browser.
  Use case: your company mandates an obsolete browser (say IE6), while your
preference goes to a more modern and safer browser (say Firefox).

It keeps the same entry point, StartBrowser.openUrl(url) and fallbacks
nicely with previous Java versions.

I didn't really test the SwitchSource option but it should work.

Please tell me what you think.

Cheers,
Olivier

Original issue reported on code.google.com by [email protected] on 23 Oct 2008 at 1:22

Attachments:

• patch-start-browser.txt

Problem with old-style Oracle outer join syntax

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
CREATE TABLE Address (id NUMBER NOT NULL, city VARCHAR2(255), PRIMARY KEY
(id));
CREATE TABLE Person (id NUMBER NOT NULL, name VARCHAR2(255), ADDRESS_ID
NUMBER, PRIMARY KEY (id));
INSERT INTO PERSON (id, name) values (1, 'Frank');

SELECT t0.id, t1.id, t1.city, t2.id, t2.name, t0.name FROM Person t0,
Address t1, Person t2 WHERE t0.ADDRESS_ID = t1.id(+) AND t1.id =
t2.ADDRESS_ID(+)


What is the expected output? What do you see instead?
Expected: 1 row [1, NULL, NULL, NULL, NULL, Frank]
Seen: empty result set

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2-2008-07-14


Do you know a workaround?
No

How important/urgent is the problem for you?
https://issues.apache.org/jira/browse/OPENJPA-292 is still pending, an
Oracle DBMS produces the expected output

In your view, is this a defect or a feature request?
defect

Please provide any additional information below.
The statement is generated by OpenJPA 1.1.0 with db-dictionary "oracle" for
a simple bidirectional one-to-one relationship.

Original issue reported on code.google.com by [email protected] on 27 Jul 2008 at 7:16

Calling Statement.execute() ends up in neverending loop when no disk space is available

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. full the hard drive to have no free disc space left
2. perform INSERT, UPDATE on database

What is the expected output? What do you see instead?
Some JdbcSQLException saying there is no disc space left should be thrown.
Instead it ends up in StackOverflowError:
General error: java.lang.StackOverflowError [50000-71]
    at org.h2.message.Message.getSQLException(Message.java:92)
    at org.h2.message.Message.convert(Message.java:175)
    at org.h2.message.TraceObject.logAndConvert(TraceObject.java:202)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164)
Caused by: java.lang.StackOverflowError
    at java.nio.ByteBuffer.<init>(ByteBuffer.java:259)
    at java.nio.HeapByteBuffer.<init>(HeapByteBuffer.java:52)
    at java.nio.ByteBuffer.wrap(ByteBuffer.java:350)
    at java.lang.StringCoding$CharsetSD.decode(StringCoding.java:187)
    at java.lang.StringCoding.decode(StringCoding.java:228)
    at java.lang.String.<init>(String.java:405)
    at java.lang.String.<init>(String.java:433)
    at java.io.RandomAccessFile.setLength(Native Method)
    at org.h2.util.FileUtils.setLength(FileUtils.java:34)
    at org.h2.store.fs.FileObjectDisk.setFileLength(FileObjectDisk.java:29)
    at org.h2.store.FileStore.setLength(FileStore.java:302)
    at org.h2.engine.Database.freeUpDiskSpace(Database.java:1441)
    at org.h2.store.FileStore.freeUpDiskSpace(FileStore.java:273)
    at org.h2.store.FileStore.setLength(FileStore.java:306)
    at org.h2.engine.Database.freeUpDiskSpace(Database.java:1441)
    at org.h2.store.FileStore.freeUpDiskSpace(FileStore.java:273)
    at org.h2.store.FileStore.setLength(FileStore.java:306)
    at org.h2.engine.Database.freeUpDiskSpace(Database.java:1441)
    at org.h2.store.FileStore.freeUpDiskSpace(FileStore.java:273)
    at org.h2.store.FileStore.setLength(FileStore.java:306)
        ...

What version of the product are you using? On what operating system, file
system, and virtual machine?
Version 1.0.71, Linux , ReiserFS

Do you know a workaround?
No

How important/urgent is the problem for you?
Normal

In your view, is this a defect or a feature request?
Defect

Please provide any additional information below.
Seems it is a problem in Database.freeUpDiskSpace() which tries to set the
size of emergencyReserve file to a half, but this ends up again in
Database.freeUpDiskSpace()

Original issue reported on code.google.com by [email protected] on 21 May 2008 at 11:25

getConnection fails without throwing exception

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.  DriverManager.getConnection("jdbc:h2:/malformedURL
nothingCreated","",null);

failure due to bad syntax (the / character )throws no exception- it fails
silently. 
2.
3.

What is the expected output? 
An exception 

What do you see instead?
nothing. 


What version of the product are you using? 
1.0.74

On what operating system, file system, and virtual machine?
winXP64 
Java 6.10


Do you know a workaround?
does not apply

How important/urgent is the problem for you?
not urgent

In your view, is this a defect or a feature request?
I think of it as a defect since it fails dues to a malformed URL or
IllegalArgumentException. Things that will always fail (couldn't possibly
work) like div by 0 and as in the example above, should throw exceptions

Please provide any additional information below.
I also noticed that the keyword or SQL keyword "file" made no difference in
the URL constrcution, it would work with it if the URL was well formed and
without it if the URL was well formed. 

Original issue reported on code.google.com by [email protected] on 11 Jul 2008 at 4:18

enhancement: allow to call 0-parameters functions without parenthesis + enhance error (code/description)

Hi,
It would be great to be able to call function with no parameters without
the parenthesis.

Example:

select user from dual;

expect to be similar to:

select user() from dual;

Resulting column name: USER (not USER())

Moreover, the current (h2-1.1.105) errorcode is wrong:

select user, database from dual;
Column USER not found; SQL statement:
select user, database from dual [42122-105] 42S22/42122

"COLUMN_NOT_FOUND_1 = 42122
The error with code 42122 is thrown when referencing an non-existing
column. Example:

 CREATE TABLE TEST(ID INT);
 SELECT NAME FROM TEST;"

Morevoer USER is not a column of the DUAL table.

Best regards,
Loïc Lefèvre

Original issue reported on code.google.com by [email protected] on 17 Jan 2009 at 11:04

MVCC: Cannot read rollbacked row.

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

Run the attached class as java application.

What is the expected output?
------------
ID VAL
-- ---------
 1 Value1
 2 Value2
------------
OK.

What do you see instead?
------------
ID VAL
-- ---------
 2 Value2
------------
Exception in thread "main" org.h2.jdbc.JdbcSQLException: テーブル TEST 
に並
行して更新が行われました: 
別のトランザクションが、同じ行に更新か削除を行い
ました
Concurrent update in table TEST: another transaction has updated or deleted
the same row [90131-74]
    at org.h2.message.Message.getSQLException(Message.java:103)
    at org.h2.message.Message.getSQLException(Message.java:114)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.table.TableData.removeRow(TableData.java:287)
    at org.h2.table.Table.updateRows(Table.java:356)
    at org.h2.command.dml.Update.update(Update.java:131)


What version of the product are you using? On what operating system, file
system, and virtual machine?

H2  Version 1.0.74 (2008-06-21)
Fedora9
Java SE 6 1.6.0_06


How important/urgent is the problem for you?

I think it is critical.


In your view, is this a defect or a feature request?

This is a defect.


Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 9 Jul 2008 at 6:20

Attachments:

• MvccRollbackTest.java

org.h2.Driver.connect freezes on Windows on ~1500th invocation

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Compile attached class and run it with H2 on classpath with argument 10000
The class just creates and closes jdbc.Connection objects in a loop.

What is the expected output? What do you see instead?
Expected: it should print 10000 lines and terminate succesfully. Instead,
it will halt on line ~1500 (this varies, sometimes on 1060, sometimes on
3000, etc).

What version of the product are you using? On what operating system, file
system, and virtual machine?
Windows Server 2003R2 Enterprise Edition. Java is 32bit "1.6.0_07"
Java(TM) SE Runtime Environment (build 1.6.0_07-b06)
Java HotSpot(TM) Client VM (build 10.0-b23, mixed mode, sharing). The bug
also occurs on Windows XP.
Important note: we failed to reproduce this bug on Linux, both on 32 and 64bit.

Do you know a workaround?
We can delay the problem by pooling connections, however we do not know a
workaround yet.

How important/urgent is the problem for you?
Medium priority.

In your view, is this a defect or a feature request?
Defect.

Please provide any additional information below.
Using JMX management we got two relevant thread stacktraces: the "main"
thread stacktrace:
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(Unknown Source)
java.io.BufferedInputStream.fill(Unknown Source)
java.io.BufferedInputStream.read(Unknown Source)
java.io.DataInputStream.readInt(Unknown Source)
org.h2.value.Transfer.readInt(Transfer.java:148)
org.h2.engine.SessionRemote.done(SessionRemote.java:400)
org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:105)
org.h2.engine.SessionRemote.connect(SessionRemote.java:263)
org.h2.engine.SessionRemote.<init>(SessionRemote.java:83)
org.h2.engine.SessionRemote.createSession(SessionRemote.java:211)
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:97)
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:87)
org.h2.Driver.connect(Driver.java:57)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
H2ConnectionTester.main(H2ConnectionTester.java:19)

The "H2 tcp server (...) thread" stack trace:
java.lang.Thread.sleep(Native Method)
org.h2.engine.Engine.openSession(Engine.java:132)
org.h2.engine.Engine.getSession(Engine.java:108)
org.h2.server.TcpServerThread.run(TcpServerThread.java:110)
java.lang.Thread.run(Unknown Source)

Original issue reported on code.google.com by [email protected] on 25 Sep 2008 at 2:25

Attachments:

• H2ConnectionTester.java

DATA_TYPE, NULLABLE and SQL_DATA_TYPE are java.lang.Short

When the following sample programs are executed on H2,
the following results are obtained.

Sample program:

import java.sql.*;

public class MetaDataTest {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(
                    "jdbc:h2:tcp://localhost/~/test", "sa", "");
            Statement stmt = con.createStatement();
            DatabaseMetaData dmd = con.getMetaData();
            rs = dmd.getColumns(null, null, null, null);
            rs.next();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String name = rsmd.getColumnName(i);
                Object value = rs.getObject(i);
                String type = (value == null)
                        ? "<null>" :
value.getClass().getCanonicalName();
                System.out.println(name + "\t" + type);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
        }
    }

}

Result:

TABLE_CAT       java.lang.String
TABLE_SCHEM     java.lang.String
TABLE_NAME      java.lang.String
COLUMN_NAME     java.lang.String
DATA_TYPE       java.lang.Short
TYPE_NAME       java.lang.String
COLUMN_SIZE     java.lang.Integer
BUFFER_LENGTH   java.lang.Integer
DECIMAL_DIGITS  java.lang.Integer
NUM_PREC_RADIX  java.lang.Integer
NULLABLE        java.lang.Short
REMARKS java.lang.String
COLUMN_DEF      <null>
SQL_DATA_TYPE   java.lang.Short
SQL_DATETIME_SUB        java.lang.Integer
CHAR_OCTET_LENGTH       java.lang.Integer
ORDINAL_POSITION        java.lang.Integer
IS_NULLABLE     java.lang.String

DATA_TYPE, NULLABLE and SQL_DATA_TYPE are java.lang.Short.

However, I think that these should be java.lang.Integer.

See: 
http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns(
java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

Environment:
H2 Version 1.0.74 (2008-06-21)
Windows XP SP3
Java SE 6 1.6.0_03

I use Number#intValue as a workaround.
Therefore, this problem is not urgent for me.

In my view, this is a defect.

Result on HSQLDB:

TABLE_CAT       <null>
TABLE_SCHEM     java.lang.String
TABLE_NAME      java.lang.String
COLUMN_NAME     java.lang.String
DATA_TYPE       java.lang.Integer
TYPE_NAME       java.lang.String
COLUMN_SIZE     java.lang.Integer
BUFFER_LENGTH   java.lang.Integer
DECIMAL_DIGITS  <null>
NUM_PREC_RADIX  <null>
NULLABLE        java.lang.Integer
REMARKS java.lang.String
COLUMN_DEF      <null>
SQL_DATA_TYPE   java.lang.Integer
SQL_DATETIME_SUB        <null>
CHAR_OCTET_LENGTH       java.lang.Integer
ORDINAL_POSITION        java.lang.Integer
IS_NULLABLE     java.lang.String
SCOPE_CATLOG    <null>
SCOPE_SCHEMA    <null>
SCOPE_TABLE     <null>
SOURCE_DATA_TYPE        <null>
TYPE_SUB        java.lang.Integer

Original issue reported on code.google.com by [email protected] on 28 Jun 2008 at 1:26

Version 1.0.62: Starting the Server doesn't work with some settings

What steps will reproduce the problem?
Starting the Server tool with the following settings does not work in
version 1.0.62:

java org.h2.tools.Server -tcpAllowOthers true -webAllowOthers true

What is the expected output? What do you see instead?

I used to work, now the possible options are listed.

Original issue reported on code.google.com by [email protected] on 26 Nov 2007 at 5:35

LPAD, RPAD functionality in the attached version

Hi,

I have a H2.jar file. I just want to know which version it is (1.0.63/...)?

I need LPAD, RPAD functionality in the attached version of JAR. Because I 
cannot change the production immediately. Can you help me on this? 

Please respond me.

Thanks in advance,
Suresh

Original issue reported on code.google.com by [email protected] on 13 Nov 2008 at 5:18

Attachments:

• h2.jar

NullPointerException in TableFilter.setPlanItem() using user defined function

I just tried 1.1.100 and experience a new failure (works in 1.0.71).
The exception is:
Caused by: java.lang.NullPointerException
    at org.h2.table.TableFilter.setPlanItem(TableFilter.java:167)
    at org.h2.command.dml.Optimizer.optimize(Optimizer.java:237)
    at org.h2.command.dml.Select.preparePlan(Select.java:798)
    at org.h2.command.dml.Select.prepare(Select.java:729)
    at org.h2.command.Parser.prepareCommand(Parser.java:233)
    ... 32 more

The query that it was trying to prepare was:
select i.folder, i.name from ITEM i where i.repository=? and (i.id in
(select id from errors_in_group(?, ?, ?))) 

errors_in_group is a user defined function defined as follows:
public static ResultSet errorsInGroup(Connection connection, Integer
repository, String groups, Boolean isLike) throws SQLException;

Original issue reported on code.google.com by [email protected] on 6 Oct 2008 at 4:44

CSVREAD should support '*' as columnNameString and charsetName for default behaviour

What steps will reproduce the problem?
1. Export data to csv file from a internationalized version of Excel in a
locale where comma is decimal separator (for instance Norwegian). 

2. Note that it is not possible to have Excel export the fields
comma-separated, since comma is a symbol to use in all decimal-value cells.
Instead they are separated by some other locale-specific separator-char
such as semicolon.

3. Open a h2:mem-database in squirrel. Try to import the csv-file and note
that in order to specify correct separator char you have to specify a lot
of other stuff first that is very cumbersome to find the correct values
for, including charset and all the columns under sql-friendly names. Note
that specifying columns '*' does not work as you would hope (all columns as
found in the file).


It is a little late to reorder the parameter list I guess, but the
situation could be improved a lot by using '*' as synonym for default
behaviour in the parameters prior to the one you want to set (meaning all
columns, default charset,...). 

Original issue reported on code.google.com by [email protected] on 2 Oct 2008 at 11:16

File id mismatch problem (maybe Corruption)

I have about ~700 installation of my program using h2 engine (on remote
kiosk PC) and regularly (but not too often, about 2-7 times a month)
getting some corruption of unknown cause or cryptography failure (on XTEA).
Versions tried 1.0.77, 1.0.79, 1.1.101 (last one only when opening)

Errors:
1.
org.h2.jdbc.JdbcSQLException: General error: java.lang.RuntimeException:
File ID mismatch got=-1373246757 expected=17 pos=3904 true
org.h2.store.DiskFile:/easysoft/.private/31100.data.db
blockCount:1926853611; SQL statement:
CREATE PRIMARY KEY ON PUBLIC.BILL(ID) [50000-74]

2.
java.lang.RuntimeException: Unexpected code path [50000-101]
at org.h2.store.DiskFile.init(DiskFile.java:415)

Other errors are like above.

I've investigated that (on 1.0.79 and up for sure) database got repaired
when connection is closed and reopened. But this procedure sometime
problematic to do when connections are managed by some third party
connection management (for example JPA implementation or just some pool).

So I expect maybe this bug (if it is bug) get fixed or probably database
can be repaired when opening first connection (not by reopening).
One such database attached to Issue

Attached Database use connection parameter ';CIPHER=XTEA' username 'sa'
and password '31100 31100'

Original issue reported on code.google.com by [email protected] on 20 Oct 2008 at 5:41

Attachments:

• 31100.zip

ClassCastException at FullText.java:562

What steps will reproduce the problem?
1. Connect to a Database which already has an FT index
2. CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init"
3. CALL FT_INIT()
4. SELECT * FROM FT_SEARCH('Hello', 0, 0)
   where 'Hello' must be in "FT.WORDS"
This will throw an Exception:
Caused by: java.lang.ClassCastException: java.lang.Long
    at org.h2.fulltext.FullText.search(FullText.java:562)

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2 Version 1.0.61 
Linux 2.6.16.27-0.6-smp
Java 1.5.0_07-b03

Do you know a workaround?
Use int/getInteger in FullText.java:221/224.

How important/urgent is the problem for you?
I'm using that workaround.

In your view, is this a defect or a feature request?
Looks like a defect.

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 28 Nov 2007 at 12:47

error 50000-104 ArrayIndexOutOfBoundsException

I'm getting the following exception when running some tests:
com.intecbilling.replib.cache.CacheException: org.h2.jdbc.JdbcSQLException:
General error: java.lang.ArrayIndexOutOfBoundsException: i=4 size=1; SQL
statement:
select i.folder, i.name from ITEM i where i.repository=? and ((i.type=? or
i.type=?) and i.name like ? and regexp_replace(i.folder,
'.*[CMFact|PEFact]/', '') in (select cr_1.name from class_reference cr_1
join item i_1 on i_1.id=cr_1.id where i_1.type in (?, ?) and i_1.name like
?)) [50000-104]
    at
com.intecbilling.replib.cache.CacheItemSearchProcessor.execute(CacheItemSearchPr
ocessor.java:80)
    at com.intecbilling.replib.cache.Cache.findByCriteria(Cache.java:2321)
    at
com.intecbilling.replib.cache.CacheTest.testFindBySxpVariableCriteria(CacheTest.
java:1625)
Caused by: org.h2.jdbc.JdbcSQLException: General error:
java.lang.ArrayIndexOutOfBoundsException: i=4 size=1; SQL statement:
select i.folder, i.name from ITEM i where i.repository=? and ((i.type=? or
i.type=?) and i.name like ? and regexp_replace(i.folder,
'.*[CMFact|PEFact]/', '') in (select cr_1.name from class_reference cr_1
join item i_1 on i_1.id=cr_1.id where i_1.type in (?, ?) and i_1.name like
?)) [50000-104]
    at org.h2.message.Message.getSQLException(Message.java:103)
    at org.h2.message.Message.convert(Message.java:257)
    at org.h2.message.Message.convert(Message.java:226)
    at org.h2.command.Command.executeQueryLocal(Command.java:143)
    at org.h2.command.Command.executeQuery(Command.java:122)
    at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
    at
com.adc.profiler.sql.ProfilePreparedStatement.executeQuery(ProfilePreparedStatem
ent.java:174)
    at com.intecbilling.replib.cache.query.Query.execute(Query.java:226)
    at
com.intecbilling.replib.cache.CacheItemSearchProcessor.execute(CacheItemSearchPr
ocessor.java:77)
    ... 24 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: i=4 size=1
    at org.h2.util.ObjectArray.throwException(ObjectArray.java:56)
    at org.h2.util.ObjectArray.get(ObjectArray.java:79)
    at org.h2.index.ViewIndex.find(ViewIndex.java:161)
    at org.h2.table.TableFilter.next(TableFilter.java:294)
    at org.h2.table.TableFilter.next(TableFilter.java:349)
    at org.h2.command.dml.Select.queryFlat(Select.java:492)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:560)
    at org.h2.command.dml.Query.query(Query.java:233)
    at org.h2.command.CommandContainer.query(CommandContainer.java:81)
    at org.h2.command.Command.executeQueryLocal(Command.java:141)
    ... 29 more
... Removed 22 stack frames

I'm hoping the stack trace is useful for diagnosis, because unfortunately I
can't produce a simple test to produce the problem.  Running the query by
itself works OK.  It appears to be the sequence of prior operations that
cause the failure.  

This error was obtained with 1.1.104.  I was trying to use 1.1.104 and
found that some queries like the one above weren't returning the same
results as they used to with 1.0.71.  But when I ran the individual queries
with 1.1.104 the results looked right.  Once again, prior operations seemed
to mess up the results in some way that doesn't seem to be easy to reproduce.

Original issue reported on code.google.com by [email protected] on 19 Dec 2008 at 1:58

Windows service Wrapper not starting because of incorrect classpath (../bin/h2.jar != h2-<version>.jar)

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. call service\0_run_server_debug.bat

What is the expected output? What do you see instead?
Inside wrapper.conf the classpath refers to h2.jar
  wrapper.java.classpath.2=../bin/h2.jar
but the jar file inside the zip distributions contains the version number.
  e.g. h2-1.1.103.jar

What version of the product are you using? On what operating system, file
system, and virtual machine?
http://www.h2database.com/h2-2008-11-07.zip

Do you know a workaround?
- rename the jar file

How important/urgent is the problem for you?
low

In your view, is this a defect or a feature request?
defect

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 12 Nov 2008 at 12:29

Test

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.
2.
3.

What is the expected output? What do you see instead?


What version of the product are you using? On what operating system, file 
system, and virtual machine?


Do you know a workaround?

How important/urgent is the problem for you?

In your view, is this a defect or a feature request?

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 26 Jul 2008 at 11:00

NullPointerException in ValueInt while closing Database

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Use H2 in Tomcat.
2. Restart or Hotdeploy Tomcat within Eclipse IDE
3. Exception (see below) occurs everytime Tomcat is trying to shut down

What is the expected output? What do you see instead?
No output at all is expected. Instead, the following exception occurs:
Exception in thread "Thread-5" java.lang.NullPointerException
    at org.h2.value.ValueInt.get(ValueInt.java:62)
    at org.h2.engine.Database.removeMeta(Database.java:785)
    at org.h2.engine.Database.update(Database.java:1354)
    at org.h2.schema.Sequence.flush(Sequence.java:125)
    at org.h2.schema.Sequence.close(Sequence.java:142)
    at org.h2.engine.Database.close(Database.java:1082)
    at org.h2.engine.DatabaseCloser.run(DatabaseCloser.java:75)

What version of the product are you using? On what operating system, file
system, and virtual machine?
v1.1.102 on Win2000 within Eclipse IDE running a Java WebApp within Tomcat 5.5

Do you know a workaround?
No

How important/urgent is the problem for you?
Low, as it only happens at shutdown

In your view, is this a defect or a feature request?
Defect. A NPE is very likely to be a defect :-)

Please provide any additional information below.

Datasource configuration in Spring ApplicationContext:
   <bean id="H2InMemoryDB"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="org.h2.Driver" />
      <property name="url" value="jdbc:h2:db_h2;AUTO_RECONNECT=TRUE" />
      <property name="username" value="sa" />
      <property name="password" value="" />
   </bean>


Thanks for H2 anyway, it's a really cool product and I love to use it for
demoing software and for unit testing purposes. We're integrating it in a
finance solution.

Original issue reported on code.google.com by [email protected] on 10 Nov 2008 at 9:07

Support IP Address binding

What steps will reproduce the problem?
N/A

What is the expected output? What do you see instead?
To allow machine with multiple IP addresses to start H2 database server
with the same port in different addresses.

Add startup parameter, e.g. -webAddress, -tcpAddress etc.

What version of the product are you using? On what operating system, file
system, and virtual machine?
N/A

Do you know a workaround?
Use different port for different instances

How important/urgent is the problem for you?
Not very urgent, but when I deploy embedded H2 databases in an application
to two JBoss instance on the same machine (binding to two different IP
address), I have to change port number for two deployment, which is not
convenient. So, it's quite urgent. And the change is trivial anyway.

In your view, is this a defect or a feature request?
Feature request

Please provide any additional information below.
My initial email to the group:
http://groups.google.com/group/h2-database/browse_thread/thread/5c5a9d0a87c31c5f

JBoss configuration document
http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfiguringMultipleJBossInstancesOnOneM
achine
remarks: it uses a -b <ip-addressORhost> parameter

Sample code in JBoss on creating Socket
http://viewvc.jboss.org/cgi-bin/viewvc.cgi/jbossas/trunk/jnp/src/main/org/jnp/in
terfaces/TimedSocketFactory.java?revision=7609&view=markup&pathrev=35186

Socket JavaDoc
http://java.sun.com/j2se/1.4.2/docs/api/java/net/Socket.html

The H2 Code that need to be changed:
http://h2database.googlecode.com/svn/trunk/h2/src/main/org/h2/util/NetUtils.java
instead of : new Socket(address, port);
uses:   Socket(String host, int port, InetAddress localAddr, int localPort) 

And take a parameter.

Original issue reported on code.google.com by [email protected] on 8 Jan 2008 at 4:40

Column aliased and accentuation

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. CentOS with java version "1.6.0_07" and locale pt_PT.UTF-8
2. select tblTest.id "column_id" from tblTest


What is the expected output? What do you see instead?
Caused by: java.lang.Error: org.h2.jdbc.JdbcSQLException: Column ID not
found [4
2122-105]
And text accentuation problems on INSERTs. Replacing accentuation at '?'.

What version of the product are you using? On what operating system, file
system, and virtual machine?
Version 1.1.105 (2008-12-19, Beta) 

Do you know a workaround?
In Ubuntu 8.04 with java version "1.6.0_10" and locale LANG=pt_PT.UTF-8 not
had this problem.

How important/urgent is the problem for you?
With that problem I cannot used it version.

In your view, is this a defect or a feature request?
A little bug. Or the problem is my wild scenary.

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 23 Dec 2008 at 12:19

Can observe the occasional double allocation error on slow machines in 1.0.72

On a fairly slow Linux machine (embedded system) we sometimes get the
"index corrupted" double allocation error when opening a database after the
machine was restarted abruptly. This seems to be a timing issue (something
is not initialized in time for the check?) since the problem does not
happen every time with exactly the same set of database files. Opening the
same files on a faster Windows PC never displays the problem.

The call stack is:

db.index.db page 22 blocks 1408-1471
        at org/h2/message/Message.getInternalError (Message.java:129)
        at org/h2/store/DiskFile.setPageOwner (DiskFile.java:676)
        at org/h2/store/DiskFile.setBlockOwner (DiskFile.java:588)
        at org/h2/store/DiskFile.init (DiskFile.java:371)
        at org/h2/engine/Database.open (Database.java:537)

H2 Version: 1.0.72

Original issue reported on code.google.com by [email protected] on 30 May 2008 at 3:49

Syntax error in SQL statement SET DATESTYLE TO 'ISO' (RHEL4+odbc+psycopg2+python-2.5)

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
-bash-3.00$ python
ActivePython 2.5.2.2 (ActiveState Software Inc.) based on
Python 2.5.2 (r252:60911, Mar 27 2008, 16:42:08)
[GCC 3.3.1 (SuSE Linux)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> conn=psycopg2.connect("dbname='db' user='user' password='pass' 
host='myhost' port='port'")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.OperationalError: can't set datestyle to ISO

1.
2.
3.
server log returns 
Query
SET DATESTYLE TO 'ISO';
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement SET DATESTYLE 
TO 'ISO'; expected ISO; SQL statement:



Same error when connecting using aqua studio.


What is the expected output? What do you see instead?


What version of the product are you using? On what operating system, file 
system, and virtual machine?



SELECT * FROM INFORMATION_SCHEMA.SETTINGS;
NAME   VALUE   
CREATE_BUILD 77 
CLUSTER '' 
DEFAULT_TABLE_TYPE 0 
WRITE_DELAY 500 
DEFAULT_LOCK_TIMEOUT 1000 
CACHE_SIZE 65536 
info.BUILD_ID 77 
info.VERSION_MAJOR 1 
info.VERSION_MINOR 0 
info.VERSION 1.0.77 (2008-08-16) 
property.java.runtime.version 1.6.0_01-b06 
property.java.vm.name Java HotSpot(TM) 64-Bit Server VM 
property.java.vendor Sun Microsystems Inc. 
property.os.name Linux 
property.os.arch amd64 
property.os.version 2.6.9-42.0.8.ELsmp 
property.sun.os.patch.level unknown 
property.file.separator / 
property.path.separator : 
property.line.separator 

property.user.country US 
property.user.language en 
property.user.variant   
property.file.encoding UTF-8 
EXCLUSIVE FALSE 
MODE PostgreSQL 
MULTI_THREADED 0 
MVCC FALSE 
QUERY_TIMEOUT 0 
LOB_FILES_IN_DIRECTORIES false 
h2.allowBigDecimalExtensions false 
h2.baseDir /projects/ 
h2.check true 
h2.check2 false 
h2.clientTraceDirectory trace.db/ 
h2.collatorCacheSize 32000 
h2.defaultMaxMemoryUndo 100000 
h2.emergencySpaceInitial 262144 
h2.emergencySpaceMin 65536 
h2.lobFilesInDirectories false 
h2.lobFilesPerDirectory 256 
h2.logAllErrors false 
h2.logAllErrorsFile h2errors.txt 
h2.maxFileRetry 16 
h2.maxQueryTimeout 0 
h2.lobCloseBetweenReads false 
h2.objectCache true 
h2.objectCacheSize 1024 
h2.objectCacheMaxPerElementSize 4096 
h2.optimizeIn true 
h2.optimizeInJoin false 
h2.optimizeMinMax true 
h2.optimizeSubqueryCache true 
h2.overflowExceptions true 
h2.recompileAlways false 
h2.redoBufferSize 262144 
h2.runFinalize true 
h2.scriptDirectory   
h2.serverCachedObjects 64 
h2.serverResultSetFetchSize 100 
h2.sortNullsHigh false 
CACHE_TYPE LRU 
info.FILE_DISK_WRITE 0 
info.FILE_DISK_READ 132 
info.FILE_INDEX_WRITE 1 
info.FILE_INDEX_READ 30 




Do you know a workaround?

How important/urgent is the problem for you?

Very - can't use product otherwise

In your view, is this a defect or a feature request?
defect 
Please provide any additional information below.



Thanks! 

Original issue reported on code.google.com by [email protected] on 4 Sep 2008 at 4:56