The H2 Compatible SQL Query

This article describes the difference between H2 compatible sql query and Sql compatible sql query.

1. All the [dbo] schema prefixes must be removed. In H2, the default schema is "public".

2. The most important thing in H2 is to understand the case sensitivity. It expects exact case in queries, otherwise it will throw error, such as "Table not found" or "Column not found".

3. By default H2 creates all the tables and columns in upper case letters. such as Create Table userinfo(info int); will be created as USERINFO and the column name in it will be INFO. Two ways to avoid this upper upper case problem.
    a. Using square bracket.
    b. Appending the following in the connection string.
DATABASE_TO_UPPER=FALSE;

4. H2 is square bracket sensitive. Note that [App] totally different from app.

5. In the above case it creates multiple tables in the same name but with different cases. Database_To_Upper is an another option to solve case sensitive issue. I recommend to use "Database_To_Upper" always.

6. Apart from tables and columns, the data in H2 is also case sensitive by default. To avoid case sensitivity, there are two options. IgnoreCase=True option in connection string.
Using collation. SET COLLATION ENGLISH STRENGTH PRIMARY;
To know more about collation set_collation

7. There are two benefits of using collation
    1. Case-insensitive columns are compared faster when using collation.
    2. String literals and parameters are however still considered case sensitive even if IgnoreCase option is set.

8. But if you look at the below picture the problem is fixed when we use Collation.

9. Collation must be set before any table created, otherwise you will receive an error.

10. To view current collation SELECT * FROM INFORMATION_SCHEMA.SETTINGS

The best practice is must not use square bracket. Use Database_To_Upper=False; and use collation as Primary. Information_Schema is the only system table mostly we refer for schema related queries. It contains all the tables, columns and constraints related information.

11. Remove "CLUSTERED" and "NONCLUSTERED" keywords from sql server compatible sql queries.

12. ; (semicolon is mandatory at the end of all statements)

13. There is no built-in user defined function in H2. We should use alias to simulate UDF. The alias uses the java code. Here is the example function.
    a. DROP ALIAS Table_Row_Count IF EXISTS;

    b. CREATE ALIAS Table_Row_Count AS
       'long Table_Row_Count(Connection conn, String tableName)
       throws SQLException {
       ResultSet rs = conn.createStatement().
       executeQuery("select count(*) from \"" + tableName + "\"");
       rs.next();
       return rs.getLong(1); }';
    c. We can use the above function by calling
       SELECT Table_Row_Count(table_name) AS RowCount FROM INFORMATION_SCHEMA.TABLES
14. '=' operator is not accepted as column alias as in sql server. We should use only "AS" operator.

15. CHECK CONSTRAINT ddl doesn't require to enforce constraints as in sql server. By default as soon as the contraints are created, they are enforced.

16. WITH CHECK doesn't require as in sql server.

17. SqlBulkCopy option is not available in H2.

H2 - Introduction

1. What is H2?

H2 is a super fast in-memory database. It is mainly used for integration testing.

2. Why do we use H2?

Since it is an in-memory database, it is faster than other database engines. It's foot print is very less (~1 MB). In a build environment we don't need a sql server instance to run integration tests, instead this H2 database simulates the sql server instance. H2 instances will be destroyed once the VM is closed. The following links are comparing H2 with other in-memory databases
H2-vs-SQLite
H2 and SQLite

3. Who manages H2 instance for the application?

In general any build tool. In our case, gradle creates the H2 database and fills data in it. A batch file also can manage H2 instance. Example H2 with Gradle and Flyway Program.

4. How do I configure H2 in my local?

a. Install H2 from download link
b. Default port number for H2 is 8082.
c. Then follow the quick start tutorial Quick Start. When launching the "H2 Console", if it says "Unable to access the network", then replace the IP address with "localhost" such as,

    http://192.168.112.13:8082/
    as
    http://localhost:8082/

    Reason: Because of the network settings in the office. I didn't face this error at my home.
d. Check the CLASSPATH environment variable, if it doesn't include the H2 jar file location, append it in the CLASSPATH variable. My CLASSPATH is variable is .;C:\Program Files (x86)\Java\jre7\lib\ext\QTJava.zip;C:\Program Files (x86)\H2\bin\h2-1.3.174.jar;
e. default username is : sa
f: default password is: <empty> (don't give any password)

5. What connection option should I use?

Although there are many ways to connect the database as mentioned here Database Urls. But for integration tests we must use Server Mode.
Example: jdbc:h2:tcp://localhost/mem:TestDatabaseName;

6. How to enable Sql Server Mode?

Append the following option in the connection string.
MODE=MSSQLServer; Now the url will look like
jdbc:h2:tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer

7. How long this database will persist?

Unless you mention DB_CLOSE_DELAY=-1; The database will be destroyed from the in-memory as soon as you disconnect it. In order to access other java programs out of gradle, you must mention close delay option also.
Example: jdbc:h2:tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
To know more about DB_CLOSE_DELAY Close Delay

8. What is "Driver Not Found" error?

As far as I know, there are two reasons.
a. If you miss the H2 jar file or H2 dll. In our case it is h2-1.3.174.jar or h2-1.3.174.dll. Set the right CLASSPATH as mentioned in Question No 4: d.
b. if you give wrong connection string, such as,
tcp://localhost/mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
The above connection string misses "jdbc:h2:" in the front. So it could not find the driver.

9. What is the driver for H2?

"org.h2.Driver"
in .net you should use it as
org.h2.Driver.load();

10. How JVM deamon behaves for H2?

Since H2 is an in-memory database, the H2 database is per JVM deamon instance. So the database created by one JVM deamon cannot be accessed by the other one. To access a database by all the JVM deamons we must use "server mode" (tcp) option . Within gradle it uses only one JVM deamon. But when we access data generator tool, the data generator will create a new JVM deamon instance. Then data generator tool will search for a database within it's own JVM deamon process, if it doesn't find the database already, it creates a new database on it's own.

11. How do I verify if the same database is used by all the JVM deamons?

If you are using non-tcp connections, this problem may arise.
Create a database without IFEXISTS=TRUE option
jdbc:h2:mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;
Add tables in it.
Now in the another process, which access the same database, use IFEXISTS=TRUE option in the connection string. Now you shouldn't throw "database not found" error.
jdbc:h2:mem:TestDatbaseName;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;IFEXISTS=TRUE;
To know more about it Exists

12. Are there any difference between in sql query for H2?

Yes, Here is the list of differences H2 - Tips and Tricks

13. What is H2 Sharp? Any example c#.net program to connect H2?

H2 Sharp is a .net API to access H2 from .net. You need IKVM binaries to use H2 Sharp.
H2 Sharp from github has a sample program.
H2 Sharp download h2sharp
IKVM binaries download IKVM binaries
As far as I know, only the above links are in working condition. The H2 Sharp, I downloaded from the google code version didn't work.

14. Exampe gradle file for H2 with flyway.

Example H2 with Gradle and Flyway Program.

Multiline Prelude Functions

Prelude is commandline utility for Haskell. Prelude supports one line functions. Such as,
let addThree a b c = a+b+c

If it crosses more than a line. Such as

addThree :: Int -> Int -> Int -> Int  
addThree x y z = x + y + z 

As soon as you hit enter after the first line, you will see error as

:11:1: Not in scope: `addThree'

Solution 1

Create a haskell file such as "addThree.hs" and place the function in it. Now load the file using ":l addThree.hs" at prelude.

Solution 2

You can also use multiline function in prelude. Now semicolon must be used after each line, shouldn't press ENTER key. Most importantly 'let' keyword must be prefixed. Here is the modified version of the above example.

let addThree :: Int -> Int -> Int -> Int; addThree x y z = x + y + z 

Haskell Error 3

quicksort.hs:6:42:
    Unexpected parallel statement in a list comprehension
    Use -XParallelListComp

quicksort.hs:6:44: Not in scope: `a'

I placed '|' (pipeline) instead of a ',' (comma) to separate predicate section. Program with Error:

quicksort :: (Ord a) => [a] -> [a] 
quicksort [] = []
quicksort(x:xs)=let smallerSorted = quicksort [a | a <- xs, a <= x]
                    biggerSorted = quicksort [a | a <- xs| a > x] 
--But it supposed to be "a <- xs, a > x"
                in smallerSorted ++ [x] ++ biggerSorted

Haskell Error 2

quickSort.hs:4:1:
    parse error (possibly incorrect indentation or mismatched brackets)
quickSort.hs:4:54: parse error on input `='

Two ways to avoid the above error:
    1: Haskell expects right indentation.
    2: Shouldn't use 'Tab Spacing' for indentation. We must use space bar.

The indentation error occurs only for specific keywords. The 'let' keyword expects the next line to be indented. And then the 'in' keyword should start the same column as 'let' starts.

I don't know why we shouldn't use 'Tab' for indentation. I got the above error because I used the 'Tab Spacing'. Used space bar and solved the problem.
Correct Version:

quicksort :: (Ord a) => [a] -> [a] 
quicksort [] = []
quicksort(x:xs)=let smallerSorted = quicksort [a | a <- xs, a <= x]
                    biggerSorted = quicksort [a | a <- xs, a > x]
                in smallerSorted ++ [x] ++ biggerSorted

Haskell Error 1

Invalid type signature: GetMax :: Ord a => [a] -> a
Should be of form <variable> :: <type>
This is because, the function 'GetMax' starts with capital case, it should be 'getMax'. In haskell function names shouldn't start with capital case, only module names can start with capital case.
Example: getMax :: (Ord a) => [a] -> a

A Date Check - To Narrow Down the Problem

I was facing an issue this week, wrong date of birth was shown for all the patients. When I look at closer, all the patients had same date of birth as "Jan/1/1970". Then I understood that "Jan/1/1970" must be the default date value of .net/sql server. It means somewhere we missed to assign the value for DOB field. Later we figured out, that it was a javascript issue. "Jan/1/1970" is the default date value of javascript. We missed to assign javascript DOB variable. So, as soon as you see "1970" instead of some other value, you can guess that something goes wrong in javascript.

.Net default date value is Jan-01-0001

Sql server default date value is NULL, starts from Jan-01-1753

Oracle default date value is NULL, starts from Jan-01-4712

Javascript default value is Jan-01-1970