/*
javac -classpath .:/opt/jakarta/jakarta-tomcat/shared/lib/cos.jar:/opt/jakarta/jakarta-tomcat/./common/lib/servlet.jar:/opt/jakarta/jakarta-tomcat/webapps/wsa/WEB-INF/classes/netalanmaxwell.jar:../lib/jsky.jar SSASQL.java
*/



// SQLGet class
// Programmed by Alan Maxwell
//
// Revision History
// ----------------
// v1.23: 13 Dec 2002
//   Added an option 'rowtotal' which specifies whether the result page should
//   show the total number of rows the query returned when only a subset is
//   displayed in summary...
// v1.22: 09 Dec 2002
//   Fixed TAR request, when an empty e-mail is provided it gives error in
//   addition to not having the 'email=' parameter at all...
// v1.21: 03 Dec 2002
//   Fixed the while loop to check if the 'isQueryProcessing()' variable of
//   the thread, to allow for even quicker responses...
// v1.20: 29 Nov 2002
//   Major rewrite to make use of the new Threaded SQLRetrieverThread class...
// v1.11: 28 Nov 2002
//   Minor code tidy/optimise - also added 'serviceCounter' which tracks
//   the number of concurrent threads...
// v1.10: 26 Nov 2002
//   Revised to include support for the 'tar=yes' and 'email=XXX' parameters to
//   allow the users to request a 'tar' file of all the FITs files related to
//   an object or group of objects.
// v1.00: 04 Oct 2002
//   Initial Release
//
// Synopsis
// --------
// This class implements a Java 'Servlet' web-service which allows the user
// to request execution of an SQL string to one of the (predetermined in this
// source) available SQL servers. The SQL can be returned as an HTML summary
// table, in addition to a CSV data file which can be ZIPped or GZIPped. SQL
// error messages are returned to the users if the SQL cannot be performed.
//
// This service is usually installed in a Servlet server such as Tomcat and
// then called from a web page (usually as the 'ACTION' event for a FORM).
//import Sesame;
//Author:       $Author: MikeRead $
//Checked in:   $Date: 2009-02-16 14:42:20 +0000 (Mon, 16 Feb 2009) $
// Locked by:   $Locker$
// CVS revision: $Revision: 969 $


import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.StringTokenizer;
import javax.servlet.*;
import javax.servlet.http.*;

import jsky.coords.*;
import java.awt.geom.*;
import net.alanmaxwell.html.HTMLStrings;
import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.WSASQLRetrieverThread;
import org.apache.log4j.Logger;

import uk.ac.roe.wfau.DatabaseURL;
import uk.ac.roe.wfau.HTMLFormatter;
import uk.ac.roe.wfau.QueryRetrieverThread;
import uk.ac.roe.wfau.SQLMethods;
import uk.ac.roe.wfau.SSAQueryRetrieverThread;
import uk.ac.roe.wfau.VESPAQueryRetrieverThread;
import uk.ac.roe.wfau.WSAHTMLSchema;
import uk.ac.roe.wfau.WSAJsp;
import uk.ac.roe.wfau.WSAQueryRetrieverThread;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.WSASession;
import net.mar.SQLRadial;
import net.mar.SQLRegion;
import net.mar.SQLMenu;
import net.mar.AnException;

public class WFAUSQL extends HttpServlet {  // 1
  private int serviceCurrent = 0;
  private int serviceTotal = 0;

  // Access methods for service counter...
  protected synchronized void enteringServiceMethod() { //2
    serviceCurrent++;
    serviceTotal++;
  };

  protected synchronized void leavingServiceMethod() { //3
    serviceCurrent--;
  };

  protected synchronized int getServiceCurrent() { //4
    return serviceCurrent;
  };

  protected synchronized int getServiceTotal() { //5
    return serviceTotal;
  };

  private boolean shuttingDown = false;

  // Access methods for the shuttingDown flag...
  protected synchronized void setShuttingDown(boolean flag) { //6
    shuttingDown = flag;
  };

  protected synchronized boolean isShuttingDown() { //7
    return shuttingDown;
  };


  private String dirName;
  private String listDirName;
  private String tmpWeb;
  private String server;
  private String user;
  private String passwd;
  private String publicuser;
  private String publicpasswd;
  
  private String defaultUKIDSSDatabase;
  private String defaultWorldDatabase;
  static Logger logger=Logger.getLogger("uk.ac.roe.wfau");
  static boolean useTDS=true;

  public void init(ServletConfig config) throws ServletException {

	  super.init(config);
      String path=getServletContext().getRealPath("/");
      Properties properties = new Properties();
      try {
		  properties.load(new FileInputStream(path+"WEB-INF/WSA.properties"));
	  }
	  catch (IOException e) {
		  System.out.println("ERROR: properties file not found");
		  return;
	  }

	  server = properties.getProperty("server");
	  user = properties.getProperty("user");
	  passwd = properties.getProperty("passwd");
	 // publicuser = properties.getProperty("publicuser");
	 // publicpasswd = properties.getProperty("publicpasswd");
	  defaultUKIDSSDatabase = properties.getProperty("defaultUKIDSSDatabase");
      defaultWorldDatabase = properties.getProperty("defaultWorldDatabase");     
      dirName = properties.getProperty("WSASQLTmpDir");
      listDirName=properties.getProperty("LISTSQLTmpDir");
      tmpWeb=properties.getProperty("WSASQLTmpWeb");
  }

  // Override the service method to update the serviceCounter...
  protected void service(HttpServletRequest req,
                         HttpServletResponse resp)
                         throws ServletException,IOException {

    enteringServiceMethod();

    try
    {
      super.service(req, resp);
    }
    finally
    {
      leavingServiceMethod();
    } ;
  }; // end of service

   public void destroy() {
  
       /*
       // Check to see if there are still any services running...
    if (numServices() > 0)
    {
      setShuttingDown(true);
    };

    // Wait for all services to finish...
    while (numServices() > 0)
    {
      try
      {
        Thread.sleep(100);
      }
      catch (InterruptedException e)
      {
      };
    }; */
       logger=null;
       super.destroy();
  };


  protected void doGet(HttpServletRequest req, HttpServletResponse res)
                 throws ServletException, IOException {
      HttpSession session = req.getSession();
      PrintWriter out = res.getWriter();
      res.setContentType("text/html");
      boolean loginboolean=false;
      boolean listDriven=false;
      String database;
      try {
      loginboolean=((Boolean)session.getAttribute("login")).booleanValue();
      }
      catch (Exception e){
          loginboolean=false;
      }
      String userSess=(String)session.getAttribute("user");
      String community=WSASession.getCommunity(session);
    try {
       if (server == null){
		   res.sendError(
	           res.SC_BAD_REQUEST,
	            "INVALID SQL Server. Check properties file."
	            );
			}

      // FIRST THING: Extract and verify all the provided parameters...
       
       if (req.getParameter("submit") != null) {
           if (req.getParameter("submit").equalsIgnoreCase("addToQueue")){
               
               RequestDispatcher dispatcher = req.getRequestDispatcher("DBQueue");
               dispatcher.forward(req, res);
               return;
           }
       }
       
       
       
      SQLServerInfoJDBC SQLServer = null;
      String SQLQueryString = "";
      String ra=null;
      String dec=null;
      String ra2000=null;
      String dec2000=null;
      String radius="";
      String name=null;
      String sys=null;
      String select=null;
      String action=null;
      String where=null;
      String from=null;
      String aper=null;
      int tableid=0;
      int timeout=0;
      String baseURL = "";
      String resultsPageHeading = "WSA SQL Query Results";
     
      int rowsHTML = QueryRetrieverThread.SQLROWS_DEFAULT;
      boolean doRowTotal = true;

      int OutputFormat = QueryRetrieverThread.OUTPUT_NONE;
      int OutputCompression = QueryRetrieverThread.COMPRESS_NONE;
      boolean errorsPresent=false;
      StringBuffer errorBuffer=new StringBuffer("");
      boolean do6df = false;
      boolean useHMS = true;
      boolean doTAR = false;
      String emailTAR = null;
      String emailAddress=null;
      boolean fg=true;
      boolean showSQL=true;
      Statement retrieverStmt=null;
      // First parameter needed is the server name, without it: duh! no data
 
      /*
      if (loginboolean){
      database=defaultUKIDSSDatabase;
	  SQLServer = new SQLServerInfoJDBC(
//			  server, database, user, passwd,"11013444"
	          server, database, user, passwd,true
			            );
      }
      else {
          database=defaultWorldDatabase;
    	  SQLServer = new SQLServerInfoJDBC(
    			  server, database, publicuser, publicpasswd,"11013442"
    			            );
      }
      */
      String archive=req.getParameter("archive");
      database=req.getParameter("database");
      
      String loggedInDatabase=defaultUKIDSSDatabase;
      String worldDatabase=defaultWorldDatabase;

      if (database == null) {
          
          if (community.equalsIgnoreCase("nonSurvey")) {            
              loggedInDatabase = userSess;
          } else {
              if (loginboolean) {
                  
                  loggedInDatabase = defaultUKIDSSDatabase;
              } else {
                  
                  worldDatabase = defaultWorldDatabase;
              }
          }
      } else {
          
          loggedInDatabase = database;
          worldDatabase = database;
      }
      
      
      
     // session.setAttribute("login", Boolean.FALSE);
      String dbURL=DatabaseURL.getDatabaseURL(session,useTDS,server,loggedInDatabase,worldDatabase);
      
      if (req.getParameter("sqlstmt") != null) {
          logger.info("WSASQL,DEBUG,stmt not null");
         // if (req.getParameter("sqlstmt").matches("(?i).*bestDR3\\..*")) {
              if (req.getParameter("sqlstmt").toLowerCase().indexOf("bestdr3..") > 0 ||
                  req.getParameter("sqlstmt").toLowerCase().indexOf("seguedr6..") > 0 ||
                  req.getParameter("sqlstmt").toLowerCase().indexOf("thutmose..") > 0 ||
                  req.getParameter("sqlstmt").toLowerCase().indexOf("bestdr5..") > 0 ||
                  req.getParameter("sqlstmt").toLowerCase().indexOf("bestdr7..") > 0 ||
                  req.getParameter("sqlstmt").toLowerCase().indexOf("bestdr2..") > 0) {
              logger.info("WSASQL,DEBUG,stmt index matches bestdr2..3.. or 5 using thutmose");
              //logger.error(""+WSASchema.getVersionNoOfRelease("ukidssedr"));
              //logger.error(""+WSASchema.getVersionNoOfRelease("ukidssr1	"));
              
              dbURL=DatabaseURL.getDatabaseURL(session,useTDS,"thutmose",loggedInDatabase,worldDatabase);   
          }
      }
      
      
  
      

		   baseURL= "http://surveys.roe.ac.uk/wsa/";
		   resultsPageHeading = "WSA Database - SQL Query Results";

		   

      // If we haven't been given a server, there's no point continuing...
    /*  if (SQLServer == null)
      {
        res.sendError(
          res.SC_BAD_REQUEST,
          "You must provide the name of a VALID SQL Server as parameter 'server'."
        );
        return; // Finish this method immediately...
      }
      else
      {
      */

      if (req.getParameter("radius") != null)
          {
            radius = req.getParameter("radius");
          };
      if (req.getParameter("ra") != null)
          {
            ra = req.getParameter("ra");
          };
      if (req.getParameter("dec") != null)
          {
            dec = req.getParameter("dec");
          };
      if (req.getParameter("name") != null)
	      {
	        name = req.getParameter("name");
          };
      if (req.getParameter("sys") != null)
	      {
	        sys = req.getParameter("sys");
          };
          if (req.getParameter("timeout") != null)
	      {
            try { 
	        timeout = Integer.valueOf(req.getParameter("timeout")).intValue();
            }
            catch (Exception e) {
                errorsPresent=true;
                errorBuffer.append("Unable to parse timeout value<p>");
            }
          };
      if (req.getParameter("select") != null)
	      {
	        select = req.getParameter("select");
          };
      if (req.getParameter("from") != null)
	      {
	        from = req.getParameter("from");
          };
      if (req.getParameter("where") != null)
	      {
	        where = req.getParameter("where");
          };

      if (req.getParameter("formaction") != null)
	      {
	        action = req.getParameter("formaction");
          };
/*
          try {
              System.out.println("mikee");
              SQLRegion sqlReg=new SQLRegion();
              sqlReg.setRequest(req);
          }
          catch (AnException ae){
              System.out.println(ae.getMore());
          }
*/

    if (action.equalsIgnoreCase("region"))
      {
        showSQL=false;
		    SQLRegion sqlr = new SQLRegion();
		   
             
		    try {
		       SQLQueryString=sqlr.setRequest(req,session,database);
		       from=sqlr.getFrom();
		       ra2000=sqlr.getRA();
            dec2000=sqlr.getDec();
            sys=sqlr.getSys();
            radius=sqlr.getRadius();
		      }
		      catch (AnException ae)
		      { String err=ae.getMessage();
	
		             out.println("Errors present, please go BACK and check input.<p>"+err);
		             out.flush();
		             out=null;
		             res.flushBuffer();
	      return;
		  }
		      catch (Exception e) {
		          System.out.println(e);
		      }
		      
		}
    
    if (action.equalsIgnoreCase("listSQL"))
    {
        listDriven=true;
      showSQL=true;
      
      String emailAddress2=null;
      String addSelect="";
      
      StringBuffer querySB=new StringBuffer("select top 1000 "+WSAJsp.LISTDRIVENBASESELECT);
      
      if (req.getParameter("aper") != null ) {
          aper=req.getParameter("aper");
      }
      
      for (int i=0; i < WSAJsp.LISTDRIVENFILTERARRAY.length; i++){
          querySB.append(", "+WSAJsp.LISTDRIVENFILTERARRAY[i]+aper);
               }
      if (req.getParameter("addSelect") != null ) {
          addSelect=req.getParameter("addSelect").trim();
      }
      if (req.getParameter("from") != null ) {
          from=req.getParameter("from").trim();
      }
      if (req.getParameter("where") != null ) {
          where=req.getParameter("where").trim();
      }
      if (addSelect != null && !addSelect.equals("")) {
          if (addSelect.startsWith(",")) {
              querySB.append(addSelect);
          }
          else {
              querySB.append(", "+addSelect);
          }
      }
      if (from.equalsIgnoreCase("sourceView")) {
          querySB.append(" from "+WSASchema.getSourceViewName(WSASchema.UKIDSSLASPROGRAMMEID));
      }
      else {
          querySB.append(" from "+WSASchema.getSourceTableName(WSASchema.UKIDSSLASPROGRAMMEID));
      }
      
      if (where != null && !where.equals("")) {
          if (where.startsWith("where")) {
              querySB.append(" "+where);
          }
          else {
              querySB.append(" where "+where);
          }
      }
      
 //     querySB.append(" from "+from);
      if (req.getParameter("emailAddress") != null ) {
          emailAddress=req.getParameter("emailAddress").trim();
      }
      if (req.getParameter("emailAddress2") != null ) {
          emailAddress2=req.getParameter("emailAddress2").trim();
      }
      
      
      if (!emailAddress.equals(emailAddress2) || emailAddress.equals("")) {
          errorBuffer.append("<p>No email address supplied or addresses do not match<p>");
          errorsPresent=true;
      }
//          out.print(querySB.toString()); 
		    
		       SQLQueryString=querySB.toString(); 
	   	
		      
		}


      if (action.equalsIgnoreCase("menu"))
      {
		    SQLMenu sqlm = new SQLMenu();

		    try {
		       SQLQueryString=sqlm.setRequest(req);
		      }
		      catch (AnException ae)
		      { String err=ae.getMessage();
		             res.sendError(
			  	     res.SC_BAD_REQUEST,err
			  	             );
	      return;
		  }
		}


      if (action.equalsIgnoreCase("freeform"))
      {
		        if (req.getParameter("sqlstmt") != null)
		  	      {
		  	        SQLQueryString=req.getParameter("sqlstmt");
		  	        if (SQLQueryString.length() < 10000) {
		  	        session.setAttribute("SQLStmt",SQLQueryString);
		  	        }
		  	        else {
		  	          session.setAttribute("SQLStmt",null);
		  	        }
          };
	  }
      
	  

        if (SQLQueryString == null)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "No SQL query was provided as parameter 'sql'."
          );
          return; // Finish this method immediately...
        }
        /*
        else if (SQLQueryString.length() > 4096)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "The SQL query parameter 'sql' cannot exceed a length of 4096 characters."
          );
          return;
        }
        */
        else {
            String parsedSQLQuery;
            try {
            parsedSQLQuery=SQLMethods.parseSQL(SQLQueryString);
            }
            catch (Exception e) {
                res.sendError(
                        res.SC_BAD_REQUEST,e.toString()
                       
                      );
                      return;
            }
            


	      if (req.getParameter("emailAddress") != null && !req.getParameter("emailAddress").equals(""))
	      {
	        emailAddress = req.getParameter("emailAddress");
             if (
                   ( emailAddress.length() < 3) ||
                   (emailAddress.indexOf("@") == -1)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "You must also include a VALID e-mail address  " +
                  "(your emailAdddress= parameter seems to be empty or " +
                  "incorrect) "
                );
                return;
			}
		  }
		  else
		  {
			  emailAddress=null;
		  }




          // Do they want to return a special number of ROWS?
          if (req.getParameter("rows") != null)
          {
            try
            {
            	rowsHTML = Integer.parseInt(req.getParameter("rows"));
            }
            catch (NumberFormatException e)
            {
              rowsHTML = QueryRetrieverThread.SQLROWS_DEFAULT;
            };

            if (rowsHTML < 0)
            {
              rowsHTML = 0;
            };
          };

          // When more rows are returned than displayed in summary,
          // does the user want to know the total rows returned?
          // Default, yes, takes slightly more time to run...
          if (req.getParameter("rowtotal") != null)
          {
            if ( req.getParameter("rowtotal").equalsIgnoreCase("N") ||
                 req.getParameter("rowtotal").equalsIgnoreCase("NO") )
            {
              doRowTotal = false;
            }
          }

          // What about compression?
          if (req.getParameter("compress") != null)
          {
            String compressParam = req.getParameter("compress");

            if (compressParam.equalsIgnoreCase("ZIP"))
            {
              OutputCompression = QueryRetrieverThread.COMPRESS_ZIP;
            }
            else if (compressParam.equalsIgnoreCase("GZIP"))
            {
              OutputCompression = QueryRetrieverThread.COMPRESS_GZIP;
            }
            else
            {
              OutputCompression = QueryRetrieverThread.COMPRESS_NONE;
            };
          };

          // Any special format (in addition to HTML) requested?
          if (req.getParameter("format") != null)
          {
            String formatParam = req.getParameter("format");

            if (formatParam.equalsIgnoreCase("CSV"))
            {
              OutputFormat = QueryRetrieverThread.OUTPUT_CSV;
            }
            else

            if (formatParam.equalsIgnoreCase("FITS"))
            {
			  OutputFormat = QueryRetrieverThread.OUTPUT_FITS;
		    }
            else
            if (formatParam.equalsIgnoreCase("VOT"))
			            {
						  OutputFormat = QueryRetrieverThread.OUTPUT_VOT;
		    }
		    else
            {
              OutputFormat = QueryRetrieverThread.OUTPUT_NONE;
            };
          };

          // Check for an e-mail param (for TAR below)...
          if (req.getParameter("email") != null)
          {
            emailTAR = req.getParameter("email");
          };

          // Do we have to generate a TAR set? (Need e-mail param too!)
          if (req.getParameter("tar") != null)
          {
            String tarParam = req.getParameter("tar");

            if ( tarParam.equalsIgnoreCase("Y") ||
                 tarParam.equalsIgnoreCase("YES") )
            {
              if (
                   (emailTAR == null) ||
                   (OutputFormat != QueryRetrieverThread.OUTPUT_CSV)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "When using the 'tar=yes' parameter to obtain a TAR file " +
                  "you must also include an e-mail address with the 'email=' " +
                  "parameter and ensure that the 'format=CSV' parameter is set " +
                  "(to generate a CSV file)!"
                );
                return;
              };

              if (
                   (emailTAR.length() < 3) ||
                   (emailTAR.indexOf("@") == -1)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "When using the 'tar=yes' parameter to obtain a TAR file " +
                  "you must also include a VALID e-mail address with the 'email=' " +
                  "parameter (your email= parameter seems to be empty or " +
                  "incorrect) and ensure that the 'format=CSV' parameter is " +
                  "set (to generate a CSV file)!"
                );
                return;
              }

              doTAR = true;
            };
          }

          // Now we should have everything we need, attempt to get the data...

          // Connect to the database and attempt to execute the provided SQL
          // Set the content type and then formulate the normal response...

          

          //PrintWriter out = null;
          QueryRetrieverThread retrieverThread = null;
          Thread runner = null;

          try
          {
            out = res.getWriter();
            //out.println(req.getParameter("submit"));
            if (errorsPresent) {
                out.print(errorBuffer);
                return;
            }
            
            // Prepare the heading of the HTML document:
            
  
                out.println(
                  HTMLStrings.getHTMLHeadWithScript(
                    resultsPageHeading,
                    baseURL + "configurestyles.js","onLoad=\"swap(0);\""
//                    baseURL + "configurestyles.js","onload=\"hideLoadingPage()\""
                  )
                );
                /*

                out.println("<div id=\"hidepage\" style=\"position: absolute; left:0px; top:0px; background-color: #FFFFFF; layer-background-color: #FFFFFF; height: 100%; width: 100%; visibility:hidden\"> ");
                out.println("<table width=\"100%\" height=\"100%\"><tr><td align=\"center\" valign=\"middle\"> ");
                out.println("<table width=\"50%\" align=\"center\" style=\"border: 1 #666666 solid\"><tr><td align=\"center\"> ");
                out.println("<img src=\"http://eurovotech.org/wfau/logos/logos/wfau_matrix_white_edge.gif\"></p><br /> </td></tr></table> </td></tr></table> </div> ");
                */
                out.println("<img name=\"imgMain\" src=\""+WSAHTMLSchema.baseURL+"static.gif\" border=\"1\"><p>");

                out.flush();
           

            out.println(
              "    <h1>" + resultsPageHeading + "</h1> \n"
            );
       //     out.println(":"+SQLQueryString);
            if (OutputFormat != QueryRetrieverThread.OUTPUT_NONE && !listDriven)
            {
              out.println(
                "    <p> \n" +
                "      Data file generating queries can take a bit longer to execute \n" +
                "      as they write to a file ALL rows returned by the query. \n" +
                "    </p> \n"
              );

              out.println(
                "    <p> \n" +
                "      A web link to your generated output file will appear at the \n" +
                "      bottom of this page. \n" +
                "    </p> \n"
              );
            };

     
            out.println(
              "    <p>"
            );
            
                //out.println("<b>Connecting to "+database+" database</b><br>");
            String dbName=DatabaseURL.getDatabaseNameFromURL(dbURL,useTDS);
                out.println("<b>Connecting to "+dbName+" database</b><br>");
            
                
            out.print(
              "      <b>QUERY STARTED:</b> " +
                Calendar.getInstance().getTime() +
                "&nbsp;&nbsp;&nbsp;[" + getServiceCurrent() + " active, " +
                getServiceTotal() + " total] \n"
            );

            out.println(
              "    </p> \n"
            );

            out.println(
              "    <p> \n" +
              "      Please keep this browser window open and wait for your results or further information to	 appear below... \n" +
              "    </p> \n"
            );

            res.flushBuffer();

            // THIS SECTION WILL DO THE QUERY:


             // retrieverThread = new QueryRetrieverThread(SQLServer); // Server we setup earlier...
            
            if (archive != null && archive.equalsIgnoreCase("WSA")) {
                retrieverThread = new WSAQueryRetrieverThread(dbURL);
            }
            else if (archive != null && archive.equalsIgnoreCase("VESPA")) {
                retrieverThread = new VESPAQueryRetrieverThread(dbURL);
                retrieverThread.setDoSexagesimal(true);
            }
            else if (archive != null && archive.equalsIgnoreCase("SSA")) {
                retrieverThread = new SSAQueryRetrieverThread(dbURL);
                retrieverThread.setDoSexagesimal(true);
            }
            else {
            retrieverThread = new QueryRetrieverThread(dbURL);
            }
            retrieverThread.setDatabaseName(dbName);
            
             retrieverThread.setOutputBaseDIR(
//                "/home/6df/public_html/"
				dirName
              );
             
             if (listDriven) {
                 retrieverThread.setOutputBaseDIR(listDirName);
             }
             
              retrieverThread.setOutputBaseURL(
//                "http://www-wfau.roe.ac.uk/6dFGS/"
                   tmpWeb
              );
              retrieverThread.setOutputAppendage(
                "tmp_sql/"
              );
              //SQLQueryString = SQLQueryString.replaceAll("\r?\n","");
             

            // Pass through any parameters to the retriever...
             String IPAddress=req.getRemoteAddr();
            logger.info("WSASQL,Login,"+(String)session.getAttribute("user")+","+(String)session.getAttribute("community")+","+IPAddress);
            logger.info("WSASQL,URL,"+dbURL+","+IPAddress);
            logger.info("WSASQL,Query,"+SQLQueryString.replaceAll("\n"," ").replaceAll("\r"," ")+","+IPAddress); 
            retrieverThread.setIPAddress(IPAddress);
            retrieverThread.setUser(WSASession.getUser(session));
            retrieverThread.setSQLQuery(parsedSQLQuery);
            retrieverThread.showSQL=showSQL;
            retrieverThread.doCancelThread=true;
            retrieverThread.updateWebqueries=true;
            if (timeout > 0) {
            timeout = Math.min(timeout,QueryRetrieverThread.MAXTIMEOUT);
            retrieverThread.timeout=timeout;
            out.print("timeout: "+timeout +"<p>");
            
            }
            //retrieverThread.setSQLQuery("EXEC dbo.spWebExecuteSQL '"+SQLQueryString+" ','"+
              //      (String)session.getAttribute("user")+"','"+IPAddress+"'");
            //retrieverThread.setSQLQuery(SQLQueryString);
            retrieverThread.setRowsHTML(rowsHTML);
            retrieverThread.setDoRowTotal(doRowTotal);
            retrieverThread.setOutputFormat(OutputFormat);
            retrieverThread.setOutputCompression(OutputCompression);
            if ( emailAddress != null)
            {
				retrieverThread.setMightBeBackground(true);
			}









            // Start a separate thread running to retrieve the SQL...
            runner = new Thread(retrieverThread);
            retrieverStmt=retrieverThread.setConnection();
            
            

            if (retrieverStmt != null) {
                out.println("Connected to database");
                if (listDriven) {
                    try {
                    SQLMethods.checkValidSQL(retrieverStmt,SQLQueryString);
                    retrieverThread.setMightBeBackground(true);
                    fg=false;
                    retrieverThread.setEmailAddress(emailAddress);
                    retrieverThread.setListDriven(aper,"http://horus.roe.ac.uk:8080/wsa/listDrivenSQL.jsp");
                    }
                    catch (SQLException se) {
                        out.print("<p><b>SQL ERROR: </b> "+se.getMessage());
                        out.print("<p><b>SQL Query: </b> "+SQLQueryString);
                        out.print("<p>go BACk and check inputs");
                        retrieverThread.closeConnection();
                       
                        return;
                    }
                }
            runner.start();
            }
            else {
                out.println("<h3>Unable to connect to database</h3><br>Please try again at a later time, if the problem persists and there is nothing mentioned on the downtime page send an " +
                		"email to mar@roe.ac.uk");
            }
            if (showSQL) {
                out.println("<p>Submitted query: "+HTMLFormatter.forHTMLTag(parsedSQLQuery)+"<p>");
            }

    // Calls the the resolving method with the command line argument


          if (action.equalsIgnoreCase("region")) {
               out.println("<br>Using "+from);
		        out.println(" RA/Long.: " + ra2000 + " &nbsp;DEC/Lat: " + dec2000 +
		        " &nbsp; Coord. Sys (<b>B</b>1950,<b>J</b>2000 or <b>G</b>alactic): "+ sys);
			}

            out.println("    <p>");

            int dotCount = 0;
           
            out.println("<script language=\"JavaScript\" type=\"text/javascript\"> <!-- ");
            out.println("swap(1);");
            out.println("--> </script>");
            out.flush();
           /* out.println("<script language=\"JavaScript\" type=\"text/javascript\"> <!-- ");
            out.println("showLoadingPage();");
            out.println("--> </script>");*/
            while (
                    (!isShuttingDown()) &&
                    (runner.isAlive()) &&
                    (retrieverThread.isQueryProcessing() && fg == true)
                  )
            {
	             if ( emailAddress != null && dotCount >= 27 )
	             {
					 retrieverThread.setEmailAddress(emailAddress);
					 fg = false;
				  }
              try
              {
                Thread.sleep(3000);
              }
              catch (InterruptedException ie)
              {
                // Do nothing when interrupted...
              }

              out.print(
                "&#8226;&#8226;&#8226;<!--                                         " +
                "                                                           -->"
              );

              res.flushBuffer();

              //dotCount++;
                dotCount=dotCount+3;
              if ((dotCount % 30) == 0)
              {
                out.println(" "+
                   retrieverThread.getRowsReturned() + " rows returned so far <br>"
                );

                //dotCount = 0;
              }
            }


			if (fg || !retrieverThread.isQueryProcessing()) {

            fg=true;
			out.println("      OK");
            out.println("    </p> \n");

            retrieverThread.setQueryTerminated(true);
            retrieverThread.setMightBeBackground(false);
            out.println(retrieverThread.getResultsHTML());

            out.println(
              "    <p> \n" +
              "      <b>QUERY FINISHED:</b> " + Calendar.getInstance().getTime() + " \n" +
              "    </p> \n"
            );
            out.println("<script language=\"JavaScript\" type=\"text/javascript\"> <!-- ");
            out.println("swap(0);");
            out.println("--> </script>");
            out.println(
              "    <p> \n" +
              "      Click your browsers 'BACK' button to try another query...\n" +
              "    </p> \n"
            );

            if (action.equalsIgnoreCase("radial")) {
				out.println(
					"<form action=\"pixel.jsp?ra="+ra2000+"&dec="+dec2000+
					"&radius="+radius+"&sys="+sys+"\" method=\"post\">" +
					"<input class=\"FontSans\" type=\"submit\" value=\"getAreaImage\" /> </form> "+
					"Click the getAreaImage button to retrieve an image of the searched area"
					);
				}


            out.println(
              HTMLStrings.getHTMLTail(baseURL + "browserversion.js")
            );

			}
			else
			{
				out.println("<p> Query running moved to background. "+
								"On completion you will be informed where to retrieve your results by email to <b>"+
				emailAddress+"</b><p> Click your browsers 'BACK' button to return to the WSA pages. </body></html>");

			}




            // QUERY DONE!
          }
          finally
          {
            // Free up resources...
//            System.out.println("here");
//            File fooFile = new File("/home/6df/tada.txt");
//            fooFile.createNewFile();
//				File fooFile = new File("/home/avo/mar/tada.txt");
 //           fooFile.createNewFile();

            if (fg == true) {

            	if (retrieverThread != null)
            	{
					/*
					if (retrieverThread.isQueryProcessing()){
						System.out.println("resorting to close");
						retrieverThread.setDBConnection();
					}
                    */
            	    if (retrieverThread.isQueryProcessing()){
            	       
            	        logger.info("WSASQL,CANCEL,cancelling");
            	        try {
            	        retrieverStmt.cancel();
            	        retrieverStmt=null;
            	    }
            	    catch (Exception se) {
            	        System.out.println(	se);
            	    }
            	    }

            	  retrieverThread.setQueryTerminated(true);
            	  //System.out.println("stopped");
            	};

			}

				runner = null;
        	    retrieverThread = null;

            if (out != null)
            {
              out.close();
            };

            out = null;
            logger.info("WSASQL,CLOSED");
          }; // try..finally
        }; // end of valid SQL string
      //}; // end of valid server if (SQLServer == null) else...
    } // end of initail try or doGet
    catch (Exception e)
    {
/*       
      res.sendError(
        res.SC_INTERNAL_SERVER_ERROR,
        e + ""
      );
      */
      return;
    };
  }; // END of doGet()

  protected void doPost(HttpServletRequest req, HttpServletResponse res)
    throws ServletException, IOException
  {
    // Data received by an HTTP POST is handled the same way
    // as data sent with a HTTP GET request... simply pass on to above...
    doGet(req, res);
  }; // END of doPost()

  public String getServletInfo() {
    return "SQLGet v1.20 by Alan Maxwell";
  }; // END of getServletInfo()
};
