Monday, February 17, 2014

Gradle Dependencies Error

src\main\java\com\package\HelloWorldServlet.java:5: error: package javax.servlet does not exist
import javax.servlet.ServletException;
OR
error: cannot find symbol
public class HelloWorldServlet extends HttpServlet {

When we get any one of the above error there must be a problem of missing depencies in gradle. In this case I missed the below line. providedCompile 'javax.servlet:servlet-api:2.5'

The complete code:

dependencies {
   providedCompile 'javax.servlet:servlet-api:2.5' //I missed this line.
}

Saturday, February 1, 2014

Working with H2, Gradle, H2Sharp, IntelliJ Idea and Flyway - Experience

1. In gradle with flyway, the H2 database doesn't work at version 1. It doesn't throw any error, it says migration is successful. But it doesn't create tables in the database. So we should start from the version 2 sql files.
Example: V1__TestDatabaseSchema.sql file doesn't work.
V2__TestDatabaseSchema.sql file works. Version number matters

2. There must be double hyphen in the filename after the version number (V2, V1 and etc).

3. I faced "Connection refused: connect" in IntelliJ.
So I tried various options like enabling IPV4, windows hot fix, installing ISA Client, restarting machine, switched java language level in IntelliJ, disabled firewall and other options mentioned in the internet. Finally I added http proxy in the IntelliJ Idea. That worked. In my office network I have Automatic configuration script enabled.

I posted my solution to the public forum http://stackoverflow.com/questions/12701347/unable-to-import-maven-project-into-intellij-idea/20758527#20758527.


So I copy pasted the proxy settings as below in IntelliJ Idea.
I was unable to download maven project from IntelliJ. When I typed the library names, it didn't give me any result. The above solution fixed this problem too. 4. When connecting H2 database with other tools in file mode, the same database should not be open in Web UI. The reason is, in file mode, multiple JVM deamons cannot access the database at the same time, it throws error. The file will be locked by one JVM deamon process.
Example: jdbc:h2:file:F:\folderName\fileName;

5. The order for running gradle script to migrate H2 via flyway is
flywayClean, flywayInit, flywayMigrate
So when there is any error in the migrate, after making changes re-run from flywayClean. flywayClean drops all tables.

6. The Sql Server Mode is mandatory when connecting to the H2 Web Console. Otherwise the query for sql server won't compile. It will succeed when running gradle scripts, but won't yeild result.

7. There are two version of H2 Sharp available in the internet.
    a. Git https://code.google.com/p/h2sharp/
    b. Google code https://github.com/Remo/h2sharp/tree/master/Dlls
Code downloaded from google code didn't work, I faced compile errors. Only git version worked for me.

8. Be careful about datetime field when using H2+H2Sharp in a .net project. It didn't work for me.

Example Program for H2 with Gradle, Flyway in IntelliJ Idea

This is an example program to work with H2, Gradle, Flyway and IntelliJ Idea. Download the example program H2Test_SampleProgram.zip.

1. To see the Gradle tasks window from IntelliJ Idea, follow the below steps.


2.Here is the order, to run gradle tasks.


3. To see the output, connect the H2 database and run the following queries. The table Schema_Version will be automatically created and updated by flyway.

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