Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that searches a relational DB for elements and 

 *             attributes that have free text matches a query string,

 *             or structured query matches to a path specified node in the 

 *             XML hierarchy.  It returns a result set consisting of the 

 *             document ID for each document that satisfies the query

 *  Copyright: 2000 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Matt Jones

 *    Release: @release@

 *

 *   '$Author: jones $'

 *     '$Date: 2003-12-10 01:15:07 -0800 (Wed, 10 Dec 2003) $'

 * '$Revision: 1956 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat;

import edu.ucsb.nceas.morpho.datapackage.*;

import java.io.*;

import java.util.StringTokenizer;

import java.util.Vector;

import java.util.zip.*;

import java.net.URL;

import java.net.MalformedURLException;

import java.sql.*;

import java.util.Stack;

import java.util.Hashtable;

import java.util.Enumeration;

import java.io.File;

import java.io.FileWriter;

import java.io.BufferedWriter;

import javax.servlet.ServletOutputStream;

/** 

 * A Class that searches a relational DB for elements and 

 * attributes that have free text matches a query string,

 * or structured query matches to a path specified node in the 

 * XML hierarchy.  It returns a result set consisting of the 

 * document ID for each document that satisfies the query

 */

public class DBQuery {

  static final int ALL = 1;

  static final int WRITE = 2;

  static final int READ = 4;

  //private Connection  conn = null;

  private String  parserName = null;

  private MetaCatUtil util = new MetaCatUtil();

  /**

   * the main routine used to test the DBQuery utility.

   * <p>

   * Usage: java DBQuery <xmlfile>

   *

   * @param xmlfile the filename of the xml file containing the query

   */

  static public void main(String[] args) {

     if (args.length < 1)

     {

        System.err.println("Wrong number of arguments!!!");

        System.err.println("USAGE: java DBQuery [-t] [-index] <xmlfile>");

        return;

     } else {

        try {

          int i = 0;

          boolean showRuntime = false;

          boolean useXMLIndex = false;

          if ( args[i].equals( "-t" ) ) {

            showRuntime = true;

            i++;

          }

          if ( args[i].equals( "-index" ) ) {

            useXMLIndex = true;

            i++;

          } 

          String xmlfile  = args[i];

          // Time the request if asked for

          double startTime = System.currentTimeMillis();

          // Open a connection to the database

          MetaCatUtil   util = new MetaCatUtil();

          //Connection dbconn = util.openDBConnection();

          double connTime = System.currentTimeMillis();

          // Execute the query

          DBQuery queryobj = new DBQuery(util.getOption("saxparser"));

          FileReader xml = new FileReader(new File(xmlfile));

          Hashtable nodelist = null;

          nodelist = queryobj.findDocuments(xml, null, null, useXMLIndex);

          // Print the reulting document listing

          StringBuffer result = new StringBuffer();

          String document = null;

          String docid = null;

          result.append("<?xml version=\"1.0\"?>\n");

          result.append("<resultset>\n"); 

          if (!showRuntime)

          {

            Enumeration doclist = nodelist.keys();

            while (doclist.hasMoreElements()) {

              docid = (String)doclist.nextElement();

              document = (String)nodelist.get(docid);

              result.append("  <document>\n    " + document + 

                            "\n  </document>\n");

            }

            result.append("</resultset>\n");

          }

          // Time the request if asked for

          double stopTime = System.currentTimeMillis();

          double dbOpenTime = (connTime - startTime)/1000;

          double readTime = (stopTime - connTime)/1000;

          double executionTime = (stopTime - startTime)/1000;

          if (showRuntime) {

            System.out.print("  " + executionTime);

            System.out.print("  " + dbOpenTime);

            System.out.print("  " + readTime);

            System.out.print("  " + nodelist.size());

            System.out.println();

          }

          //System.out.println(result);

          //write into a file "result.txt"

          if (!showRuntime)

          {

            File f = new File("./result.txt");

            FileWriter fw = new FileWriter(f);

            BufferedWriter out = new BufferedWriter(fw);

            out.write(result.toString());          

            out.flush();

            out.close();

            fw.close();

          }

        } 

        catch (Exception e) {

          System.err.println("Error in DBQuery.main");

          System.err.println(e.getMessage());

          e.printStackTrace(System.err);

        }

     }

  }

  /**

   * construct an instance of the DBQuery class 

   *

   * <p>Generally, one would call the findDocuments() routine after creating 

   * an instance to specify the search query</p>

   *

   * @param conn the JDBC connection that we use for the query

   * @param parserName the fully qualified name of a Java class implementing

   *                   the org.xml.sax.XMLReader interface

   */

  public DBQuery(String parserName ) 

                  throws IOException, 

                         SQLException, 

                         ClassNotFoundException {

    //this.conn = conn;

    this.parserName = parserName;

  }

  /** 

   * routine to search the elements and attributes looking to match query

   *

   * @param xmlquery the xml serialization of the query (@see pathquery.dtd)

   * @param user the username of the user

   * @param group the group of the user

   */

  public Hashtable findDocuments(Reader xmlquery, String user, String[] groups)

  {

    return findDocuments(xmlquery, user, groups, true);

  }

  /** 

   * routine to search the elements and attributes looking to match query

   *

   * @param xmlquery the xml serialization of the query (@see pathquery.dtd)

   * @param user the username of the user

   * @param group the group of the user

   * @param useXMLIndex flag whether to search using the path index

   */

  public Hashtable findDocuments(Reader xmlquery, String user, String[] groups,

                                 boolean useXMLIndex)

  {

      Hashtable result = new Hashtable();

      try

      {

        // Get the XML query and covert it into a SQL statment

        QuerySpecification qspec = new QuerySpecification(xmlquery, 

                                   parserName, 

                                   util.getOption("accNumSeparator"));

        result = findDocuments(qspec, user, groups, useXMLIndex);

      }

      catch (IOException ioe) 

      {

        MetaCatUtil.debugMessage("IO error in DBQuery.findDocuments:", 30);

        MetaCatUtil.debugMessage(ioe.getMessage(), 30);

      }

      return result;

  }

  /** 

   * routine to search the elements and attributes looking to match query

   *

   * @param qspec java object of the query

   * @param user the username of the user

   * @param group the group of the user

   * @param useXMLIndex flag whether to search using the path index

   */

  public Hashtable findDocuments(QuerySpecification qspec, String user, 

                                  String[] groups, boolean useXMLIndex)

  {

      Hashtable   docListResult = new Hashtable();

      PreparedStatement pstmt = null;

      String docid = null;

      String docname = null;

      String doctype = null;

      String createDate = null;

      String updateDate = null;

      String fieldname = null;

      String fielddata = null;

      String relation = null;

      //Connection dbconn = null;

      //Connection dbconn2 = null;

      int rev = 0;

      StringBuffer document = null;

      DBConnection dbconn = null;

      int serialNumber = -1;

      try {

        dbconn=DBConnectionPool.getDBConnection("DBQuery.findDocuments");

        serialNumber=dbconn.getCheckOutSerialNumber();

        String query = qspec.printSQL(useXMLIndex);

        String ownerQuery = getOwnerQuery(user);

        MetaCatUtil.debugMessage("query: "+query, 30);

        //MetaCatUtil.debugMessage("query: "+ownerQuery, 30);

        // if query is not the owner query, we need to check the permission

        // otherwise we don't need (owner has all permission by default)

        if (!query.equals(ownerQuery))

        {

          // set user name and group

          qspec.setUserName(user);

          qspec.setGroup(groups);

          // Get access query

          String accessQuery = qspec.getAccessQuery();

          query = query + accessQuery;

          MetaCatUtil.debugMessage(" final query: "+query, 30);

        }

        double startTime = System.currentTimeMillis()/1000;

        pstmt = dbconn.prepareStatement(query);

        // Execute the SQL query using the JDBC connection

        pstmt.execute();

        ResultSet rs = pstmt.getResultSet();

        double queryExecuteTime =System.currentTimeMillis()/1000; 

        MetaCatUtil.debugMessage("Time for execute query: "+ 

                                            (queryExecuteTime -startTime), 30);

        boolean tableHasRows = rs.next();

        while (tableHasRows) 

        {

          docid = rs.getString(1).trim();

          //long checkTimeStart = System.currentTimeMillis();

          //boolean permit =hasPermission(user, groups, docid);

          //long checkTimeEnd = System.currentTimeMillis();

          //MetaCatUtil.debugMessage("check permission time: "+

                                  //(checkTimeEnd - checkTimeStart), 30);

          //if ( !permit ) {

            // Advance to the next record in the cursor

            //tableHasRows = rs.next();

            //continue;

          //}

          docname = rs.getString(2);

          doctype = rs.getString(3);

          createDate = rs.getString(4);

          updateDate = rs.getString(5);

          rev = rs.getInt(6);

          // if there are returndocs to match, backtracking can be performed

          // otherwise, just return the document that was hit

          Vector returndocVec = qspec.getReturnDocList();

          if (returndocVec.size() != 0 && !returndocVec.contains(doctype) 

              && !qspec.isPercentageSearch())

          { 

            MetaCatUtil.debugMessage("Back tracing now...", 20);

            String sep = util.getOption("accNumSeparator");

            StringBuffer btBuf = new StringBuffer();

            btBuf.append("select docid from xml_relation where ");

            //build the doctype list for the backtracking sql statement

            btBuf.append("packagetype in (");

            for(int i=0; i<returndocVec.size(); i++)

            {

              btBuf.append("'").append((String)returndocVec.get(i)).append("'");

              if (i != (returndocVec.size() - 1))

              {

                btBuf.append(", ");

              } 

            }

            btBuf.append(") ");

            btBuf.append("and (subject like '");

            btBuf.append(docid).append("'");

            btBuf.append("or object like '");

            btBuf.append(docid).append("')");

            PreparedStatement npstmt = dbconn.

                                       prepareStatement(btBuf.toString());

            //should incease usage count

            dbconn.increaseUsageCount(1);

            npstmt.execute();

            ResultSet btrs = npstmt.getResultSet();

            boolean hasBtRows = btrs.next();

            while (hasBtRows)

            { //there was a backtrackable document found

              DocumentImpl xmldoc = null;

              String packageDocid = btrs.getString(1);

              util.debugMessage("Getting document for docid: "+packageDocid,40);

              try

              {

                //  THIS CONSTRUCTOR BUILDS THE WHOLE XML doc not needed here

                // xmldoc = new DocumentImpl(dbconn, packageDocid);

                //  thus use the following to get the doc info only

                //  xmldoc = new DocumentImpl(dbconn);

                xmldoc = new DocumentImpl(packageDocid, false);

                if (xmldoc == null) {

                  util.debugMessage("Document was null for: "+packageDocid, 50);

                }

              }

              catch(Exception e)

              {

                System.out.println("Error getting document in " + 

                                   "DBQuery.findDocuments: " + e.getMessage());

              }

              String docid_org = xmldoc.getDocID();

              if (docid_org == null) {

                util.debugMessage("Docid_org was null.", 40);

              }

              docid   = docid_org.trim();

              docname = xmldoc.getDocname();

              doctype = xmldoc.getDoctype();

              createDate = xmldoc.getCreateDate();

              updateDate = xmldoc.getUpdateDate();

              rev = xmldoc.getRev();

              document = new StringBuffer();

              String completeDocid = docid + util.getOption("accNumSeparator");

              completeDocid += rev;

              document.append("<docid>").append(completeDocid);

              document.append("</docid>");

              if (docname != null) {

                document.append("<docname>" + docname + "</docname>");

              }

              if (doctype != null) {

                document.append("<doctype>" + doctype + "</doctype>");

              }

              if (createDate != null) {

                document.append("<createdate>" + createDate + "</createdate>");

              }

              if (updateDate != null) {

                document.append("<updatedate>" + updateDate + "</updatedate>");

              }

              // Store the document id and the root node id

              docListResult.put(docid,(String)document.toString());

              // Get the next package document linked to our hit

              hasBtRows = btrs.next();

            }

            npstmt.close();

            btrs.close();

          } 

          else if (returndocVec.size() != 0 && returndocVec.contains(doctype)) 

          {

            document = new StringBuffer();

            String completeDocid = docid + util.getOption("accNumSeparator");

            completeDocid += rev;

            document.append("<docid>").append(completeDocid).append("</docid>");

            if (docname != null) {

              document.append("<docname>" + docname + "</docname>");

            }

            if (doctype != null) {

              document.append("<doctype>" + doctype + "</doctype>");

            }

            if (createDate != null) {

              document.append("<createdate>" + createDate + "</createdate>");

            }

            if (updateDate != null) {

              document.append("<updatedate>" + updateDate + "</updatedate>");

            }

            // Store the document id and the root node id

            docListResult.put(docid,(String)document.toString());

          }

          // Advance to the next record in the cursor

          tableHasRows = rs.next();

        }

        rs.close();

        pstmt.close();

        double docListTime =System.currentTimeMillis()/1000;

        MetaCatUtil.debugMessage("prepare docid list time: "

                                          +(docListTime-queryExecuteTime), 30);

        if (qspec.containsExtendedSQL())

        {

          Vector extendedFields = new Vector(qspec.getReturnFieldList());

          Vector results = new Vector();

          Enumeration keylist = docListResult.keys();

          StringBuffer doclist = new StringBuffer();

          Hashtable parentidList = new Hashtable();

          Hashtable returnFieldValue = new Hashtable();

          while(keylist.hasMoreElements())

          {

            doclist.append("'");

            doclist.append((String)keylist.nextElement());

            doclist.append("',");

          }

          if (doclist.length() > 0) 

          {

            Hashtable controlPairs = new Hashtable();

            double extendedQueryStart = System.currentTimeMillis()/1000;

            doclist.deleteCharAt(doclist.length()-1); //remove the last comma

            // check if user has permission to see the return field data

            String accessControlSQL = qspec.

                        printAccessControlSQLForReturnField(doclist.toString());

            pstmt = dbconn.prepareStatement(accessControlSQL);

            //increase dbconnection usage count

            dbconn.increaseUsageCount(1);

            pstmt.execute();

            rs = pstmt.getResultSet();

            tableHasRows = rs.next();

            while(tableHasRows)

            {

              long startNodeId = rs.getLong(1);

              long endNodeId = rs.getLong(2);

              controlPairs.put(new Long(startNodeId), new Long(endNodeId));

              tableHasRows = rs.next();

            }

            double extendedAccessQueryEnd = System.currentTimeMillis()/1000;

            MetaCatUtil.debugMessage("Time for execute access extended query: "

                              +(extendedAccessQueryEnd-extendedQueryStart), 30);

            String extendedQuery = qspec.printExtendedSQL(doclist.toString(), 

                                                          controlPairs);

            MetaCatUtil.debugMessage("Extended query: "+ extendedQuery, 30);

            pstmt = dbconn.prepareStatement(extendedQuery);

            //increase dbconnection usage count

            dbconn.increaseUsageCount(1);

            pstmt.execute();

            rs = pstmt.getResultSet();

            double extendedQueryEnd = System.currentTimeMillis()/1000;

            MetaCatUtil.debugMessage("Time for execute extended query: "

                                    +(extendedQueryEnd-extendedQueryStart), 30);

            tableHasRows = rs.next();

            while(tableHasRows) 

            {

              ReturnFieldValue returnValue = new ReturnFieldValue();

              docid = rs.getString(1).trim();

              fieldname = rs.getString(2);

              fielddata = rs.getString(3);

              fielddata = MetaCatUtil.normalize(fielddata);

              String parentId = rs.getString(4);

              StringBuffer value = new StringBuffer();

              if (!parentidList.containsKey(parentId))

              {

                // don't need to merger nodedata 

                value.append("<param name=\"");

                value.append(fieldname);

                value.append("\">");

                value.append(fielddata);

                value.append("</param>");

                //set returnvalue

                returnValue.setDocid(docid);

                returnValue.setFieldValue(fielddata);

                returnValue.setXMLFieldValue(value.toString());

                // Store it in hastable

                parentidList.put(parentId, returnValue);

              }

              else

              {

                // need to merge nodedata if they have same parent id ant

                // node type is text

                fielddata = (String)((ReturnFieldValue)

                       parentidList.get(parentId)).getFieldValue() +  fielddata;

                value.append("<param name=\"");

                value.append(fieldname);

                value.append("\">");

                value.append(fielddata);

                value.append("</param>");

                returnValue.setDocid(docid);

                returnValue.setFieldValue(fielddata);

                returnValue.setXMLFieldValue(value.toString());

                // remove the old return value from paretnidList

                parentidList.remove(parentId);

                // store the new return value in parentidlit

                parentidList.put(parentId, returnValue);

              }

               tableHasRows = rs.next();

            }//while

            rs.close();

            pstmt.close();

            // put the merger node data info into doclistReult

            Enumeration xmlFieldValue = parentidList.elements();

            while( xmlFieldValue.hasMoreElements() )

            {

              ReturnFieldValue object = (ReturnFieldValue)

                                         xmlFieldValue.nextElement();

              docid = object.getDocid();

              if (docListResult.containsKey(docid))

              {

                  String removedelement = (String)docListResult.remove(docid);

                  docListResult.put(docid, removedelement +

                                    object.getXMLFieldValue());

              }

              else

              {

                  docListResult.put(docid, object.getXMLFieldValue()); 

              }

            }//while

            double docListResultEnd = System.currentTimeMillis()/1000;

            MetaCatUtil.debugMessage("Time for prepare doclistresult after"+

                                      " execute extended query: "

                                    +(docListResultEnd-extendedQueryEnd), 30);

            // get attribures return

            docListResult = getAttributeValueForReturn

                                      (qspec,docListResult, doclist.toString());

          }//if doclist lenght is great than zero

        }//if has extended query

        //this loop adds the relation data to the resultdoc

        //this code might be able to be added to the backtracking code above

        double startRelation = System.currentTimeMillis()/1000;

        Enumeration docidkeys = docListResult.keys();

        while(docidkeys.hasMoreElements())

        {

          //String connstring = "metacat://"+util.getOption("server")+"?docid=";

          String connstring = "%docid=";

          String docidkey = (String)docidkeys.nextElement();

          pstmt = dbconn.prepareStatement(qspec.printRelationSQL(docidkey));

          pstmt.execute();

          rs = pstmt.getResultSet();

          tableHasRows = rs.next();

          while(tableHasRows)

          {

            String sub = rs.getString(1);

            String rel = rs.getString(2);

            String obj = rs.getString(3);

            String subDT = rs.getString(4);

            String objDT = rs.getString(5);

            document = new StringBuffer();

            document.append("<triple>");

            document.append("<subject>").append(MetaCatUtil.normalize(sub));

            document.append("</subject>");

            if ( subDT != null ) {

              document.append("<subjectdoctype>").append(subDT);

              document.append("</subjectdoctype>");

            }

            document.append("<relationship>").

                                          append(MetaCatUtil.normalize(rel));

            document.append("</relationship>");

            document.append("<object>").append(MetaCatUtil.normalize(obj));

            document.append("</object>");

            if ( objDT != null ) {

              document.append("<objectdoctype>").append(objDT);

              document.append("</objectdoctype>");

            }

            document.append("</triple>");

            String removedelement = (String)docListResult.remove(docidkey);

            docListResult.put(docidkey, removedelement + 

                              document.toString());

            tableHasRows = rs.next();

          }

          rs.close();

          pstmt.close();

        }

        double endRelation = System.currentTimeMillis()/1000;

        MetaCatUtil.debugMessage("Time for adding relation to docListResult: "+

                                (endRelation-startRelation), 30);

      } catch (SQLException e) {

        System.err.println("SQL Error in DBQuery.findDocuments: " + 

                           e.getMessage());

      } catch (Exception ee) {

        System.err.println("Exception in DBQuery.findDocuments: " + 

                           ee.getMessage());

        ee.printStackTrace(System.err);

      }

      finally 

      {

        try

        {

          pstmt.close();

        }//try

        catch (SQLException sqlE)

        {

          MetaCatUtil.debugMessage("Error in DBQuery.findDocuments: "

                                      +sqlE.getMessage(), 30);

        }//catch

        finally

        {

          DBConnectionPool.returnDBConnection(dbconn, serialNumber);

        }//finally

      }//finally

    //System.out.println("docListResult: ");

    //System.out.println(docListResult.toString());

    return docListResult;

  }

  /*

   * A method to return search result after running a query which return

   * field have attribue

   */

  private Hashtable getAttributeValueForReturn(QuerySpecification squery,

                                               Hashtable docInformationList,

                                               String docList)

  {

    StringBuffer XML = null;

    String sql = null;

    DBConnection dbconn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    int serialNumber = -1;

    boolean tableHasRows =false;

    //check the parameter

    if (squery == null || docList==null || docList.length() <0)

    {

      return docInformationList;

    }

    // if has attribute as return field

    if (squery.containAttributeReturnField())

    {

      sql = squery.printAttributeQuery(docList);

      try 

      {

        dbconn=DBConnectionPool.getDBConnection("DBQuery.getAttributeValue");

        serialNumber=dbconn.getCheckOutSerialNumber();

        pstmt = dbconn.prepareStatement(sql);

        pstmt.execute();

        rs = pstmt.getResultSet();

        tableHasRows = rs.next();

        while(tableHasRows) 

        {

          String docid = rs.getString(1).trim();

          String fieldname = rs.getString(2);

          String fielddata = rs.getString(3);

          String attirbuteName = rs.getString(4);

          XML = new StringBuffer();

          XML.append("<param name=\"");

          XML.append(fieldname);

          XML.append(QuerySpecification.ATTRIBUTESYMBOL);

          XML.append(attirbuteName);

          XML.append("\">");

          XML.append(fielddata);

          XML.append("</param>");

          tableHasRows = rs.next();

          if (docInformationList.containsKey(docid))

          {

            String removedelement = (String)docInformationList.remove(docid);

            docInformationList.put(docid, removedelement + XML.toString());

          }

          else

          {

            docInformationList.put(docid, XML.toString()); 

          }

        }//while

        rs.close();

        pstmt.close();

      }

      catch(Exception se)

      {

        MetaCatUtil.debugMessage("Error in DBQuery.getAttributeValue1: "

                                      +se.getMessage(), 30);

      }

      finally

      {

        try

        {

          pstmt.close();

        }//try

        catch (SQLException sqlE)

        {

          MetaCatUtil.debugMessage("Error in DBQuery.getAttributeValue2: "

                                      +sqlE.getMessage(), 30);

        }//catch

        finally

        {

          DBConnectionPool.returnDBConnection(dbconn, serialNumber);

        }//finally

      }//finally

    }//if

    return docInformationList;

  }

  /*

   * A method to create a query to get owner's docid list

   */

  private String getOwnerQuery(String owner)

  {

    StringBuffer self = new StringBuffer();

    self.append("SELECT docid,docname,doctype,");

    self.append("date_created, date_updated, rev ");

    self.append("FROM xml_documents WHERE docid IN (");

    self.append("(");

    self.append("SELECT DISTINCT docid FROM xml_nodes WHERE \n");

    self.append("nodedata LIKE '%%%' ");

    self.append(") \n");

    self.append(") ");

    self.append(" AND (");

    self.append(" user_owner = '" + owner + "'");

    self.append(") ");

    return self.toString();

  }

  /**

   * returns a string array of the contents of a particular node. 

   * If the node appears more than once, the contents are returned 

   * in the order in which they appearred in the document.

   * @param nodename the name or path of the particular node.

   * @param docid the docid of the document you want the node from.

   */

  public static Object[] getNodeContent(String nodename, String docid)

  {

    DBConnection dbconn = null;

    int serialNumber = -1;

    StringBuffer query = new StringBuffer();

    Vector result = new Vector();

    PreparedStatement pstmt = null;

    query.append("select nodedata from xml_nodes where parentnodeid in ");

    query.append("(select nodeid from xml_index where path like '");

    query.append(nodename);

    query.append("' and docid like '").append(docid).append("')");

    try

    {

      dbconn=DBConnectionPool.getDBConnection("DBQuery.getNodeContent");

        serialNumber=dbconn.getCheckOutSerialNumber();

      pstmt = dbconn.prepareStatement(query.toString());

      // Execute the SQL query using the JDBC connection

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next();

      while (tableHasRows) 

      {

        result.add(rs.getString(1));

        //System.out.println(rs.getString(1));

        tableHasRows = rs.next();

      }

    } 

    catch (SQLException e) 

    {

      System.err.println("Error in DBQuery.getNodeContent: " + e.getMessage());

    } finally {

      try

      {

        pstmt.close();

      }

      catch(SQLException sqle) 

      {}

      finally

      {

        DBConnectionPool.returnDBConnection(dbconn, serialNumber);

      }

    }

    return result.toArray();

  }

  /**

   * format a structured query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param params The list of parameters that should be included in the query

   */

  public static String createSQuery(Hashtable params)

  { 

    StringBuffer query = new StringBuffer();

    Enumeration elements;

    Enumeration keys;

    String filterDoctype = null;

    String casesensitive = null;

    String searchmode = null;

    Object nextkey;

    Object nextelement;

    //add the xml headers

    query.append("<?xml version=\"1.0\"?>\n");

    query.append("<pathquery version=\"1.0\">\n");

    if (params.containsKey("meta_file_id"))

    {

      query.append("<meta_file_id>");

      query.append( ((String[])params.get("meta_file_id"))[0]);

      query.append("</meta_file_id>");

    }

    if (params.containsKey("returndoctype"))

    {

      String[] returnDoctypes = ((String[])params.get("returndoctype"));

      for(int i=0; i<returnDoctypes.length; i++)

      {

        String doctype = (String)returnDoctypes[i];

        if (!doctype.equals("any") && 

            !doctype.equals("ANY") &&

            !doctype.equals("") ) 

        {

          query.append("<returndoctype>").append(doctype);

          query.append("</returndoctype>");

        }

      }

    }

    if (params.containsKey("filterdoctype"))

    {