[Babeldoc-devel] babeldoc/modules/sql/src/com/babeldoc/sql/pipeline/stage SqlQueryPipelineStage.java,NONE,1.1

[<jon...@us...>]

Update of /cvsroot/babeldoc/babeldoc/modules/sql/src/com/babeldoc/sql/pipeline/stage
In directory sc8-pr-cvs1:/tmp/cvs-serv19573/src/com/babeldoc/sql/pipeline/stage

Added Files:
	SqlQueryPipelineStage.java 
Log Message:
Added SqlQueryPipelineStage, and fixed a couple of bugs in the GenericSQL and Oracle journals.


--- NEW FILE: SqlQueryPipelineStage.java ---
package com.babeldoc.sql.pipeline.stage;

import com.babeldoc.core.I18n;
import com.babeldoc.core.LogService;
import com.babeldoc.core.NameValuePair;
import com.babeldoc.core.option.ComplexConfigOptionType;
import com.babeldoc.core.option.ConfigOption;
import com.babeldoc.core.option.IConfigOptionType;
import com.babeldoc.core.pipeline.PipelineException;
import com.babeldoc.core.pipeline.PipelineStage;
import com.babeldoc.core.pipeline.PipelineStageInfo;
import com.babeldoc.core.pipeline.PipelineStageResult;
import com.babeldoc.core.pipeline.PipelineDocument;
import com.babeldoc.core.resource.IResource;
import com.babeldoc.core.resource.ResourceException;
import com.babeldoc.core.resource.ResourceFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;

/**
 * Make an XML document out of a SQL query
 **/
public class SqlQueryPipelineStage
    extends PipelineStage {
  /**
   * constants
   */
  public final static String SQL = "sql";
  public final static String RESOURCE_NAME = "resourceName";
  
  //Connection pool
  private IResource resource = null;

  /**
   * Construct with this stages info
   */
  public SqlQueryPipelineStage() {
    super(new PipelineStageInfo() {
      public String getName() {
        return "SqlQuery";
      }

      public String getDescription() {
        return I18n.get("sql.900");
      }

      public Collection getTypeSpecificOptions() {
        ArrayList options = new ArrayList();
        options.add(new ConfigOption(RESOURCE_NAME, IConfigOptionType.STRING,
            null, true, I18n.get("sql.901")));

        IConfigOptionType queries = new ComplexConfigOptionType( new ConfigOption[] {
          new ConfigOption(SQL, IConfigOptionType.MULTI, null, false, I18n.get("sql.902"))
        });

        options.add(new ConfigOption(SQL, queries,
            null, false, I18n.get("sql.106")));

        return options;
      }
    });
  }

  /**
   * Heart of pipeline stage processing
   *
   * @return set of results from the processing
   */
  public PipelineStageResult[] process() throws PipelineException {

    // Check that the resource is set
    if (! hasOption(RESOURCE_NAME))
    {
      throw new PipelineException(I18n.get("sql.201"));
    }

    NameValuePair[] queries = this.getOptionList(new String[]{SQL});
    
    StringBuffer xml = new StringBuffer();
    xml.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
    xml.append("<queryresults>\n");
    
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    String sql = null;
    try {
       conn = getConnection();
       
       // Iterate through sqls
       if ((queries != null) && (queries.length > 0)) {
         for (int i = 0; i < queries.length; ++i) {
           String queryName = queries[i].getName();
           xml.append("<" + queryName + ">\n");
           sql = queries[i].getValue();
           stmt = conn.createStatement();
           LogService.getInstance().logDebug("Executing " + sql);
           rs = stmt.executeQuery(sql);  
           ResultSetMetaData metaData = rs.getMetaData();           
           while (rs.next()) {
              xml.append("<row>\n");
              for (int j = 1, count = metaData.getColumnCount(); j <= count; j++) {
                 String name = metaData.getColumnLabel(j).toLowerCase();
                 Object value = rs.getObject(j);                 
                 xml.append("<" + name + ">");
                 xml.append(value);
                 xml.append("</" + name + ">\n");                 
              }
              xml.append("</row>\n");              
           }
           xml.append("</" + queryName + ">\n");
         }
       }
    }
    catch (ResourceException e) {
      throw new PipelineException(I18n.get("sql.103"), e);
    } catch (SQLException e) {
      throw new PipelineException(I18n.get("sql.104", sql), e);
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception ignoreThis) {
      }

      try {
        if (stmt != null) {
          stmt.close();
        }
      } catch (Exception ignoreThis) {
      }

      try {
        if (conn != null) {
          resource.checkIn(conn);
        }
      } catch (Exception ignoreThis) {
      }
    }
            
    xml.append("</queryresults>\n");
    
    PipelineDocument newDoc = new PipelineDocument(this.getDocument(), xml.toString().getBytes());
    newDoc.setMimeType("text/xml");
    
    return super.processHelper(newDoc);
  }

  
  /**
   * Get the named resource name from the resource factory
   *
   * @return
   * @throws PipelineException
   * @throws ResourceException
   */
  private Connection getConnection()
      throws PipelineException, ResourceException {
    if (resource == null) {
      resource = ResourceFactory.getResource(this.getOptions(RESOURCE_NAME));
    }
    if (resource == null)
    {
      throw new ResourceException(I18n.get("sql.103"));
    }

    return ((Connection) resource.checkOut());
  }
}