The Terrible H2 Workflow

This is my frustrating experience of working with h2 in IDEA and is in someways a follow up to my post from 2015

https://intellij-support.jetbrains.com/hc/en-us/community/posts/206806775-Why-I-Don-t-Use-The-Database-Editor-And-How-I-USe-My-Chosen-Editor

Decide to add h2 to your project

Go to build.gradle and alt-insert "Add maven artifact dependency"

    - Nothing shows up because the local maven repo is not indexed

Go to https://mvnrepository.com/artifact/com.h2database/h2 to copy the import statement instead (change to compile scope)

compile group: 'com.h2database', name: 'h2', version: '1.4.197'

Add a new embedded (file based) h2 data source in the Database Tool Window

jdbc:h2:file:./test

Where are the files??

Recheck url

Search online

Search hard drive

Eventually find them in the IDEA directory instead of the project directory (the fix is for IDEA to set the working dir to the project dir)

opt/idea-IU-182.3569.1/bin/test.h2.db

Import a CSV with first column as primary key.

Fails with

[HYC00][50100] Feature not supported: "Index on BLOB or CLOB column:

Try without a primary key and it succeeds.

Search around a bit and realise that it is because IDEA is using TEXT instead of VARCHAR.

Try to test out some SQL 

call TRANSLATE('Hello world', 'eo', 'EO')

Function not found.

Search around, realise that IDEA is using a different driver to what I just added to the project

.m2/repository/com/h2database/h2/1.3.175/h2-1.3.175.jar

Post on the forum and get annoyed with the editor adding <p> instead of <br/>!