Changeset 9858

Timestamp:

2005-05-12T14:52:45+12:00 (19 years ago)

Author:

kjdon

Message:

OK, changed my mind about making SQLConnection kill off the previous statement.
To make it more transparent what is happening, you now have to create a Statement (connection.createStatement()), then use the Statement to execute the query. This means that the thing doing the query owns the Statement, and can kill it off when finished with it, and nothing else can kill it off unexpectedly. The previous way this was all implemented meant that there was a large memory leak, and some functionality actually relied on this. A newer version of the mysql connector/J has fixed the bug where the statement wasn't closed on garbage collection, but it still seems better to close it explicitly.
Hopefully I have got it all back to working as well as it was bfore, and haven't introduced any bugs :-)

Location:

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3

Files:

• gs3build/CollectionManager.java (modified) (3 diffs)
• gs3build/classifier/AZListClassifier.java (modified) (11 diffs)
• gs3build/classifier/AbstractHierarchyNode.java (modified) (9 diffs)
• gs3build/database/GS3SQLConnection.java (modified) (2 diffs)
• gs3build/database/SQLConnection.java (modified) (5 diffs)
• gs3build/doctypes/AbstractDocument.java (modified) (5 diffs)
• gs3build/doctypes/DocumentFactory.java (modified) (2 diffs)
• gs3build/doctypes/DocumentList.java (modified) (12 diffs)
• gs3build/doctypes/DocumentSQLWriter.java (modified) (4 diffs)
• gs3build/doctypes/HTMLDocument.java (modified) (1 diff)
• gs3build/metadata/METSDescriptive.java (modified) (7 diffs)
• gs3build/metadata/METSDescriptiveSet.java (modified) (2 diffs)
• gs3build/metadata/METSDivision.java (modified) (8 diffs)
• gs3build/metadata/METSFile.java (modified) (6 diffs)
• gs3build/metadata/METSFileGroup.java (modified) (10 diffs)
• gs3build/metadata/METSFileSet.java (modified) (5 diffs)
• gs3build/metadata/METSNamespace.java (modified) (5 diffs)
• gs3build/metadata/METSStructure.java (modified) (7 diffs)
• gs3build/metadata/METSStructureSet.java (modified) (2 diffs)
• gs3build/metadata/SimpleNamespace.java (modified) (4 diffs)
• gs3build/notifier/NotifierManager.java (modified) (3 diffs)
• util/SQLQuery.java (modified) (19 diffs)

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/CollectionManager.java

@@ -216 +216 @@
       GS3SQLSelect select = new GS3SQLSelect("build");
       select.addField("*");
-      this.database.execute(select.toString());
-      ResultSet results = this.database.getResultSet();
-      if (results != null && 
-      results.first()) {
+      Statement statement = this.database.createStatement();
+      ResultSet results = statement.executeQuery(select.toString());
+      if (results.first()) {
     System.out.println("Reading all keys");
     do {
@@ -238 +237 @@
     } while (results.next());
       }
+      statement.close();
     }
     catch (SQLException ex)
@@ -479 +479 @@
     
     // Update build date information
-    GS3SQLDelete remove = new GS3SQLDelete("build");
-    //    GS3SQLWhere where = new GS3SQLWhere(new GS3SQLWhereItem("buildKey", "=", "NextSeqNo"));
-    //    rem
-    this.database.execute(remove.toString());
-    
-    GS3SQLInsert insert = new GS3SQLInsert("build");
-    insert.addValue("buildKey", "NextSeqNo");
-    insert.addValue("buildValue", Integer.toString(this.buildDocNo));
-    this.database.execute(insert.toString());
-    
-    insert = new GS3SQLInsert("build");
-    insert.addValue("buildKey", "lastBuildDate");
-    insert.addValue("buildValue", getDateString(this.lastBuildDate));
-    this.database.execute(insert.toString());
-
+    try {
+      GS3SQLDelete remove = new GS3SQLDelete("build");
+      //    GS3SQLWhere where = new GS3SQLWhere(new GS3SQLWhereItem("buildKey", "=", "NextSeqNo"));
+      //    rem
+      Statement statement = this.database.createStatement();
+      statement.execute(remove.toString());
+      
+      GS3SQLInsert insert = new GS3SQLInsert("build");
+      insert.addValue("buildKey", "NextSeqNo");
+      insert.addValue("buildValue", Integer.toString(this.buildDocNo));
+      statement.execute(insert.toString());
+    
+      insert = new GS3SQLInsert("build");
+      insert.addValue("buildKey", "lastBuildDate");
+      insert.addValue("buildValue", getDateString(this.lastBuildDate));
+      statement.execute(insert.toString());
+      statement.close();
+    } catch (SQLException e) {
+    System.err.println("CollectionManager.endBuild(): Can't update build information: "+e);
+    }
+    
     // Do tail of build output
     Date startDate = this.lastBuildDate.getTime();

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/classifier/AZListClassifier.java

@@ -9 +9 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 
 import org.xml.sax.XMLReader;
@@ -189 +190 @@
     select.setWhere(where);
 
-    connection.execute(select.toString());
 
     try {
-      ResultSet results = connection.getResultSet();
-      if (results != null && results.first()) {
+      Statement statement = connection.createStatement();
+      ResultSet results = statement.executeQuery(select.toString());
+      if (results.first()) {
     GS3SQLUpdate update = new GS3SQLUpdate("classifiers");
     update.setWhere(where);
@@ -206 +207 @@
 
     action = insert;
+    classifyRef = -1;
       }
       action.addValue("ClassifyID", label);
@@ -213 +215 @@
       action.addValue("NumLeafDocs", Integer.toString(noOfLeafDocs), GS3SQLField.INTEGER_TYPE);
 
-      connection.execute(action.toString());
-      classifyRef = -1;
-    }
-    catch (SQLException sqlEx) {
-      System.err.println(sqlEx);
-      return -1;
-    }
-
-    // get the ClassifyRef if we don't already have it (have done a
-    // insert action above)...
-    if (classifyRef == -1) {
-      connection.execute(select.toString());
-      
-      try {
-    ResultSet results = connection.getResultSet();
-    if (results == null || !results.first()) {
+      // do the update/insert
+      statement.execute(action.toString());
+
+
+      // get the ClassifyRef if we don't already have it (have done a
+      // insert action above)...
+      if (classifyRef == -1) {
+    results = statement.executeQuery(select.toString());
+    if (!results.first()) {
       return -1;
     }
@@ -234 +229 @@
     classifyRef = results.getInt("ClassifyRef");
       }
-      catch (SQLException sqlEx) {
-    System.err.println(sqlEx);
+      
+      statement.close();
+    }  catch (SQLException sqlEx) {
+    System.err.println("AZListClassifier.writeSQLClassifyNode(): "+sqlEx);
     return -1;
-      }
-    }
+    }
+  
 
     return classifyRef;
@@ -274 +271 @@
     }
 
+    try {
+    Statement statement = connection.createStatement();
+    
     List children;
     
@@ -292 +292 @@
         Iterator iterator = childDocs.iterator();
     int childOrder = 1;
+    //St
     while (iterator.hasNext()) {
       AZDocumentItem documentItem = (AZDocumentItem) iterator.next();
@@ -301 +302 @@
       insert.addValue("DocOrder", Integer.toString(childOrder), GS3SQLField.INTEGER_TYPE);    
 
-      connection.execute(insert.toString());
+      statement.execute(insert.toString());
 
       childOrder ++;
@@ -310 +311 @@
     }
 
+   
     /*
     else {
@@ -318 +320 @@
       delete.setWhere(where);
 
-      connection.execute(delete.toString());
+      statement.execute(delete.toString());
     }
 
@@ -331 +333 @@
     }
     */
+    statement.close();
+    } catch (SQLException e) {
+    System.err.println("AZListClassifier.writeSQL(): "+e);
+    return false;
+    }
 
     return true;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/classifier/AbstractHierarchyNode.java

@@ -7 +7 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 
 import org.greenstone.gsdl3.gs3build.doctypes.DocumentID;
@@ -273 +274 @@
     GS3SQLInsert insert;
 
+    Statement statement;
+    // can we connect to the database?
+    try {
+    statement = connection.createStatement();
+    } catch (SQLException e) {
+    System.err.println("AbstractHierarchyNode.writeSQL(): "+e);
+    return false;
+    }
+    
     // Get own full id
     String fullId = this.id.length() > 0 ? this.prefix+"."+this.id : this.prefix;
@@ -283 +293 @@
     select.setWhere(where);
 
-    connection.execute(select.toString());
 
     // update or insert the classifier as required
     try {
-      ResultSet results = connection.getResultSet();
-      if (results != null && results.first()) {
+      ResultSet results = statement.executeQuery(select.toString());
+      if (results.first()) {
     GS3SQLUpdate update = new GS3SQLUpdate("classifiers");
     update.setWhere(where);
@@ -313 +322 @@
 
     action = insert;
+    classifyRef = -1;
       }
       action.addValue("ClassifyID", fullId);
@@ -331 +341 @@
       action.addValue("NumLeafDocs", Integer.toString(this.noOfLeafDocs()), GS3SQLField.INTEGER_TYPE);
 
-      connection.execute(action.toString());
-      classifyRef = -1;
+      statement.execute(action.toString());
     }
     catch (SQLException sqlEx) {
       if (action == null) {
-    System.err.println(sqlEx);
+    System.err.println("AbstractHierarchyNode.writeSQL(): "+sqlEx);
       }
       else {
-    System.err.println(sqlEx + " " + action.toString());
+    System.err.println("AbstractHierarchyNode.writeSQL(): "+sqlEx + " " + action.toString());
       }
       return false;
@@ -347 +356 @@
     // insert action above)...
     if (classifyRef == -1) {
-      connection.execute(select.toString());
-      
       try {
-    ResultSet results = connection.getResultSet();
-    if (results == null || !results.first()) {
-      return false;
+        ResultSet results = statement.executeQuery(select.toString());
+    if (!results.first()) {
+        statement.close();
+        return false;
     }
     
     classifyRef = results.getInt("ClassifyRef");
+    
       }
       catch (SQLException sqlEx) {
-    System.err.println(sqlEx);
+    System.err.println("AbstractHierarchyNode.writeSQL(): "+sqlEx);
     return false;
       }
@@ -368 +377 @@
       GS3SQLDelete delete = new GS3SQLDelete("classdocuments");
       delete.setWhere(where);
-
-      connection.execute(delete.toString());
-    }
-
+      try {
+      statement.execute(delete.toString());
+      } catch (SQLException e) {
+      System.err.println("AbstractHierarchyNode.writeSQL(): "+e);
+      return false;
+      }
+    }
+    
     // post the child nodes...
     Iterator iterator = this.childNodes.iterator();
@@ -379 +392 @@
       if (!childNode.writeSQL(connection)) {
       System.out.println("Failed to write " );
-    return false;
+      return false;
       }
     }
@@ -394 +407 @@
       insert.addValue("DocID", docId.toString());
       insert.addValue("DocOrder", Integer.toString(order), GS3SQLField.INTEGER_TYPE);
-
-      connection.execute(insert.toString());
-
+      try {
+      statement.execute(insert.toString());
+      } catch (SQLException e) {
+      System.err.println("AbstractHierarchyNode.writeSQL(): "+e);
+      return false;
+      }
       order ++;
     }
+    // close the statment
+    try {
+    statement.close();
+    } catch (SQLException e) {
+    System.err.println("AbstractHierarchyNode.writeSQL(): "+e);
+    }
+
     return true;
   }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/database/GS3SQLConnection.java

@@ -65 +65 @@
       
     try {
-      statement = this.connection.createStatement();
+      Statement statement = this.connection.createStatement();
 
       // create build history table
@@ -236 +236 @@
     {
       System.out.println(ex.toString());
-      if (statement != null) {
-      try {
-          statement.close();
-      } catch (Exception e){}
-      statement = null;
-      }
       return false;
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/database/SQLConnection.java

@@ -10 +10 @@
 {
     protected Connection connection;
-    protected Statement  statement;
     protected String database;
     
@@ -24 +23 @@
         this.connection.close();        
         } catch (Exception e) {}
-    }
-    if (this.statement!=null) {
-        try {
-        this.statement.close();     
-        } catch (Exception e) {}
-    }
-    
+    }   
     this.connection = null;   
-    this.statement = null;
-
     }
     
@@ -39 +30 @@
     finalize();
     }
-    
-    public boolean execute(String sql)
-    {
-    try {
-        if (this.statement != null) {
-        this.statement.close();
-        }
-        this.statement = this.connection.createStatement();
-        this.statement.execute(sql);
-    }
-    catch (SQLException ex) {
-        System.out.println(ex);
-        return false;
-    }
-    return true;
-    }
     
-    public void closeStatement() {
-    try {
-        if (this.statement != null) {
-        this.statement.close();
-        } 
-    } catch (Exception e) {}
-        
-    }
-    public Statement createStatement()
+    public Statement createStatement() throws SQLException
     { 
-    try {
-        return this.connection.createStatement();
-    }
-    catch (SQLException ex) {
-        return null;
-    }
-    }
-    
-    public Statement getStatement()
-    { 
-    return this.statement;
-    }
-    
-    public ResultSet getResultSet()
-    { 
-    try {
-        return this.statement.getResultSet();
-    }
-    catch (SQLException ex) {
-        return null;
-    }
+    return this.connection.createStatement();
     }
     
@@ -105 +52 @@
     public boolean dropDatabase(String database) {
     try {
-        this.statement = this.connection.createStatement();
-        this.statement.execute("DROP DATABASE "+database+";");
-        this.statement.close();
+        Statement statement = this.connection.createStatement();
+        statement.execute("DROP DATABASE "+database+";");
+        statement.close();
     }
     catch (SQLException ex){
@@ -120 +67 @@
     try {
         String command = "CREATE DATABASE " + database;
-        this.statement = this.connection.createStatement();
-        this.statement.execute(command);
-        this.statement.close();
+        Statement statement = this.connection.createStatement();
+        statement.execute(command);
+        statement.close();
     } catch (Exception e) {
         System.err.println(e);

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/doctypes/AbstractDocument.java

@@ -9 +9 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 import java.sql.Timestamp;
@@ -190 +191 @@
     // Query for documents using the same file...
     String query = "SELECT DocID FROM files INNER JOIN filegroups ON files.FileGroupRef=filegroups.FileGroupRef WHERE (filegroups.FileGroupId=\"default\" AND files.FileLocation=\"" + this.fileSet.getFile(0).getLocation().toString() + "\")";
-    connection.execute(query);
-
-    List docs = new ArrayList();
-    ResultSet results = connection.getResultSet();
-
     try {
-      if (results != null &&
-      results.first())
-      { do {
-          String value = results.getString("DocID");
-
-      docs.add(value);
-        } while (results.next());
-
-        Iterator docIterator = docs.iterator();
-    while (docIterator.hasNext()) {
-      String docId = docIterator.next().toString();
-      String innerQuery = "SELECT * FROM document WHERE DocID=\"" + docId + "\"";
-      connection.execute(innerQuery);
-      ResultSet innerSet = connection.getResultSet();
-      if (innerSet != null && innerSet.first()) {
-        String docType = innerSet.getString("DocType");
-        if (docType.equals(this.getDocumentType())) {
-          return docId;
+    Statement statement = connection.createStatement();
+    ResultSet results = statement.executeQuery(query);
+
+    List docs = new ArrayList();
+
+    if (results.first()) {
+        do {
+        String value = results.getString("DocID");      
+        docs.add(value);
+        } while (results.next());
+        
+        Iterator docIterator = docs.iterator();
+        while (docIterator.hasNext()) {
+        String docId = docIterator.next().toString();
+        String innerQuery = "SELECT * FROM document WHERE DocID=\"" + docId + "\"";
+        results = statement.executeQuery(innerQuery);
+        if (results.first()) {
+            String docType = results.getString("DocType");
+            if (docType.equals(this.getDocumentType())) {
+            return docId;
+            }
+        }
         }
-      }
     }
-      }
+    statement.close();
     }
     catch (java.sql.SQLException sqlEx) {
-      System.err.println(sqlEx);
+      System.err.println("AbstractDocument.getDuplicateID(): "+sqlEx);
     }
 
@@ -454 +453 @@
       // Use a factory method to create the correct subtype...
       AbstractDocument document = DocumentFactory.createDocument(type, id);
-
       // Append the document date information
       document.indexDate    = sqlResult.getTimestamp("IndexedDate");
@@ -460 +458 @@
       document.modifiedDate = sqlResult.getTimestamp("ModifiedDate");
 
-      // Note that once we have used the connection for something else, our results are closed.
       // Get the individual components of the document
       METSFileSet fileSet = METSFileSet.readSQL(document, connection);
@@ -474 +471 @@
     }
     catch (SQLException sqlEx) {
-      System.out.println("Failure to load document: " + sqlEx);
+      System.err.println("AbstractDocument.readSQL(): Failure to load document: " + sqlEx);
     }
     return null;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/doctypes/DocumentFactory.java

@@ -2 +2 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 import java.net.URL;
@@ -61 +62 @@
   {
     String query = "SELECT * FROM document WHERE DocID=\""+id.toString()+"\";";
-    connection.execute(query);
-
     try {
-      ResultSet results = connection.getResultSet();
-      if (results != null && results.first()) {
-    return AbstractDocument.readSQL(connection, results);
-      }
+    Statement statement = connection.createStatement();
+    ResultSet results = statement.executeQuery(query);
+    
+    DocumentInterface di = null;
+    if (results.first()) {
+        di = AbstractDocument.readSQL(connection, results);
+    }
+    statement.close();
+    return di;
     }
     catch (SQLException sqlEx) {
-      System.err.println(sqlEx);
+      System.err.println("AbstractDocument.readSQLDocument():"+sqlEx);
     }
     return null;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/doctypes/DocumentList.java

@@ -13 +13 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -68 +69 @@
     select.setWhere(where);
 
-    this.connection.execute(select.toString());
-
-    ResultSet results = this.connection.getResultSet();
-    if (results != null) {
-      select = new GS3SQLSelect("filegroups");
-      select.addField("DocID");
-      select.setDistinct(true);
-      
-      where = new GS3SQLWhere();
-      where.setCondition(GS3SQLWhere.OR_CONDITION);
-
-      GS3SQLWhereItem whereItem = null;
-
-      try {
-    results.first();
+    try {
+    Statement statement = connection.createStatement();
+    ResultSet results = statement.executeQuery(select.toString());
+    
+    select = new GS3SQLSelect("filegroups");
+    select.addField("DocID");
+    select.setDistinct(true);
+      
+    where = new GS3SQLWhere();
+    where.setCondition(GS3SQLWhere.OR_CONDITION);
+    
+    GS3SQLWhereItem whereItem = null;
+
+        results.first();
     do {
       int fileGroupRef = results.getInt("FileGroupRef");
@@ -90 +90 @@
     while (results.next());
     select.setWhere(where);
-    results.close();
-    
-    this.connection.execute(select.toString());
-    
-    results = this.connection.getResultSet();
+    
+    results = statement.executeQuery(select.toString());
+    
     results.first();
     do {
@@ -100 +98 @@
       reply.add(docId);
     } while (results.next());
-      } 
-      catch (SQLException sqlEx)
-      { System.err.println(sqlEx);
-      }
-    }
+    statement.close();
+    }
+    catch (SQLException sqlEx) {
+    System.err.println("DocumentList.getDocumentIdsWithFile(): "+sqlEx);
+    }
+    
     return reply;
   }
@@ -173 +172 @@
   }
 
-  private List findDocumentIdsUsingFileQuery(String query)
-  { this.connection.execute(query);
-
-    try {
-
-      ResultSet results = this.connection.getResultSet();
-      if (results == null ||
-      !results.first()) {
-    return null;
-      }
+    private List findDocumentIdsUsingFileQuery(String query) {
+    
+    try {
+    
+    Statement statement = connection.createStatement();
+    ResultSet results = statement.executeQuery(query);
+
+    if (!results.first()) {
+        statement.close();
+        return null;
+    }
       
       // get a list of group ids first and turn it into a query on filegroups
@@ -207 +207 @@
       // structures...recreating new filegroup queries as necessary
       while (queryBuffer.length() > 0) {
-    connection.execute(queryBuffer.toString());
-    
-    results = this.connection.getResultSet();
-    if (results == null || !results.first()) {
-      return null;
+    results = statement.executeQuery(queryBuffer.toString());
+    
+    if (!results.first()) {
+        statement.close();
+        return null;
     }
     
@@ -256 +256 @@
       
       // execute the division query
-      this.connection.execute(queryBuffer.toString());
-      
-      results = this.connection.getResultSet();
-      if (results == null ||
-      !results.first()) {
-    return null;
+      results = statement.executeQuery(queryBuffer.toString());
+      
+      if (!results.first()) {
+      statement.close();
+      return null;
       }
 
@@ -268 +267 @@
     reply.add(results.getString("DocID"));
       } while (results.next());
-
+      
+      statement.close();
       return reply;
     }
     catch (SQLException ex) {
-      System.err.println(ex);
+      System.err.println("DocumentList.findDocumentIdsUsingFileQuery()"+ ex);
     }
     return null;
@@ -454 +454 @@
     select.addField("*");
     
-    ResultSet documents;
     try {
-    connection.execute(select.toString());
-    documents = connection.getResultSet();
-
-      
+    Statement statement = connection.createStatement();
+    ResultSet documents = statement.executeQuery(select.toString());
     if (documents.first()) { 
-        // clone the connection cos we are still usign the old result set
-        GS3SQLConnection cloned_connection = connection.cloneConnection();
-        
         do {
-        DocumentInterface document = AbstractDocument.readSQL(cloned_connection, documents);
+        DocumentInterface document = AbstractDocument.readSQL(connection, documents);
         list.addDocument(document);
         }
         while (documents.next());
-        cloned_connection.close();
-        cloned_connection = null;
-    }
+    }
+    statement.close();
     }
     catch (java.sql.SQLException ex) {
-    System.out.println(ex);
-    ex.printStackTrace();
+    System.out.println("DocumentList.writeSQLDocuments(): "+ex);
     return null;
     }
@@ -494 +486 @@
 {
   private boolean hasNext;
+  private Statement statement;
   private ResultSet resultSet;
   private GS3SQLConnection connection;
@@ -505 +498 @@
 
     try {
-      connection.execute(select.toString());
-      this.resultSet = connection.getResultSet();
+    this.statement = connection.createStatement();
+      this.resultSet = statement.executeQuery(select.toString());
       this.hasNext = this.resultSet.first();
     } catch (SQLException ex) {
+      System.err.println("DocumentListIterator(): "+ex);
       this.hasNext = false;
     }
@@ -527 +521 @@
 
       if (!this.hasNext) {
-    this.resultSet.close(); // be a good citizen & close used result sets
+      this.statement.close(); // be a good citizen & close used statement 
       }
     } catch (SQLException ex) {
+    System.err.println("DocumentList.iterator.next(): "+ex);
       this.hasNext = false;
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/doctypes/DocumentSQLWriter.java

@@ -19 +19 @@
     update.addDate("IndexedDate", new java.sql.Timestamp(touchTime));
     update.addDate("ModifiedDate", new java.sql.Timestamp(modTime));
-    connection.execute(update.toString());
+    try {
+    Statement statement = connection.createStatement();
+    statement.execute(update.toString());
+    statement.close();
+    } catch (SQLException e) {
+    System.err.println("DocumentSQLWriter.touchDocument() Error: "+ e);
+    return false;
+    }
     System.out.println(update.toString());
     return true;
@@ -39 +46 @@
     select.addField("*");
     select.setWhere(new GS3SQLWhere(new GS3SQLWhereItem("DocID", "=", document.getID().toString())));
-    connection.execute(select.toString());
     
-    ResultSet results = connection.getResultSet();
-
-    if (results == null ||
-        !results.first())
-    { GS3SQLInsert insert = new GS3SQLInsert("document");
+    Statement statement = connection.createStatement();
+    ResultSet results = statement.executeQuery(select.toString());
+    if (!results.first()) { // empty result
+      GS3SQLInsert insert = new GS3SQLInsert("document");
       insert.addValue("DocID", document.getID().toString());
       insert.addValue("DocType", document.getDocumentType());
@@ -53 +58 @@
       insert.addDate("ModifiedDate", new java.sql.Timestamp(document.getModifiedDatestamp()));
 
-      connection.execute(insert.toString());
+      statement.execute(insert.toString());
     }
     else {
@@ -61 +66 @@
       //      connection.execute(update.toString());
     }
+    statement.close();
       }
-    } catch (Exception ex) {
-    System.out.println(ex);
+    } catch (SQLException ex) {
+    System.out.println("DocumentSQLWriter.writeDocument() Error:"+ex);
     }
     //    output.println(tag);

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/doctypes/HTMLDocument.java

@@ -279 +279 @@
     public Document getDOMDocument()
     {    
-    if (this.domDocument == null) {
+      if (this.domDocument == null) {
       URL     url =(URL) this.fileSet.getFile(0).getLocation();
       this.loadDocument(url);

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSDescriptive.java

@@ -8 +8 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -275 +276 @@
         } else {
         // TODO: raise an error!
-        System.out.println("Error: METSDescriiptive: unrecognised tag "+childName);
+        System.out.println("Error: METSDescriptive: unrecognised tag "+childName);
         }
     }
@@ -375 +376 @@
     select.setWhere(where);
     
-    connection.execute(select.toString());
-    
     try {
+        Statement statement = connection.createStatement();
+        ResultSet resultSet = statement.executeQuery(select.toString());
         GS3SQLAction action;
         
-        ResultSet resultSet = connection.getResultSet();
-        
-        if (resultSet != null &&
-        resultSet.first()) {
+        if (resultSet.first()) {
         // the node already exists - no need to do anything as such
         GS3SQLUpdate update = new GS3SQLUpdate("metadata");
@@ -390 +388 @@
         }
         else { // Set result set to null, just in case first() didn't work above...
-        resultSet = null;
-        
         // It is a new node and needs writing
         action = new GS3SQLInsert("metadata");
@@ -403 +399 @@
         
         // execute the action as required
-        connection.execute(action.toString());
+        statement.execute(action.toString());
         
         // get the resultSet again
@@ -409 +405 @@
         // 'MetadataRef'
         // System.out.println(select.toString());
-        connection.execute(select.toString());
-        
-        resultSet = connection.getResultSet();
-        resultSet.first();
-        
-        
-        // get the reference for this item...
-        sqlId = resultSet.getInt("MetadataRef");
-        connection.closeStatement();
+        resultSet = statement.executeQuery(select.toString());
+        
+        if (resultSet.first()) {
+        // get the reference for this item...
+        sqlId = resultSet.getInt("MetadataRef");
+        }
+        statement.close();
     }
     catch (SQLException sql){
-        System.out.println(sql);
+        System.err.println("METSDescriptive.writeSQL(): "+sql);
+    }
+    if (sqlId == -1) {
+        return false;
     }
     
@@ -470 +467 @@
         select.setWhere(where);
         
-        connection.execute(select.toString());
-        
-        // parse through the namespaces, calling the namespace class to create instances
-        // as it will
-        ResultSet namespaceSet = connection.getResultSet();
-
-        // clone the connection cos we are still usign the old result set
-        GS3SQLConnection cloned_connection = connection.cloneConnection();
-
-        if (namespaceSet != null && namespaceSet.first()) {
+        Statement statement = connection.createStatement();
+        ResultSet namespaceSet = statement.executeQuery(select.toString());
+        
+        // parse through the namespaces, calling the namespace class to create instances as it will
+        if (namespaceSet.first()) {
         do {
-            METSNamespace namespace = METSNamespace.readSQL(cloned_connection, namespaceSet);
+            METSNamespace namespace = METSNamespace.readSQL(connection, namespaceSet);
             if (namespace != null) {
             descriptive.addNamespace(namespace);
             }
             else {
-            System.out.println("Null namespace output");
+            System.err.println("Null namespace output");
             }
         }
         while (namespaceSet.next());
         }
-        cloned_connection.close();
-        cloned_connection = null;
+        statement.close();
         return descriptive;
     }
     catch (SQLException sqlEx){
-        System.out.println(sqlEx);
-        sqlEx.printStackTrace();
+        System.err.println("METSDescriptive.readSQL(): "+sqlEx);
         System.exit(1);
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSDescriptiveSet.java

@@ -9 +9 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -236 +237 @@
     GS3SQLWhereItem whereItem = new GS3SQLWhereItem("DocID", "=", document.getID().toString());
     select.setWhere(new GS3SQLWhere(whereItem));
-    connection.execute(select.toString());
-    
-    // clone the connection cos we are still using the old result set
-    GS3SQLConnection cloned_connection = connection.cloneConnection();
 
     // start going through the matching metadata blocks
     try {
-        ResultSet resultSet = connection.getResultSet();
-        resultSet.first();
-        do {
-        METSDescriptive descriptive = METSDescriptive.readSQL(document, cloned_connection, resultSet);
-        if (descriptive != null) {
-            set.addDescriptive(descriptive);
-        }
-        else {
-            System.out.println("Null descriptive");
-        }
-        } while (resultSet.next());
-        
-        connection.closeStatement();
+        Statement statement = connection.createStatement();
+        ResultSet resultSet = statement.executeQuery(select.toString());
+        if (resultSet.first()) {
+        do {
+            METSDescriptive descriptive = METSDescriptive.readSQL(document, connection, resultSet);
+            if (descriptive != null) {
+            set.addDescriptive(descriptive);
+            }
+            else {
+            System.out.println("Null descriptive");
+            }
+        } while (resultSet.next());
+        }
+        statement.close();
     }
     catch (SQLException sqlEx) {
-        System.out.println(sqlEx);
-        sqlEx.printStackTrace();
+        System.err.println("METSDescriptiveSet.readSQL(): "+sqlEx);
         System.exit(1);
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSDivision.java

@@ -10 +10 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -475 +476 @@
     select.setWhere(where);
     
-    connection.execute(select.toString());
+    Statement statement = null;
+    ResultSet resultSet = null;
     
     // Do the actual writing
     GS3SQLAction action;
     
-    ResultSet resultSet = connection.getResultSet();
     try {
-        if (resultSet == null ||
-        !resultSet.first()) {
-        if (resultSet != null) {
-            resultSet.close();
-            resultSet = null;
-        }
+        statement = connection.createStatement();
+        resultSet = statement.executeQuery(select.toString());
+        if (!resultSet.first()) {
         
         GS3SQLInsert insert = new GS3SQLInsert("divisions");
@@ -517 +515 @@
     action.addValue("UserLabel", this.userLabel);
     
-    if (!connection.execute(action.toString())){
+    try {
+        statement.execute(action.toString());
+    } catch (SQLException e) {
+        System.err.println("METSDivision.writeSQL(): "+e);
         return false;
     }
     
     // if doing a fresh item, get the new structure reference...
-    if (resultSet == null){
+    if (sqlRef == -1){
         // get the new structure reference
-        connection.execute(select.toString());
-        
-        // get the sql reference for the division
         try {
         // read in the structure reference
-        resultSet = connection.getResultSet();
-        if (resultSet == null) {
-            return false;
+        resultSet = statement.executeQuery(select.toString());
+        if (resultSet.first()) {
+            sqlRef = resultSet.getInt("DivisionRef");
         }
-        resultSet.first();
-        sqlRef = resultSet.getInt("DivisionRef");
-        resultSet.close();
-        resultSet = null;
         }
         catch (SQLException sqlex) {
-        System.err.println("Unable to retrieve reference for Division " + sqlex);
+        System.err.println("METSDIVISION.writeSQL(): Unable to retrieve reference for Division " + sqlex);
         return false;
         }
     }
-    // close the open resultSet, as we don't need it any longer...
-    else {
-        try {
-        resultSet.close();
-        }
-        catch (SQLException sql) {
-        System.err.println(sql + " " + select.toString());
-        }
-    }
     
     // delete the old file/metadata references
-    GS3SQLWhere referenceWhere = 
-        new GS3SQLWhere(new GS3SQLWhereItem("DivisionRef", "=", Integer.toString(sqlRef), 
-                        GS3SQLField.INTEGER_TYPE));
-    
-    GS3SQLDelete delete = new GS3SQLDelete("divisionfilerefs");
-    delete.setWhere(referenceWhere);
-    connection.execute(delete.toString());
-    
-    delete = new GS3SQLDelete("divisionmetarefs");
-    delete.setWhere(referenceWhere);
-    connection.execute(delete.toString());
-    
-    // write the new file references
-    if (this.fileRefs.size() > 0){
-        Iterator iterator = this.fileRefs.iterator();
-        
-        while (iterator.hasNext()) {
-        GS3SQLInsert fileinsert = new GS3SQLInsert("divisionfilerefs");
-        fileinsert.addValue("DocID", docId.toString());
-        fileinsert.addValue("DivisionRef", Integer.toString(sqlRef), GS3SQLField.INTEGER_TYPE);
-        fileinsert.addValue("DivisionType", "Group");
-        fileinsert.addValue("FileID", iterator.next().toString());
-        connection.execute(fileinsert.toString());
-        }
-    }
-    
-    // write the metadata references
-    if (this.metadataRefs.size() > 0){
-        Iterator iterator = this.metadataRefs.iterator();
-        
-        while (iterator.hasNext()) {
-        GS3SQLInsert metainsert = new GS3SQLInsert("divisionmetarefs");
-        metainsert.addValue("DocID", docId.toString());
-        metainsert.addValue("DivisionRef", Integer.toString(sqlRef), GS3SQLField.INTEGER_TYPE);
-        metainsert.addValue("DivisionType", "Group");
-        metainsert.addValue("MetaID", iterator.next().toString());
-        connection.execute(metainsert.toString());
-        }
-    }
-    
+    try {
+        GS3SQLWhere referenceWhere = 
+        new GS3SQLWhere(new GS3SQLWhereItem("DivisionRef", "=", Integer.toString(sqlRef), 
+                            GS3SQLField.INTEGER_TYPE));
+        
+        GS3SQLDelete delete = new GS3SQLDelete("divisionfilerefs");
+        delete.setWhere(referenceWhere);
+        
+        statement.execute(delete.toString());
+        
+        delete = new GS3SQLDelete("divisionmetarefs");
+        delete.setWhere(referenceWhere);
+        statement.execute(delete.toString());
+    
+        // write the new file references
+        if (this.fileRefs.size() > 0){
+        Iterator iterator = this.fileRefs.iterator();
+        
+        while (iterator.hasNext()) {
+            GS3SQLInsert fileinsert = new GS3SQLInsert("divisionfilerefs");
+            fileinsert.addValue("DocID", docId.toString());
+            fileinsert.addValue("DivisionRef", Integer.toString(sqlRef), GS3SQLField.INTEGER_TYPE);
+            fileinsert.addValue("DivisionType", "Group");
+            fileinsert.addValue("FileID", iterator.next().toString());
+            statement.execute(fileinsert.toString());
+        }
+        }
+        
+        // write the metadata references
+        if (this.metadataRefs.size() > 0){
+        Iterator iterator = this.metadataRefs.iterator();
+        
+        while (iterator.hasNext()) {
+            GS3SQLInsert metainsert = new GS3SQLInsert("divisionmetarefs");
+            metainsert.addValue("DocID", docId.toString());
+            metainsert.addValue("DivisionRef", Integer.toString(sqlRef), GS3SQLField.INTEGER_TYPE);
+            metainsert.addValue("DivisionType", "Group");
+            metainsert.addValue("MetaID", iterator.next().toString());
+            statement.execute(metainsert.toString());
+        }
+        }
+        statement.close();
+    } catch (SQLException e) {
+        System.err.println("METSDIVISION.writeSQL(): "+e);
+        return false;
+    }
     // write out any children in turn
     Iterator groups = this.children.values().iterator();
@@ -635 +626 @@
         select.setWhere(where);
 
-        connection.execute(select.toString());
+        Statement statement = connection.createStatement();
+        ResultSet childSet = statement.executeQuery(select.toString());
       
         // circulate through to obtain further children
-        ResultSet childSet = connection.getResultSet();
-        // clone the connection cos we are still usign the old result set
-        GS3SQLConnection cloned_connection = connection.cloneConnection();
-
         if (childSet.first())
         {
             do {
-            METSDivision childDivision = METSDivision.readSQL(cloned_connection, childSet);
+            METSDivision childDivision = METSDivision.readSQL(connection, childSet);
             division.addDivision(childDivision);
             }
             while (childSet.next());
         }
-
-        cloned_connection.close();
-        cloned_connection = null;
         select = new GS3SQLSelect("divisionfilerefs");
         select.addField("*");
@@ -659 +644 @@
         select.setWhere(where);
 
-        connection.execute(select.toString());
-
-        ResultSet fileSet = connection.getResultSet();
-        if (fileSet != null && fileSet.first()){
+        ResultSet fileSet = statement.executeQuery(select.toString());
+
+        if (fileSet.first()){
         do {
             String reference = fileSet.getString("FileID");
@@ -669 +653 @@
         while (fileSet.next());  
         }
-
+        
         select = new GS3SQLSelect("divisionmetarefs");
         select.addField("*");
@@ -676 +660 @@
         select.setWhere(where);
 
-        connection.execute(select.toString());
-
-        ResultSet metaSet = connection.getResultSet();
-        if (metaSet != null && metaSet.first()){
+        ResultSet metaSet = statement.executeQuery(select.toString());
+
+        if (metaSet.first()){
         do {
             String reference = metaSet.getString("MetaID");
@@ -687 +670 @@
         }
         
-        connection.closeStatement();
+        statement.close();
         return division;
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSFile.java

@@ -11 +11 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 
 import org.w3c.dom.Document;
@@ -201 +202 @@
     where.add(whereItem);
     select.setWhere(where);
-    connection.execute(select.toString());
+
+    Statement statement = null;
     
     // if not, then make an insert action
     try {
-        ResultSet results = connection.getResultSet();
-        if (results == null ||
-        !results.first()){
+        statement = connection.createStatement();
+        ResultSet results = statement.executeQuery(select.toString());
+        if (!results.first()){
         GS3SQLInsert insert = new GS3SQLInsert("files");
         
@@ -223 +225 @@
     }
     catch (SQLException ex){
-        System.err.println(ex);
+        System.err.println("METSFile.writeSQL(): "+ex);
         return false;
     }
@@ -230 +232 @@
     action.addValue("MIMEType", this.MIMEType);
     
-    boolean return_val = connection.execute(action.toString());
-    connection.closeStatement();
-    return return_val;
+    try {
+        statement.execute(action.toString());
+        statement.close();
+        
+    } catch (SQLException e) {
+        System.err.println("METSFile.writeSQL():"+e);
+        return false;
+    }
+    return true;
     }
     
@@ -250 +258 @@
     }
     catch (SQLException ex){
-        System.out.println(ex);
+        System.out.println("METSFile.readSQL(): "+ex);
     }
     catch (java.net.MalformedURLException urlEx){
-        System.out.println(urlEx);
+        System.out.println("METSFile.readSQL(): "+urlEx);
     }
     return null;
@@ -311 +319 @@
     } catch (java.net.MalformedURLException ex) {
         // TODO: raise error
-        System.err.println(ex);
+        System.err.println("METSFile.parse_flocateXML(): "+ex);
     }
     return thisFilePos;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSFileGroup.java

@@ -8 +8 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -271 +272 @@
     { 
     int sqlId = -1;
-    
     // check if this node is in the database already
     GS3SQLSelect select = new GS3SQLSelect("filegroups");
@@ -281 +281 @@
     select.setWhere(where);
     
-    connection.execute(select.toString());
-    
-    ResultSet selectResult = connection.getResultSet();
+    ResultSet results = null;
+    Statement statement = null;
     
     try {
-        if (selectResult == null ||
-        !selectResult.first()) {
+        statement = connection.createStatement();
+        results = statement.executeQuery(select.toString());
+        if (!results.first()) {
         GS3SQLInsert insert = new GS3SQLInsert("filegroups");
         
@@ -295 +295 @@
         insert.addValue("ParentType", parentIsSection ? SECTION_PARENT : GROUP_PARENT);
         
-        if (!connection.execute(insert.toString())) {
-            return false;
-        }
+        statement.execute(insert.toString());
         }
         else {
@@ -304 +302 @@
     }
     catch (SQLException ex){
-        System.err.println(ex);
+        System.err.println("METSFileGroup.writeSQL(): "+ex);
         return false;
     }
     
     // get the filegroup reference now
-    connection.execute(select.toString());
-    
     try {
-        ResultSet results = connection.getResultSet();
-        if (results == null) {
+        results = statement.executeQuery(select.toString());
+        if (results.first()) {
+        sqlId = results.getInt("FileGroupRef");
+        } else {
+        statement.close();
         return false;
         }
-        results.first();
-        sqlId = results.getInt("FileGroupRef");
-    }
-    catch (SQLException sqlex) {
-        System.out.println(sqlex);
+        statement.close();
+    } catch (SQLException sqlex) {
+        System.err.println("METSFileGroup.writeSQL(): "+sqlex);
         return false;
     }
-    
     // iterate over the child groups
     Iterator childIter = childGroups.iterator();
@@ -330 +326 @@
         
         if (!fileGroup.writeSQL(document, Integer.toString(sqlId), false, connection)){
+        System.err.println("METSFileGroup.writeSQL(): Couldn't write FileGroup");
         return false;
         }
@@ -337 +334 @@
     childIter = children.iterator();
     while (childIter.hasNext()){
-        METSFile file = (METSFile) childIter.next();
-        
+        METSFile file = (METSFile) childIter.next();        
         if (!file.writeSQL(sqlId, connection)){
+        System.err.println("METSFileGroup.writeSQL(): Couldn't write File");
         return false;
         }
@@ -371 +368 @@
         select.setWhere(where);
         
-        connection.execute(select.toString());
+        Statement statement = connection.createStatement();
         
         // parse through the child groups
-        ResultSet childSet = connection.getResultSet();
-        // clone the connection cos we are still usign the old result set
-        GS3SQLConnection cloned_connection = connection.cloneConnection();
+        ResultSet childSet = statement.executeQuery(select.toString());
         if (childSet.first()) {
         do {
-            METSFileGroup fileGroup = METSFileGroup.readSQL(document, cloned_connection, childSet);
+            METSFileGroup fileGroup = METSFileGroup.readSQL(document, connection, childSet);
             if (fileGroup != null) {
             group.addGroup(fileGroup);
@@ -386 +381 @@
         while (childSet.next());
         }
-        
         // now scan for file members
         select = new GS3SQLSelect("files");
         select.addField("*");
-        whereItem = new GS3SQLWhereItem("FileGroupRef", "=", Integer.toString(groupRef),
-                        GS3SQLField.INTEGER_TYPE);
+        whereItem = new GS3SQLWhereItem("FileGroupRef", "=", Integer.toString(groupRef), GS3SQLField.INTEGER_TYPE);
         where = new GS3SQLWhere(whereItem);
         select.setWhere(where);
-        connection.execute(select.toString());
-        
-        ResultSet childFileSet = connection.getResultSet();
-        if (childFileSet != null && childFileSet.first()) {
+        ResultSet childFileSet = statement.executeQuery(select.toString());
+        if (childFileSet.first()) {
         do {
-            METSFile file = METSFile.readSQL(cloned_connection, childFileSet);
+            METSFile file = METSFile.readSQL(connection, childFileSet);
             if (file != null) {
             group.addFile(file);
@@ -406 +397 @@
         while (childFileSet.next());
         }
-        cloned_connection.close();
-        cloned_connection = null;
+        statement.close();
         return group;
     }
     catch (SQLException sqlEx){
-        System.out.println(sqlEx);
+        System.err.println("METSFileGroup.readSQL(): "+sqlEx);
         System.exit(1);
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSFileSet.java

@@ -16 +16 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -232 +233 @@
         insert.addValue("FileSecID", "test"); // TODO: remove magic string
         
-        if (!connection.execute(insert.toString())){
+        Statement statement = null;
+        try {
+        statement = connection.createStatement();
+        statement.execute(insert.toString());
+        } catch (SQLException e) {
+        System.err.println("METSFileSet.writeSQL(): "+e);
         return false;
         }
@@ -246 +252 @@
         
         try {
-        connection.execute(select.toString());
-        
-        ResultSet set = connection.getResultSet();
-        set.first();
-        int sectionRef = set.getInt("FileSectionRef");
-        
-        this.reference = Integer.toString(sectionRef);
+        ResultSet set = statement.executeQuery(select.toString());
+        if (set.first()) {
+            int sectionRef = set.getInt("FileSectionRef");
+            
+            this.reference = Integer.toString(sectionRef);
+        }
+        statement.close();
         }
         catch (SQLException ex){
-        System.out.println(ex);
+        System.out.println("METSFileSet.writeSQL(): "+ex);
         return false;
         }
     }
     
+    if (this.reference == null) {
+        return false;
+    }
     // write out the children
     while (groups.hasNext()){
@@ -281 +290 @@
     GS3SQLWhereItem whereItem = new GS3SQLWhereItem("DocID", "=", document.getID().toString());
     select.setWhere(new GS3SQLWhere(whereItem));
-    connection.execute(select.toString());
+
+    Statement statement = null;
     
     // Get the identifier for this file set, etc.
-    ResultSet sections = connection.getResultSet();
-    int fileSetRef;
+    int fileSetRef = -1;
     try {
-        sections.first();
-        fileSetRef     = sections.getInt("FileSectionRef");
-        set.reference = Integer.toString(fileSetRef);
+        statement = connection.createStatement();
+        ResultSet sections = statement.executeQuery(select.toString());
+        if (sections.first()) {
+        fileSetRef = sections.getInt("FileSectionRef");
+        set.reference = Integer.toString(fileSetRef);
+        } else {
+        statement.close();
+        return null;
+        }
+        
     }
     catch (SQLException ex){
-        System.out.println(ex);
+        System.err.println("METSFileSet.readSQL() "+ex);
         return null;
     }
-    
     // Get child file groups
     select = new GS3SQLSelect("filegroups");
@@ -307 +322 @@
     where.add(whereItem);
     select.setWhere(where);
-    connection.execute(select.toString());
-    
     // start going through the matching file groups
-    // clone the connection cos we are still usign the old result set
-    GS3SQLConnection cloned_connection = connection.cloneConnection();
     try {
-        ResultSet resultSet = connection.getResultSet();
-        resultSet.first();
-        do {
-        METSFileGroup filegroup = METSFileGroup.readSQL(document, cloned_connection, resultSet);
-        if (filegroup != null) {
-            set.addGroup(filegroup);
-        }
-        } while (resultSet.next());
-
-        cloned_connection.close();
-        cloned_connection = null;
+        ResultSet resultSet = statement.executeQuery(select.toString());
+        if (resultSet.first()) {
+        do {
+            METSFileGroup filegroup = METSFileGroup.readSQL(document, connection, resultSet);
+            if (filegroup != null) {
+            set.addGroup(filegroup);
+            }
+        } while (resultSet.next());
+        }
+        statement.close();
     }
     catch (SQLException sqlEx) {
-        System.out.println(sqlEx);
-        sqlEx.printStackTrace();
+        System.out.println("METSFileSet.readSQL(): "+sqlEx);
         System.exit(1);
     }
-    
     return set;
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSNamespace.java

@@ -6 +6 @@
 import java.util.Iterator;
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -150 +151 @@
 
     // Execute the action
-    connection.execute(action.toString());
-
+    Statement statement = null;
+    try {
+        statement = connection.createStatement();
+        statement.execute(action.toString());
+    } catch (SQLException e) {
+        System.err.println("METSNamespace.writeSQL(): "+e);
+        return false;
+    }
     // then get the namespace reference number if needsbe...
     if (this.id == null) {
@@ -161 +168 @@
         where.add(where);
         try {
-        connection.execute(select.toString());
-      
-        ResultSet result = connection.getResultSet();
-        result.last();
-        this.id = Integer.toString(result.getInt("NamespaceRef"));
+        ResultSet result  = statement.executeQuery(select.toString());
+            if (result.last()) {
+            this.id = Integer.toString(result.getInt("NamespaceRef"));
+        }
+        statement.close();
         }
         catch (SQLException sqlex){
         this.id = null;
-        System.err.println(sqlex);
+        System.err.println("METSNamespace.writeSQL(): "+sqlex);
         return false;
         }
@@ -201 +208 @@
         select.setWhere(where);
 
-        connection.execute(select.toString());
-      
-        ResultSet valuesSet = connection.getResultSet();
-        if (valuesSet != null && valuesSet.first()) {
+        Statement statement = connection.createStatement();
+        ResultSet valuesSet = statement.executeQuery(select.toString());
+        if (valuesSet.first()) {
         do {
             String label = valuesSet.getString("Label");
@@ -213 +219 @@
         while (valuesSet.next());
         }
-
+        statement.close();
         return namespace;
     }
     catch (java.net.MalformedURLException urlEx){
-        System.out.println(urlEx);
+        System.err.println("METSNamespace.readSQL(): "+urlEx);
     }
     catch (SQLException sqlEx){
-        System.out.println(sqlEx);
+        System.err.println("METSNamespace.readSQL(): "+sqlEx);
     }    
     return null;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSStructure.java

@@ -10 +10 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -180 +181 @@
     { 
     int sqlRef = -1;
-    ResultSet results;
-
+    ResultSet results = null;
+    Statement statement = null;
+    
     // Prepare query to see if this structure has already been written
     GS3SQLSelect select = new GS3SQLSelect("structure");
@@ -190 +192 @@
     where.add(item);
     select.setWhere(where);
-
+    
     // attempt to execute the query & get the current structure's reference
     try {
-        connection.execute(select.toString());
-        results = connection.getResultSet();
-        if (results != null &&
-        results.first()){
+        statement = connection.createStatement();
+        results = statement.executeQuery(select.toString());
+        if (results.first()){
         sqlRef = results.getInt("structureRef");
         }
-        else { 
-        results = null;
-        }
-        
-        if (results != null) {
-        results.close();
-        }
     }
     catch (SQLException sqlEx) {
-        System.err.print(sqlEx);
+        System.err.print("METSStructure.writeSQL(): "+sqlEx);
         return false;
     }
     
     // insert a new structure if it wasn't there previously
-    if (results == null) {
+    if (sqlRef == -1) {
         GS3SQLInsert insert = new GS3SQLInsert("structure");
         insert.addValue("DocID", document.getID().toString());
@@ -220 +214 @@
         insert.addValue("Label", this.label);
         
-        if (!connection.execute(insert.toString())) {
+        try {
+        statement.execute(insert.toString());
+        
+        // get the new structure reference by re-running the original select object
+        results = statement.executeQuery(select.toString());
+        if (results.first()) {
+            sqlRef = results.getInt("StructureRef");
+        } 
+        } catch (SQLException sqlEx) {
+        System.err.print("METSStructure.writeSQL(): "+sqlEx);
         return false;
         }
         
-        // get the new structure reference by re-running the original select object
-        connection.execute(select.toString());
-
-        try {
-        results = connection.getResultSet();
-        results.first();
-        sqlRef = results.getInt("StructureRef");
-        }
-        catch (SQLException sql) {
-        System.err.println(sql);
-        return false;
-        }
     }
     else {
@@ -244 +235 @@
         update.addValue("Label", this.label);
         
-        connection.execute(update.toString());
-    }
-    
+        try {
+        statement.execute(update.toString());
+        } catch (SQLException sqlEx) {
+        System.err.print("METSStructure.writeSQL(): "+sqlEx);
+        return false;
+        }
+    }
+    
+    // close the statement
+    try {
+        statement.close();
+    } catch (SQLException e) {
+        System.err.println("METSStructure.writeSQL(): "+e);
+    }
     // write out the child groups (Divisions) now...    
     Iterator groups = this.children.values().iterator();
@@ -296 +298 @@
         select.setWhere(where);
 
-        connection.execute(select.toString());
+        Statement statement = connection.createStatement();
+        ResultSet divisionSet = statement.executeQuery(select.toString());
 
         // parse through the divisions
-        ResultSet divisionSet = connection.getResultSet();
-        // use a new connection for the following bit, cos we still want to use results from the last execute
-        GS3SQLConnection cloned_connection = connection.cloneConnection();
-        if (divisionSet != null && divisionSet.first()) {
+        if (divisionSet.first()) {
         do {
-            METSDivision division = METSDivision.readSQL(cloned_connection, divisionSet);
+            METSDivision division = METSDivision.readSQL(connection, divisionSet);
             if (division != null) {
             structure.addDivision(division);
@@ -311 +311 @@
         while (divisionSet.next());
         }
-        cloned_connection.close();
-        cloned_connection = null;
+        statement.close();
         return structure;
     }
-    catch (SQLException sqlEx)
-        { System.out.println(sqlEx + " " + select.toString());
-        sqlEx.printStackTrace();
+    catch (SQLException sqlEx) {
+        System.err.println("METSStructure.readSQL(): "+ sqlEx + " " + select.toString());
         System.exit(1);
-        }
+    }
     return null;
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/METSStructureSet.java

@@ -10 +10 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -157 +158 @@
     GS3SQLWhereItem whereItem = new GS3SQLWhereItem("DocID", "=", document.getID().toString());
     select.setWhere(new GS3SQLWhere(whereItem));
-    connection.execute(select.toString());
-    // clone the connection cos we are still usign the old result set
-    GS3SQLConnection cloned_connection = connection.cloneConnection();
-
+    
     // start going through the matching metadata blocks
     try {
-        ResultSet resultSet = connection.getResultSet();
-        resultSet.first();
-        do {
-        METSStructure structure = METSStructure.readSQL(document, cloned_connection, resultSet);
-        if (structure != null) {
-            set.addStructure(structure);
-        }
-        } while (resultSet.next());
-        cloned_connection.close();
-        cloned_connection = null;
+        Statement statement = connection.createStatement();
+        ResultSet resultSet = statement.executeQuery(select.toString());
+        if (resultSet.first()) {
+        do {
+            METSStructure structure = METSStructure.readSQL(document, connection, resultSet);
+            if (structure != null) {
+            set.addStructure(structure);
+            }
+        } while (resultSet.next());
+        }
+        statement.close();
     }
     catch (SQLException sqlEx) {
-        System.out.println(sqlEx);
-        sqlEx.printStackTrace();
+        System.err.println("METSStructureSet.readSQL(): "+sqlEx);
         System.exit(1);
     }

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/metadata/SimpleNamespace.java

@@ -9 +9 @@
 
 import java.sql.SQLException;
+import java.sql.Statement;
 import java.sql.ResultSet;
 
@@ -281 +282 @@
     }
     
+    Statement statement = null;
+    ResultSet results = null;
     try {
+        statement = connection.createStatement();
         if (this.id == null) {
         GS3SQLSelect select = new GS3SQLSelect("namespaces");
         select.setWhere(new GS3SQLWhere(new GS3SQLWhereItem("MetadataRef", "=", Integer.toString(parentId), GS3SQLField.INTEGER_TYPE)));
         select.addField("NamespaceRef");
-        connection.execute(select.toString());
-        
-        ResultSet results = connection.getResultSet();
-        results.first();
-        sqlId = Integer.toString(results.getInt("NamespaceRef"));
+        results = statement.executeQuery(select.toString());
+        if (results.first()) {
+            sqlId = Integer.toString(results.getInt("NamespaceRef"));
+        } else {
+            statement.close();
+            return false;
+        }
         }
         else {
@@ -300 +306 @@
         GS3SQLWhere where = new GS3SQLWhere(new GS3SQLWhereItem("NamespaceRef", "=", sqlId, GS3SQLField.INTEGER_TYPE));
         delete.setWhere(where);
-        connection.execute(delete.toString());
+        statement.execute(delete.toString());
         
         // write out the metadata for this namespace
@@ -316 +322 @@
             insert.addValue("Label", thisKey);
             insert.addValue("Value", value);
-            connection.execute(insert.toString());
-        }
-        }
+            statement.execute(insert.toString());
+        }
+        }
+        statement.close();
     }
     catch (SQLException sql) {
-        System.out.println(sql);
+        System.out.println("SimpleNamespace.writeSQL(): "+sql);
     }
     return true;

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/gs3build/notifier/NotifierManager.java

@@ -27 +27 @@
     String lastBuild = dateToSqlString(collManager.getBuildDate());
     System.out.println("last build was " + lastBuild);
-    Statement statement = collManager.getDatabase().createStatement();
         try {
+        Statement statement = collManager.getDatabase().createStatement();
             // detect all new documents. A document is new if and only if
             // AccessionDate >= CollectionLastRebuiltDate
@@ -130 +130 @@
     private String getDocumentTitle(String documentID, GS3SQLConnection database) {
         System.out.println("getting title for document " + documentID + " from database");
-        Statement statement = database.createStatement();
 
-        ResultSet results;
         try {
+        Statement statement = database.createStatement();
             String sqlString = "SELECT mdvalues.value " +
         "FROM structure s JOIN divisions d ON (s.structureref = d.parentref) " +
@@ -144 +143 @@
         "                                 AND d.parenttype = 'Structure' " + 
         "                                 AND m.docID = s.docid;";
-            results = statement.executeQuery(sqlString);
-            if (results.next()) {
+            ResultSet results = statement.executeQuery(sqlString);
+            if (results.first()) {
                 String title = results.getString("value");
                 System.out.println("title is " + title);

branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/util/SQLQuery.java

@@ -6 +6 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
 
 import java.util.ArrayList;
@@ -17 +18 @@
 
     public boolean setDatabase(String db_name) {
-    try {
-        connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);
-        
+    connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);
+    try {
+
         // test the connection
         String query = "select * from "+GSSQL.DOCUMENT_TABLE+GSSQL.END;
-        if (!connection.execute(query)) {
-        //connection.close();
-        connection = null;
-        return false;
-        }
+        Statement s = connection.createStatement();
+        s.execute(query);
+        s.close();
         return true;
     } catch (Exception e) {
-        System.err.println("SQLQuery.setDatabase Exception");
-        return false;
-    }
+        System.err.println("SQLQuery.setDatabase():"+e);
+    }
+    closeConnection();
+    return false;
     }
 
     public void closeConnection() {
-    connection=null;
+    if (connection != null) {
+        connection.close();
+        connection=null;
+    }
     }
 
@@ -52 +55 @@
         GSSQL.END;
     
-    connection.execute(query);
+    Statement statement = null;
     ResultSet results = null;
     String doc_id = null;
     String meta_id = null;
     try {
-        results = connection.getResultSet();
-        results.first();
-        doc_id = results.getString(GSSQL.DOCUMENT_ID);
-        meta_id = results.getString(GSSQL.METADATA_ID);
-    } catch (java.sql.SQLException e) {
-
-        System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage());
-        return null;
-    }
-    System.out.println("doc id ="+doc_id+", meta id = "+meta_id);
+        statement = connection.createStatement();
+        results = statement.executeQuery(query);
+        if (results.first()) {
+        doc_id = results.getString(GSSQL.DOCUMENT_ID);
+        meta_id = results.getString(GSSQL.METADATA_ID);
+        } else {
+        statement.close();
+        return null;
+        }
+    } catch (java.sql.SQLException e) {
+        System.err.println("SQLQuery.MGNum2OID(): "+e);
+        return null;
+    }
 
     // now get division label
@@ -77 +83 @@
         GSSQL.END;
 
-    connection.execute(query);
+    
     String short_label = null;
     try {
-        results = connection.getResultSet();
-        results.first();
-        short_label = results.getString(GSSQL.SHORT_LABEL);
-
-    } catch (java.sql.SQLException e) {
+        results = statement.executeQuery(query);
+        if (results.first()) {
+        short_label = results.getString(GSSQL.SHORT_LABEL);
+        } else {
+        statement.close();
+        return null;
+        }
+        statement.close();
+    } catch (SQLException e) {
 
         System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage());
         return null;
     }
-
-    ///ystem.out.println("short label = "+short_label);
-
-    System.out.println("final doc_id = "+GS3OID.createOID(doc_id, short_label));
+    
     return GS3OID.createOID(doc_id, short_label);
     }
 
     public String OID2MGNum(String oid) {
-    System.out.println("converting oid "+oid+" to mg num");
     String id = getDocumentMetadata(oid, "gsdl3.mgseqno");
-    System.out.println("mg id = "+id);
     return id;
 
@@ -117 +122 @@
         " and "+ GSSQL.STRUCTURE_ID +GSSQL.EQUALS_QUOTE + "Section" + GSSQL.QUOTE +
         GSSQL.END;
-    
-    connection.execute(query);
-    ResultSet results = null;
-    try {
-        results = connection.getResultSet();
-        if (results.next()) {
-        return true;
-        } 
+    boolean is_hierarchical = false;
+    try {
+        Statement statement = connection.createStatement();
+        ResultSet results = statement.executeQuery(query);
+        is_hierarchical = results.first();
+        statement.close();
     } catch (java.sql.SQLException e) {
         return false;
     }
-
-    return false;
+    
+    return is_hierarchical;
     }
     
@@ -135 +138 @@
     public String getClassifierMetadata(String oid, String full_meta_name) {
 
-    if (full_meta_name.equals("Title") || full_meta_name.equals("numleafdocs")) {
-        // get the description
-        String field_name="";
-        if (full_meta_name.equals("Title")) {
-        field_name = GSSQL.DESCRIPTION;
-        } else {
-        field_name = GSSQL.NUM_LEAF_DOCUMENTS;
-        }
-         
-        String query = "select "+ field_name+
-        " from " + GSSQL.CLASSIFIER_TABLE +
-        " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
-        GSSQL.END;
+    if (!full_meta_name.equals("Title") && !full_meta_name.equals("numleafdocs")) {
+        return null;
+    }
+    // get the description
+    String field_name="";
+    if (full_meta_name.equals("Title")) {
+        field_name = GSSQL.DESCRIPTION;
+    } else {
+        field_name = GSSQL.NUM_LEAF_DOCUMENTS;
+    }
+    
+    String query = "select "+ field_name+
+        " from " + GSSQL.CLASSIFIER_TABLE +
+        " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
+        GSSQL.END;
+    
+    String value = null;
+    try {
+        Statement statement = connection.createStatement();
+        ResultSet results = statement.executeQuery(query);
+        if (results.first()) {
+        value = results.getString(field_name);
+        }
+        statement.close();
+    } catch (java.sql.SQLException e) {
         
-        connection.execute(query);
-        ResultSet results = null;
-        String value = null;
-        try {
-        results = connection.getResultSet();
-        results.first();
-        value = results.getString(field_name);
-        } catch (java.sql.SQLException e) {
-        
-        System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage());
-        return null;
-        }
-        return value;
- 
-    }  else return null;
-    
+        System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage());
+        return null;
+    }
+    return value;
+    
     }
     
@@ -176 +180 @@
         section_id = GS3OID.getSectionLabel(oid);
     }
-    System.out.println("getDocChildren: oid = "+oid+", doc id = "+doc_id+", sec id ="+section_id);
 //      int sep_index = oid.indexOf(".");
 //      if (sep_index != -1) {
@@ -190 +193 @@
         GSSQL.END;
 
-    connection.execute(query);
     ResultSet results = null;
+    Statement statement = null;
     String div_ref = null;
     try {
-        results = connection.getResultSet();
-        results.first();
-        div_ref = results.getString(GSSQL.DIVISION_REF);
+        statement = connection.createStatement();
+        results = statement.executeQuery(query);
+        if (results.first()) {
+        div_ref = results.getString(GSSQL.DIVISION_REF);
+        } else {
+        statement.close();
+        return null;
+        }
     } catch (java.sql.SQLException e) {
         
@@ -210 +218 @@
         " and "+GSSQL.PARENT_REF+GSSQL.EQUALS_QUOTE+div_ref+GSSQL.QUOTE+
         GSSQL.END;
-    connection.execute(query);
-    results = null;
-    try {
-        results = connection.getResultSet();
+    try {
+        results = statement.executeQuery(query);
         while (results.next()) {
         String id = results.getString(GSSQL.SHORT_LABEL);
         children.add(GS3OID.createOID(doc_id, id));
         }
+        statement.close();
     } catch (java.sql.SQLException e) {
         
@@ -247 +254 @@
         GSSQL.END; 
     
-    connection.execute(query);
-    ResultSet results = null;
-    try {
-        results = connection.getResultSet();
+    try {
+        Statement statement = connection.createStatement();
+        ResultSet results = statement.executeQuery(query);
         while (results.next()) {
         String id = results.getString(GSSQL.CLASSIFIER_ID);
         children.add(id);
         }
-    } catch (java.sql.SQLException e) {
-        
+        statement.close();
+    } catch (java.sql.SQLException e) {
+        
         System.err.println("SQLQuery.getClassifierChildren Error: "+e.getMessage());
         return null;
@@ -264 +271 @@
 
     public ArrayList getClassifierDocChildren(String oid) {
-    System.out.println("in getclassifierdocchildren");
     ArrayList children = new ArrayList();
 
@@ -273 +279 @@
         " order by "+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ORDER+
         GSSQL.END;
-    System.out.println("query="+query);
-    connection.execute(query);
-    ResultSet results = null;
-    try {
-        results = connection.getResultSet();
+    try {
+        Statement statement = connection.createStatement();
+        ResultSet results = statement.executeQuery(query);
         while (results.next()) {
         String id = results.getString(GSSQL.DOCUMENT_ID);
@@ -287 +291 @@
         return null;
     }
-    // now check for documents
     
     return children;
@@ -306 +309 @@
 //      }
 
-    System.out.println("get meta for "+oid+", doc id = "+doc_id+"sec id = "+section_id);
 
     // get the metadata block id
@@ -318 +320 @@
         GSSQL.END;
     
-    connection.execute(query);
+    Statement statement = null;
     ResultSet results = null;
     String meta_id = null;
     try {
-        results = connection.getResultSet();
-        results.first();
-        meta_id = results.getString(GSSQL.METADATA_ID);
+        statement = connection.createStatement();
+        results = statement.executeQuery(query);
+        if (results.first()) {
+        meta_id = results.getString(GSSQL.METADATA_ID);
+        } else {
+        statement.close();
+        return null;
+        }
     } catch (java.sql.SQLException e) {
 
@@ -330 +337 @@
         return null;
     }
-    System.err.println("metadata block id = "+meta_id);
-
+    
     // now get the list of namespace refs for the right namespace
     int sep_index = full_meta_name.indexOf('.');
@@ -341 +347 @@
         meta_name = full_meta_name.substring(sep_index+1);
     }
-    System.out.println("orig meta = "+full_meta_name+", ns = "+meta_ns+", name="+meta_name);
 
     query = "select * "+ 
@@ -351 +356 @@
         " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
         GSSQL.END;
-    System.out.println("query="+query);
-    connection.execute(query);
+    
     String meta_value = null;
     try {
-        results = connection.getResultSet();
+        statement = connection.createStatement();
+        results = statement.executeQuery(query);
         while (results.next()) {
         String m_name = results.getString(GSSQL.LABEL);
@@ -363 +368 @@
         }
         }
+        statement.close();
     } catch (java.sql.SQLException e) {