CACHED TABLE

2005-03-20
2014-01-19
  • S.Chekanov (jWork.ORG)

    Hi,

    I have found one feature, and at this moment I do not see how I can fix it. I've tested HSQLDB using "TestDima.java" example from the HSQLDB package. Everything seems is working. Then I divided it to the two programs: one is to write database, and second is to read it. For the first program I've removed everything between "java.sql.ResultSet rs;" and "rs.close();"
    For the second, I removed lines between "java.sql.Statement stat = conn.createStatement();"  and "java.sql.ResultSet rs;"
    So, the first program is just to write the table on the disk,
    and second is to read it from the disk. I also modified
    connection to  "Connection conn = DriverManager.getConnection( "jdbc:hsqldb:file:test", "sa", "");

    So, I've compiled and run first program. The database "test.data" was created, everyhing was ok. Then I run second program - and it seems it cannot read any values in these tables. I've checked - the tables " BAZ" "BAR" and "FOO"
    do exist in the database, but  I cannot read any values, i.e.
    the loop
    while (rs.next()) {
                    System.out.println(rs.getInt(1));
                    System.out.println(rs.getString(2));
                }

    does not print any values...

     
    • Anonymous - 2005-03-20

      Perhaps somewhere a commit does not take place that should have?

       
    • S.Chekanov (jWork.ORG)

      Hi,
      I've checked this again. "TestDima.java" was added to RC9 ("test" directory), it works as example, but it does not work when I divide it to 2 independent programs to write DB and to read it. The commit statement is there. Below I'm giving  2 programs based on  "TestDima.java" - one is to write DB, second  is to read it. When I'm trying to read the created DB , it does find the table, but I cannot get any number from it:

      public class TestDimaWrite  {

          public static void main(String[] args) {

              try {
                  Class.forName("org.hsqldb.jdbcDriver");

                  java.sql.Connection conn = java.sql.DriverManager.getConnection(
                      "jdbc:hsqldb:file:test", "sa", "");

                  conn.setAutoCommit(false);

                  java.sql.Statement stat = conn.createStatement();

                  stat.executeUpdate("DROP TABLE BAZ IF EXISTS");
                  stat.executeUpdate("DROP TABLE BAR IF EXISTS");
                  stat.executeUpdate("DROP TABLE FOO IF EXISTS");
                  conn.commit();
                  stat.executeUpdate("CHECKPOINT");
                  stat.executeUpdate(
                      "CREATE CACHED TABLE FOO (ID INTEGER IDENTITY PRIMARY KEY, VAL VARCHAR(80))");
                  stat.executeUpdate(
                      "CREATE TABLE BAR (ID INTEGER IDENTITY PRIMARY KEY, FOOID INTEGER NOT NULL, "
                      + "VAL VARCHAR(80), FOREIGN KEY(FOOID) REFERENCES FOO(ID) ON DELETE CASCADE)");
                  stat.executeUpdate(
                      "CREATE TABLE BAZ (ID INTEGER IDENTITY PRIMARY KEY, BARID INTEGER NOT NULL, "
                      + "VAL VARCHAR(80), FOREIGN KEY(BARID) REFERENCES BAR(ID) ON DELETE CASCADE)");
                  stat.executeUpdate("CHECKPOINT");
                  stat.executeUpdate("INSERT INTO FOO (VAL) VALUES ('foo 1')");
                  stat.executeUpdate(
                      "INSERT INTO BAR (FOOID,VAL) VALUES (IDENTITY(),'bar 1')");
                  stat.executeUpdate(
                      "INSERT INTO BAZ (BARID,VAL) VALUES (IDENTITY(),'baz 1')");
                  stat.executeUpdate("INSERT INTO FOO (VAL) VALUES ('foo 2')");
                  stat.executeUpdate(
                      "INSERT INTO BAR (FOOID,VAL) VALUES (IDENTITY(),'bar 2')");
                  stat.executeUpdate(
                      "INSERT INTO BAZ (BARID,VAL) VALUES (IDENTITY(),'baz 2')");
                  stat.executeUpdate("INSERT INTO FOO (VAL) VALUES ('foo 3')");
                  stat.executeUpdate(
                      "INSERT INTO BAR (FOOID,VAL) VALUES (IDENTITY(),'bar 3')");
                  stat.executeUpdate(
                      "INSERT INTO BAZ (BARID,VAL) VALUES (IDENTITY(),'baz 3')");

                  conn.commit();
                  stat.close();
                  conn.close();
                  conn.close();
              } catch (Exception e) {
                  System.err.println(e.getMessage());
                  e.printStackTrace();
              }
          }
      }    //*********** eof Test ********************************************************
      public class TestDimaRead {

          public static void main(String[] args) {

              try {
                  Class.forName("org.hsqldb.jdbcDriver");
                  java.sql.Connection conn = java.sql.DriverManager.getConnection(
                      "jdbc:hsqldb:file:test", "sa", "");

                  conn.setAutoCommit(false);

                  java.sql.Statement stat = conn.createStatement();

                  java.sql.ResultSet rs;
                  java.sql.Statement query = conn.createStatement(
                      java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
                      java.sql.ResultSet.CONCUR_READ_ONLY);

                  rs = query.executeQuery("SELECT ID,VAL FROM FOO");

                  System.out.println("Table FOO:");

                  while (rs.next()) {
                      System.out.println(rs.getInt(1));
                      System.out.println(rs.getString(2));
                  }

                  rs = query.executeQuery("SELECT ID,FOOID,VAL FROM BAR");

                  System.out.println("Table BAR:");

                  while (rs.next()) {
                      System.out.println(rs.getInt(1));
                      System.out.println(rs.getInt(2));
                      System.out.println(rs.getString(3));
                  }

                  rs = query.executeQuery("SELECT ID,BARID,VAL FROM BAZ");

                  System.out.println("Table BAZ:");

                  System.out.println("Table BAZ:");

                  while (rs.next()) {
                      System.out.println(rs.getInt(1));
                      System.out.println(rs.getInt(2));
                      System.out.println(rs.getString(3));
                  }

                  rs.close();
                  query.close();
                  stat.close();
                  conn.close();
              } catch (Exception e) {
                  System.err.println(e.getMessage());
                  e.printStackTrace();
              }
          }
      }    //*********** eof Test ********************************************************

       
      • Fred Toussi

        Fred Toussi - 2005-03-26

        As you are using the file: protocol to connect, you should shutdown the database in the first program. Otherwise, changes are written only at the WRITE_DELAY interval which is 60 seconds by default. You can change this value to a lower setting. In 1.8.0, if you SET WRITE_DELAY 0 then each commit will persist the data.

         

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks