How to migrate from hsqldb to h2

Recently from a udig IRC meeting I got a real appetite to try out the H2 database, since the google summer of code this year will bring the geotools module to have spatial indexing...

Well, in JGrass we had based all out-of-the-box database needs (non spatial) on Hsqldb, so I wanted to try the substitution. Well, since they are all based on jdbc, the thing has been fairly easy. Some interface extracted, some new plugin, and everything was fine. Not much to say about that.

But can you note something from the code below?

// start the server
String[] args = {"-tcp", "-tcpPort", String.valueOf(_port)};
tcpServer = Server.createTcpServer(args).start();
args = new String[]{"-web", "-webPort", String.valueOf(_port + 1)};
webServer = Server.createWebServer(args).start();

// connect
Class.forName(DRIVER);
url = "jdbc:h2:tcp://localhost:" + _port + "/" + database;
con = DriverManager.getConnection(url, user, _passwd);

With hsqldb I had a problem with not beeing able to access the database while JGrass being connected to it (which is nasty because of much development time lost). Also when bringing the application to client we usually had to ship also a db client like Squirrelsql to give a way to access the database.

H2 comes with a webserver instance with some nice application to browse the database tables and do queries:



Once JGrass is started I can access through the webbrowser my machine on the port I defined and I get the access login you can see in the picture above in the browser behind JGrass. JGrass in that case backs up the content of the annotationslayer in the H2 database booted together with JGrass.

Once entered, I get to see the following:


That is a great thing for keeping things under control while developing.
I think I will stay on H2 for a little while :)

How to deal with timezones in hsqldb - no gap filled yet?

Working with timeseries of environmental data, there is a big need to have the daylight saving switched OFF, else the unique dates will appear more than once :)

For now, with hsqldb and java there is only one way I found to deal with that, i.e. setting another timezone.

In the Italian Job case, Africa answers the call for help:
-Duser.timezone="Africa/Kinshasa"
saves us by giving the same zone, but without the daylight saving.

Thank God there is Kinshasa! ;)

How to "serialize to" and "retrieve from" objects from hsqldb

Assuming ChartData implements the serializable interface:

     String insertGraph ="INSERT INTO GRAFICI_BLOB (ID, DATA) VALUES (##, ?)"

    ChartData chartData = chartsPage.getChartData();
     // Serialize to a byte array
     ByteArrayOutputStream bos = new ByteArrayOutputStream();
     try {
         ObjectOutputStream out = new ObjectOutputStream(bos);
         out.writeObject(chartData);
         out.close();
     } catch (IOException e2) {
         e2.printStackTrace();
     }
     byte[] chartDataBytes = bos.toByteArray();

     PreparedStatement str1 = null;
     try {
         str1 = con.prepareStatement(insertGraph);
         str1.setBytes(1, chartDataBytes);
         str1.executeUpdate();
     } catch (Exception ex) {
         ex.printStackTrace();
     } finally {
         try {
             if (null != str1)
                 str1.close();
         } catch (Exception ex) {
         }

     }

/*
* and the way back
*/
          Statement stmt = con.createStatement();
          String sql = the query to select your blob field... (in my case GRAFICO)

          ResultSet res = stmt.executeQuery(sql);
          byte[] bytes = res0.getBytes("GRAFICO");
              // Deserialize from byte array
              try {
                  ObjectInputStream in = new ObjectInputStream(new ByteArrayInputStream(bytes));
                  runProperties.chartData = (ChartData) in.readObject();
                  in.close();
              } catch (IOException e) {
                  e.printStackTrace();
              } catch (ClassNotFoundException e) {
                  e.printStackTrace();
              }

How to insert and retrieve files in hsqldb

How to save files into hsqldb? How to restore them back?

     String calibrationZip = "calibrazione.zip";
     String newcalibrationZip = "new_calibrazione.zip";
      String insertStr = "INSERT INTO CALIBRAZIONE_BLOB (ID, DATA) VALUES (##, ?)"
     String sql = "select data as DATA from CALIBRAZIONE_BLOB where id = ##"
      int runId = 1;


     /*
      * insert into blob
      */
     FileInputStream fis = null;
     PreparedStatement st = null;
     try {
         File fl = new File(calibrationZip);
         fis = new FileInputStream(fl);
         insertStr = insertStr.replaceFirst("##", String.valueOf(runId));
         st = con.prepareStatement(insertStr);
         st.setBinaryStream(1, fis, (int) fl.length());
         st.executeUpdate();
     } catch (Exception ex) {
         System.out.println(ex);
     } finally {
         try {
             if (null != st)
                 st.close();
         } catch (Exception ex) {
         }
         try {
             if (null != fis)
                 fis.close();
         } catch (Exception ex) {
         }
     }

     /*
      * extract the blob
      */
     sql = sql.replaceFirst("##", String.valueOf(runId));
     try {
         Statement statement = con.createStatement();
         ResultSet rs = statement.executeQuery(sql);
         if (rs.next()) {
             InputStream is = rs.getBinaryStream("DATA");
             FileOutputStream fos = new FileOutputStream(newcalibrationZip);
             byte[] buff = new byte[8192];
             int len;
             while( 0 < (len = is.read(buff)) )
                 fos.write(buff, 0, len);
             fos.close();
             is.close();
         }
         rs.close();
         statement.close();
     } catch (FileNotFoundException e) {
         e.printStackTrace();
     } catch (SQLException e) {
         e.printStackTrace();
     } catch (IOException e) {
         e.printStackTrace();
     }