Incorporating H2 database into Eclipse

So I finally did this.

Here's a decent explanation.

https://ibytecode.com/blog/eclipse-dtp-configure-h2-datasource-using-data-source-explorer/

but it goes wrong at the end.

(This is for Eclipse Oxygen; Eclipse Mars can't even do this, on my machine at home; Eclipse Luna doesn't do it either. This is curious, it doesn't feel like a new thing.)


Here's much the same thing over again:

Menubar File->New->Other...

Good god. My pasted-in pictures all got lost. Yeesh. Gotta do them over, saved as files. Yeesh.

Choose "Connection Profile" and "Next"

See this: and click Generic JDBC

Type "H2 JDBC" for the name. (When you try to do this again, you can't use the same name, you'll get told it exists). Click "NEXT", and get a new dialog window.

Click on the strange icon inside the red circle:

See this new dialog:

Click on Generic Driver. Enter H2 JDBC Driver for the name.


If you don't click on Generic, you can't do the next thing:

which is click on "Add JAR". Find your H2 jar file.



Now comes the absurd part. Click on the H2 jar file name. Click on Properties tab button. It's blank. Go back to JAR list. Click the jar file. Click Properties. Still blank. Back to JAR list. Click the H2 file name.

The Edit button will now be active. Click that, the click Cancel in the file dialog. Now click Properties. Yay! props table.

Driver class is org.h2.Driver (capitalized just that way). URL is jdbc:h2:tcp://localhost/wherever-your-db-is. Name is whatever you want as a name. I'm not sure what this is for. Click OK.

enter a password, click "save", click "test connection" to be sure you don't have a typo somewhere.

The tab for "Optional" parameters would be something like mvstore=false; or other jazz.

I don't recommend turning on "connect every time". Regrettably, you'll have to go through this again every time you have a new database, but the end result here is that you can create a .sql file in a project, and connect it to this database, and run sql live right there from in the buffer.



I should have done this years ago...but Eclipse is not really the best of all possible interfaces to H2. Especially since it won't show the result from "SELECT * FROM my_table;", just whether or not it executed error-free.

I have some other tools I wrote for real database interface work, but they don't do text-edit buffer.

--------------------------------

Of course, H2 already has to have that database created...I was trying to get a CSV dumped into a single table to practice some SQL with. Just the load was trickier than I wanted.

How to load a table from CSV into H2:

(Yep, I'm looking at some FCC data.)

H2 doesn't give a very good explanation for CSVREAD to load a DB. Tried it before, and spent an hour today trying to guess what it wanted for syntax. It's actually really simple, simpler than it looks in their doc.

INSERT INTO table(col, col,col...) SELECT col,col,col... FROM csvread("file-location.csv");

There's a simpler way if your table will have exactly the same columns as the CSV, but mine had that extra "ID" column first.