Unable to perform table update

  • dfermin315

    dfermin315 - 2013-06-15


    I have a table that I am trying to perform an update on. Here is the table structure:

      groupId INT,
      pepId INT,
      wt DOUBLE DEFAULT -1

    Here is the JAVA code I am using to perform the update. The update is based upon the pepId and groupId fields in pepwts table. The default  value of the 'wt' field is -1 and is there for error checking. After the update finishes, this value should range from 0 to 1.

    Statement stmt = conn.createStatement();
    Statement stmt2 = conn.createStatement();
    ResultSet rs = null;
    rs = stmt.executeQuery("SELECT pepId, COUNT(DISTINCT groupId) FROM pepwts GROUP BY pepId");
    while(rs.next()) {
      int pepIdx = rs.getInt(1);
      double wt = 1.0 / ( (double) rs.getInt(2) );
      String query = "UPDATE pepwts SET wt = " + wt + "  WHERE pepId = " + pepIdx + "  ";

    This code executes just fine except when I load the HSQL Database Manager and view the pepwts table, the 'wt' field is still set to -1 for some case. Back in JAVA, if I print out the value of the 'wt' variable for those cases, the result is what I expect. It appears that HSQL fails to perform the update for some pepId values.

    Any and all suggestions for how to fix this are welcome.
    I am using HSQL 2.2.9 if that helps.

    Thanks in advance.

  • Fred Toussi

    Fred Toussi - 2013-06-16

    You can do this with an SQL statemant:


    UPDATE pepwts p1 SET wt  = SELECT COUNT(DISTINCT groupId) FROM pepwts p2 WHERE p2.pepId = p1.pepId

  • dfermin315

    dfermin315 - 2013-06-16

    Thanks for the reply.
    I can see that your suggested query will work.
    But I'm more worried about why my code doesn't work.
    I'm using HSQL inside another JAVA program hosted here on Sourceforge and I'm worried that other database calls I'm making are going wrong and don't know it.

    Is there an HSQL database setting I should use to ensure table stability?
    I assume the default transaction method would be okay. 

  • Fred Toussi

    Fred Toussi - 2013-06-17

    This usage is not recommended. In general do not keep a result set open on a table while you are updating it with a different statement.

  • dfermin315

    dfermin315 - 2013-06-17

    Thanks for the info. I will avoid acting on a table with an open result set.
    In my attempts to deal with this issue I tried a different approach and was suprised by the results. Here is the function code I tried:

        void calcPepWtsForCombinedTbl(Connection conn, Object object) throws SQLException {
            String query;
            Statement stmt = conn.createStatement();
            query = "CREATE TABLE tmp1_ (pepIdx, freq) AS ( "
                  + "SELECT pepIdx, COUNT(DISTINCT groupId) as freq "
                  + "FROM grps2peps_ "
                  + "GROUP BY pepIdx "
                  + ") WITH DATA ";
                    stmt.executeUpdate("CREATE INDEX tmp1_idx ON t1_(pepIdx)");
                    System.err.print("CalcPepWtsForCombinedTbl().... DONE!\n");

    My JAVA code compiles with out problem and the debugging code is printed to the screen. However, the table tmp1_ does not get created in the database.

    The HSQL *.data file that is created is 128MB.
    I'm wondering if there is a file limit to HSQL that I'm missing somewhere and that's why I can't create the table.  If so, is there a way to remove the limit?

    Thanks again for your help

  • Fred Toussi

    Fred Toussi - 2013-06-17

    Probably you are not shutting down your database when your program completes its work.

  • dfermin315

    dfermin315 - 2013-06-17

    Thanks Fred. That was the problem.
    After shutting down the database everything now works correctly.


Log in to post a comment.

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

Sign up for the SourceForge newsletter:

No, thanks