SQL error trying to change status from draft

[gcampanile]

If you try to change a status of a metadata record from Draft to something else (Approved, Incomplete,...) an error is raised: ERROR: syntax error at or near ")" Position: 52 The problem is in class ImsMetadataAdminDao.java in function updateApprovalStatus: at line 1140 there's the following statement: uuids.clear but then uuids.size() is used in the SELECT statement. I'm not sure but I think that the statement should go just before the following lines:

        while (rs2.next()) {
          uuids.add(rs2.getString(1));

Hereafter the modified function:

public int updateApprovalStatus(Publisher publisher, 
                                StringSet uuids, 
                                MmdEnums.ApprovalStatus approvalStatus)
  throws SQLException, CatalogIndexException {

  // update the database approval status
  PreparedStatement st = null;
  int nRows = 0;
  this.hadUnalteredDraftDocuments = false;
  try {
    if (!uuids.isEmpty()) {   

      // determine if the set contains documents in 'draft' mode
      Connection con = returnConnection().getJdbcConnection();
      StringBuffer sbSql = new StringBuffer();
      sbSql.append("SELECT DOCUUID FROM ").append(getResourceTableName());
      sbSql.append(" WHERE DOCUUID IN (").append(generateQMarks(uuids.size())).append(")");
      sbSql.append(" AND APPROVALSTATUS = ?"); 
      logExpression(sbSql.toString());
      st = con.prepareStatement(sbSql.toString());
      int argIndex = 0;
      for (String uuid: uuids) {
        if (!isUuid(uuid)) {
            throw new SQLException("Invalid UUID.");
        }
        st.setString(++argIndex, uuid);
      }
      st.setString(++argIndex,MmdEnums.ApprovalStatus.draft.toString());
      ResultSet rs = st.executeQuery();
      if (rs.next()) {
        this.hadUnalteredDraftDocuments = true;
      }
      closeStatement(st);

      // execute the update, don't update documents in 'draft' mode
      sbSql = new StringBuffer();     
      sbSql.append("UPDATE ").append(getResourceTableName());
      sbSql.append(" SET APPROVALSTATUS=?");
      sbSql.append(" WHERE DOCUUID IN (").append(generateQMarks(uuids.size())).append(")");
      sbSql.append(" AND (APPROVALSTATUS IS NULL OR APPROVALSTATUS <> ?)");
      logExpression(sbSql.toString());
      st = con.prepareStatement(sbSql.toString());
      argIndex = 0;
      st.setString(++argIndex,approvalStatus.toString());
      for (String uuid: uuids) {
        if (!isUuid(uuid)) {
            throw new SQLException("Invalid UUID.");
        }
        st.setString(++argIndex, uuid);
      }
      st.setString(++argIndex,MmdEnums.ApprovalStatus.draft.toString());
      nRows = st.executeUpdate();

      // re-build the index uuid set if 'draft' documents were not updated
      if (this.hadUnalteredDraftDocuments || (nRows != uuids.size())) {
        closeStatement(st);
        // THIS SHOULD NOT BE HERE uuids.clear();
        sbSql = new StringBuffer();
        sbSql.append("SELECT DOCUUID FROM ").append(getResourceTableName());
        sbSql.append(" WHERE DOCUUID IN (").append(generateQMarks(uuids.size())).append(")");
        sbSql.append(" AND (APPROVALSTATUS IS NULL OR APPROVALSTATUS <> ?)"); 
        logExpression(sbSql.toString());
        st = con.prepareStatement(sbSql.toString());
        argIndex = 0;
        for (String uuid: uuids) {
          if (!isUuid(uuid)) {
              throw new SQLException("Invalid UUID.");
          }
          st.setString(++argIndex, uuid);
        }
        st.setString(++argIndex,MmdEnums.ApprovalStatus.draft.toString());
        ResultSet rs2 = st.executeQuery();
    // MOVED HERE
    uuids.clear();
        while (rs2.next()) {
          uuids.add(rs2.getString(1));
        }
      }
    }
  } finally {
    closeStatement(st);
  }

[zguo]

Thanks! have applied the change to source code.