HSQLDB: a tábla nem marad meg

Üdv!

Elkezdtem egy hsqldb-s progit írni, de hamar belefutottam egy problémába. A program leteszteli, hogy létezik-e 2 tábla (egyszerűen futtat egy select-et). Ha nem, akkor létrehozza őket, és ismét teszteli (az utóbbit csak ideiglenesen). A probléma az, hogy indítás után megint nincs meg a tábla, de a második teszt mindig jelzi, hogy létrejött. Nem TEMP tábla, próbáltam MEMORY és CACHED módban is létrehozni, az sem segített rajta.

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

	Connection conn = null;

	public Main() {
		super();
	}

	public void run() {
		try {
			Runtime.getRuntime().addShutdownHook(new ShutdownThread());

			Class.forName("org.hsqldb.jdbcDriver");
			conn = DriverManager.getConnection("jdbc:hsqldb:file:./database.db", "sa", "");
			conn.setAutoCommit(false);

			System.out.println("Connection Succeeded, testing database...");

			String[][] tests = new String[][] { 
					{ "currencies_sell", "SELECT curid, currency, time, value FROM currencies_sell;", // TOP 0 vagy LIMIT 0 esetén: Unexpected token: 0 in statement [0]
						"CREATE MEMORY TABLE currencies_sell " + 
							"( curid INTEGER IDENTITY, currency CHAR(3), time DATETIME, value INTEGER );" },
					{ "currencies_buy", "SELECT curid, currency, time, value FROM currencies_buy TOP 0;", 
						"CREATE CACHED TABLE currencies_buy " + 
							"( curid INTEGER IDENTITY, currency CHAR(3), time DATETIME, value INTEGER );" }
					};

			Statement stmt = null;
			ResultSet rs = null;

			for (int i = 0; i < tests.length; i++) {
				try {
					System.out.println("Testing table: " + tests[i][0]);
					stmt = conn.createStatement();
					rs = stmt.executeQuery(tests[i][1]);
					System.out.println("OK");
				} catch (SQLException e) {
					System.out.println("Table not found: " + tests[i][0] + ". Creating...");
					e.printStackTrace();

					Statement create = null;
					try {
						create = conn.createStatement();
						create.executeUpdate(tests[i][2]);
						System.out.println(tests[i][2]);
						System.out.println(tests[i][0] + " created.");
					} catch (SQLException ex) {
						System.out.println("Cannot create table. Exiting...");
						return;
					} finally {
						if (create != null)
							try {
								create.close();
							} catch (SQLException exx) {
							}
					}
				} finally {
					if (rs != null)
						try {
							stmt.close();
						} catch (SQLException e) {
						}
					if (stmt != null)
						try {
							stmt.close();
						} catch (SQLException e) {
						}
				}
			}
			
			for (int i = 0; i < tests.length; i++) {
				try {
					System.out.print("Testing table: " + tests[i][0] + ": ");
					stmt = conn.createStatement();
					rs = stmt.executeQuery(tests[i][1]);
					System.out.println("OK");
				} catch (SQLException e) {
					System.out.println("Table not found: " + tests[i][0] + ". Exception: ");
					e.printStackTrace();
				} finally {
					if (rs != null)
						try {
							stmt.close();
						} catch (SQLException e) {
						}
					if (stmt != null)
						try {
							stmt.close();
						} catch (SQLException e) {
						}
				}
			}

			System.out.println("DB check complete.");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

		}
	}

	public static void main(String[] args) {
		new Main().run();
	}

	private class ShutdownThread extends Thread {
		@Override
		public void run() {
			System.out.println("Shutting down...");
			if (conn != null)
				try {
					conn.close();
					System.out.println("Database connection closed");
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
}

Kimenet:

Connection Succeeded, testing database...
Testing table: currencies_sell
Table not found: currencies_sell. Creating...
java.sql.SQLException: Table not found in statement [SELECT curid, currency, time, value FROM currencies_sell]
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
	at test.Main.run(Main.java:43)
	at test.Main.main(Main.java:113)
CREATE MEMORY TABLE currencies_sell ( curid INTEGER IDENTITY, currency CHAR(3), time DATETIME, value INTEGER );
currencies_sell created.
Testing table: currencies_buy
Table not found: currencies_buy. Creating...
java.sql.SQLException: Table not found in statement [SELECT curid, currency, time, value FROM currencies_buy]
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
	at test.Main.run(Main.java:43)
	at test.Main.main(Main.java:113)
CREATE CACHED TABLE currencies_buy ( curid INTEGER IDENTITY, currency CHAR(3), time DATETIME, value INTEGER );
currencies_buy created.
Testing table: currencies_sell: OK
Testing table: currencies_buy: Table not found: currencies_buy. Exception: 
DB check complete.
Shutting down...
Database connection closed
java.sql.SQLException: Unexpected token: 0 in statement [0]
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
	at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
	at test.Main.run(Main.java:83)
	at test.Main.main(Main.java:113)

Ami még érdekes, hogy TOP 0 sem tetszik neki, pedig elméletileg oda lehetne rakni. Viszont azt nem értem, hogy ez mért csak a második tesztnél okoz neki problémát. Ha syntax hiba, mért nem jelzi az elsőnél (ki sem írja az első hibaüzenetben a TOP-ot.

hsqldb.org-ról elérhető 1.8.0_10-es verziót használom.