// SQLRetrieverThread class
// Programmed by Alan Maxwell
//
// Revision Historyt
// ----------------
// v1.21: 13 Dec 2002
//   Added an option 'doRowTotal' which specifies whether the result page should
//   show the total number of rows the query returned when only a subset is
//   displayed in summary... access via get/set methods...
// v1.20: 28 Nov 2002
//   Renamed the class from SQLRetriever to SQLRetrieverThread!
//   Rewrite the class to implement the 'runnable' interface, allowing this
//   class to run as a separate thread. This will allow the SQL retrieval
//   to proceed in the background while the user/gui gets on with other
//   things.
//   The controlling class can run in a while loop and test for
//   'isQueryFinished()' periodically. The query can ended prematurely by
//   calling 'stopQuery()'.
// v1.05: 30 Oct 2002
//   Worked on a 'formatter' array which takes an array of SQLDataFormatter
//   objects for each of the columns describing how to format them properly.
//   This 'formatting' array is built up ONCE (as the heading row is formed)
//   with a call to getSQLDataFormatterArray(). Then, as we iterate through the
//   returned rows, we can refer to the format 'lookup table' and return the
//   nicely formatted data...
//
// v1.02: 25 Oct 2002
//   Removed the CSVSafeString() function which checks that strings have no
//   commas or quotes in them - Mike will make sure that the SQL database
//   does not have these in the data...
//
// v1.01: 15 Oct 2002
//   Revised the constructor to accept the new SQLServerInfoJDBC class which
//   more tightly and neatly encompasses all the info associated with a JDBC
//   SQLServer
//
// v1.00: 03 Oct 2002
//   Initial Release
//
// Synopsis
// --------
// This class allows the user to specify details of an MS SQL Server database
// and then execute SQL queries on that database (assuming a successful
// connection can be established) to return the results as an neat HTML
// document, with the query results returned as a neatly formatted table.
//
// The user can apply special formatting to specific data columns by
// implementing a subclass of this object, and overriding the formatColumnData()
// method to return the desired (string) representation of the data...

package net.alanmaxwell.sql;

import org.apache.log4j.Logger;

import uk.ac.roe.wfau.DatabaseConnection;
import uk.ac.roe.wfau.FormatLines;
import uk.ac.roe.wfau.HTMLFormatter;
import uk.ac.roe.wfau.SQLMethods;
import uk.ac.roe.wfau.ShowCols;
import uk.ac.roe.wfau.StringToRADec;
import uk.ac.roe.wfau.WSAHTMLSchema;
import uk.ac.roe.wfau.WSASchema;

import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.util.*;
import java.util.zip.*;
import java.util.StringTokenizer;

//import FormatRS.*;
import java.sql.*;

import javax.servlet.http.*;

import net.mar.ACSVWriter;
import net.mar.CancelThread;
import net.mar.Format;
import net.mar.FormatRS;
import net.mar.ParseCoords;
import net.mar.ParseVOT;
import net.mar.FITSWriter;
import net.mar.RetrieverMapper;
import net.mar.UCDManager;
import net.mar.RetrieverManager;
import net.alanmaxwell.html.HTMLStrings;

import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.SQLDataFormatter;

import javax.mail.*;
import javax.mail.internet.*;

/**
 * After providing the correct initialisation, the run() method call be called
 * either specifically or as a separate thread to have the SQL query executed.
 * The value of isQueryFinished() can be repeatedly tested to see if the query
 * has completed before calling getResultsHTML() to return the HTML result of
 * the query (usually a results table with pre/post amble but sometimes an error
 * message).
 * 
 * @author Alan Maxwell
 */
public class WSASQLRetrieverThread implements Runnable {
    // Some constants...
    public static final int SQLROWS_DEFAULT = 30; // Default returned # table
                                                  // rows

    public static final int HTMLROWS_UPPERLIMIT = 1000; // Maximum EVER returned
                                                      // HTMLrows..
    
    public static final int MAXHTMLCELLS=10000;
    public static final int MAXRESBUFFERSIZE=1024*1024;

    public static final int ROWSWRITTEN_UPPERLIMIT = 30000000;
    public static final int FACTOR = 2; // total rows cycle through = factor *
                                        // rowswrittenupperlimit
    public static Logger logger = Logger.getLogger("wsa.simple");
    public static final int MAXTIMEOUT = 14400; // maximum timeout in seconds 
    
    public static final int OUTPUT_NONE = 0;

    public static final int OUTPUT_CSV = 1;

    public static final int OUTPUT_FITS = 2;

    public static final int OUTPUT_VOT = 3;

    public static final int COMPRESS_NONE = 0;

    public static final int COMPRESS_ZIP = 1;

    public static final int COMPRESS_GZIP = 2;

    public String uploadFile = null;

    public BufferedReader uploadBufferedReader;

    public static final int OUTPUT_BUFFERSIZE = 524288; // .5Meg file buffer!

    private static UCDManager ucdMgr;

    private static RetrieverManager retMgr;

    private static RetrieverMapper retMap;

    // Important vars that are accessed by several functions, not synchronized!
    protected Connection databaseConnection = null;

    protected Statement databaseQuery = null;

    protected ResultSet results = null;

    protected ResultSetMetaData resultsMetaData = null;

    protected int row = 0;

    protected int rowsTotalUpperLimit = 0;

    protected int rowsWrittenUpperLimit = 0;

    protected int rowsWrittenToFile = 0;
    
    protected int rowsWrittenToHTML = 0;

    protected int resultsNumColumns = 0;
    
    String database="database";
    
    protected String columnData = "";
    
    String resultsURL="";
    
    int qID=-1;
    
    boolean AnySQLErrors=false;
    
    public String databaseURL=null;
    
    public static String [] showMFArr={"multiframeid","filename","numdetectors","compfile"};
    public static String [] showGIFArr={"framesetID","ra","dec"};
    public static String [] showGIMArr={"multiframeID","ra","dec"};
    public boolean doShowMF=true;
    public boolean doShowGIF=true;
    public boolean doShowGIM=true;
    boolean showMF=false;
    boolean showGIF=false;
    boolean showGIM=false;
    boolean listDriven=false;
    String listDrivenAper=null;
    String listDrivenURL=null;
    String listDrivenRes=null;
    public boolean showSQL=false;
    public String extraHTMLMsg="";
    public boolean doCancelThread=false;
    
    public int timeout=MAXTIMEOUT;
    
    public boolean updateWebqueries=false;

    protected String[] columnNames = null;

    protected SQLDataFormatter[] columnFormats = null;

    FormatRS frs = new FormatRS();
    FITSWriter fw=null;
    
    String CSVName = "";

    String CSVFileName = "";

    String FITSName = "";

    String FITSFileName = "";
    
    String savedFilename="";
    
    String fileDescr=null;

    String emailAddress = null;
    
    long startTime=0;
    String startTimeStr;

    // 'static' initialiser is called when class is first loaded to initialise
    // and install the database driver(s)!
   static {
        // Load in the Microsoft-provided JDBC SQL Server driver
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            Class.forName("net.sourceforge.jtds.jdbc.Driver");

        } catch (ClassNotFoundException e) {
            // A class not found error means the SQL driver class could not be
            // found.
            // Which means that this driver would not be available until this is
            // remedied. This flag variable will signal this to the class!

            System.err.println("SQLRetrieverThread::static(): Could not load SQLServerDriver class!");
        }
        ;
       // ucdMgr = UCDManager.getInstance();
        //retMgr = RetrieverManager.getInstance();
        //retMap = RetrieverMapper.INSTANCE;

    }; // END of 'static' initialiser
    

    // The CSVNumber variable is used to generate unique filenames for CSV files
    // when people request them. The idea when CSV is made, the
    // getNextCSVNumber()
    // method will be called to return the next number in sequence and also to
    // increment the counter. Note that thisd is a 'synchronised' method,
    // meaning
    // that it is THREAD SAFE and so can be called by any thread (as long as it
    // is
    // through the getNextCSVNumber() method. DO NOT MODIFY CSVNumber variable
    // DIRECTLY!!
    private static final int CVSNUM_UPPERLIMIT = 60000;

    private static int CSVNumber = 0; // 'Unique' counter for CSV
                                      // requests/filenames

    private static synchronized int getNextCSVNumber() {
        CSVNumber++;

        if (CSVNumber > CVSNUM_UPPERLIMIT) {
            CSVNumber = 0;
        }
        ;

        return CSVNumber;
    };

    public WSASQLRetrieverThread(SQLServerInfoJDBC newServerInfo) {
        setServerInfo(newServerInfo);
    };
    
    public void setQID(int qID) {
        this.qID=qID;
        
    }
    
    public boolean  upNamePresent=false;
    
   
    
    public void setListDriven(String aper, String url) {
        listDriven=true;
        listDrivenAper=aper;
        listDrivenURL=url;
        
    }
    public String getResultsURL() {
        return resultsURL;
    };

    public void setDatabaseName(String database) {
        this.database=database;
    };
    
    public WSASQLRetrieverThread(String dbURL) {
        databaseURL=dbURL;
    };
    
    protected SQLServerInfoJDBC serverInfo = null;

    protected synchronized void setServerInfo(SQLServerInfoJDBC newServerInfo) {
        serverInfo = newServerInfo;
    };

    public synchronized void setUploadFile(String newUploadFile) {
        uploadFile = newUploadFile;
    }
    
    String xDB; 
    String xTable; 
    String xSelect; 
    String xWhere; 
    String xID;
    double xRadius; 
    int xNearest;
    String xOtherTables;
    String xOtherTableConstraints;
    
    public void setCrossIDParameters(String xDB, String xTable, String xSelect, String xWhere, String xID,double xRadius, int xNearest) {
        setCrossIDParameters(xDB,xTable,xSelect,xWhere,xID,xRadius,xNearest,null,null);

    }
    public void setCrossIDParameters(String xDB, String xTable, String xSelect, String xWhere, String xID,double xRadius, int xNearest,String xOtherTables,String xOtherTableConstraints) {
        this.xDB=xDB;
        this.xTable=xTable;
        this.xSelect=xSelect;
        this.xWhere=xWhere;
        this.xRadius=xRadius;
        this.xNearest=xNearest;
        this.xID=xID;
        this.xOtherTables=xOtherTables;
        this.xOtherTableConstraints=xOtherTableConstraints;
    }
    public synchronized void setUpBufferedReader(BufferedReader newUpBufferedReader) {
        BufferedReader uploadBufferedReader = newUpBufferedReader;
    }

    protected boolean queryTerminated = false;

    public synchronized void setQueryTerminated(boolean status) {
        queryTerminated = status;
    };

    //public synchronized boolean isQueryTerminated() {
    public boolean isQueryTerminated() {
        return queryTerminated;
    };

    protected boolean queryProcessing = true;
    
    
    public void closeConnection(){
        if (databaseConnection != null) {
            try {
                databaseConnection.close();
                logger.info("ConnectionClosed");
            } catch (SQLException se) {
                logger.error(se);
            }
            
        }
    }
    public synchronized void setDBConnectionClosed() {
        logger.info("setDBConnectionClosed");
        if (databaseConnection != null) {
            System.out.println("im closing");
            logger.info("setDBConnectionClosed");
            try {
                databaseQuery.setQueryTimeout(1);
                //databaseConnection.close();
            } catch (SQLException se) {
                System.out.println(se);
            }
            ;
        }
        ;
    }

    public synchronized void setQueryProcessing(boolean status) {
        queryProcessing = status;
    };

    public synchronized boolean isQueryProcessing() {
        return queryProcessing;
    };

    boolean mightBeBackground = false;

    public synchronized void setMightBeBackground(boolean newMBB) {
        mightBeBackground = newMBB;
    };

    public synchronized boolean isMightBeBackground() {
        return mightBeBackground;
    };

    public synchronized void setEmailAddress(String newEmailAddress) {
        emailAddress = newEmailAddress;
    }

    public synchronized String getEmailAddress() {
        return emailAddress;
    }

    protected String radius = "0";

    public synchronized void setRadius(String newRadius) {
        radius = newRadius;
    }

    protected String pair = "nearest";

    public synchronized void setPair(String newPair) {
        pair = newPair;
    }

    protected int rowsHTML = SQLROWS_DEFAULT;

    public synchronized void setRowsHTML(int newRows) {
        if (newRows > HTMLROWS_UPPERLIMIT) {
            rowsHTML = HTMLROWS_UPPERLIMIT;
        } else if (newRows < 0) {
            rowsHTML = 0;
        } else {
            rowsHTML = newRows;
        }
        ;
    };

    public synchronized int getRowsHTML() {
        return rowsHTML;
    };

    public synchronized int getRowsReturned() {
        return row;
    };

    protected boolean doRowTotal = true;

    public synchronized boolean getDoRowTotal() {
        return doRowTotal;
    };

    public synchronized void setDoRowTotal(boolean status) {
        doRowTotal = status;
    };

    protected String SQLQuery = null;
    String user = null;
    String ipAddress = null;
    

    public synchronized void setSQLQuery(String newQuery) {
        SQLQuery = newQuery;

        setQueryProcessing(true); // We haven't got the results for this
                                  // query...
    };
    public  void setUser(String user) {
        this.user = user;
    };
    public  void setIPAddress(String ipAddress) {
        this.ipAddress = ipAddress;
    };

    public synchronized String getSQLQuery() {
        return SQLQuery;
    };

    protected int dataOutputFormat = OUTPUT_NONE;

    public synchronized void setOutputFormat(int newFormat) {
        dataOutputFormat = newFormat;
    };

    public synchronized int getOutputFormat() {
        return dataOutputFormat;
    };

    protected int dataOutputCompression = COMPRESS_NONE;

    public synchronized void setOutputCompression(int newCompression) {
        dataOutputCompression = newCompression;
    };

    public synchronized int getOutputCompression() {
        return dataOutputCompression;
    };

    protected StringBuffer htmlResults = new StringBuffer("");

    protected synchronized void clearResultsHTML() {
        htmlResults = new StringBuffer("");
    };

    protected synchronized void addToResultsHTML(String newLine) {
        htmlResults.append(newLine);
    };

    public synchronized String getResultsHTML() {
        return htmlResults.toString();
    };

    protected String HTMLTableRowColorHeader = WSAHTMLSchema.headRow;

    //protected String HTMLTableRowColorOdd = "#99CCFF";
    //protected String HTMLTableRowColorOdd = "#FFCCCC";
    protected String HTMLTableRowColorOdd = WSAHTMLSchema.oddRow;

    protected String HTMLTableRowColorEven = WSAHTMLSchema.evenRow;

    public synchronized void setHTMLTableColorRowHeader(String newColor) {
        HTMLTableRowColorHeader = newColor;
    };

    public synchronized String getHTMLTableColorRowHeader() {
        return HTMLTableRowColorHeader;
    };

    public synchronized void setHTMLTableColorRowOdd(String newColor) {
        HTMLTableRowColorOdd = newColor;
    };

    public synchronized String getHTMLTableColorRowOdd() {
        return HTMLTableRowColorOdd;
    };

    public synchronized void setHTMLTableColorRowEven(String newColor) {
        HTMLTableRowColorEven = newColor;
    };

    public synchronized String getHTMLTableColorRowEven() {
        return HTMLTableRowColorEven;
    };

    protected String outputBaseDIR = "";

    public synchronized void setOutputBaseDIR(String newDir) {
        outputBaseDIR = newDir;
    };

    public synchronized String getOutputBaseDIR() {
        return outputBaseDIR;
    };

    protected String outputBaseURL = "";

    public synchronized void setOutputBaseURL(String newUrl) {
        outputBaseURL = newUrl;
    };

    public synchronized String getOutputBaseURL() {
        return outputBaseURL;
    };

    protected String outputAppendage = "";

    public synchronized void setOutputAppendage(String newAppendage) {
        outputAppendage = newAppendage;
    };

    public synchronized String getOutputAppendage() {
        return outputAppendage;
    };

    protected void generateErrorHTML(String errorMessage) {
        clearResultsHTML();
         if (showSQL) {
             //addToResultsHTML("<p>Submitted query: <b>"+SQLQuery.trim()+"</b><p>");
         }
        addToResultsHTML(HTMLStrings.getHTMLErrorFragment(errorMessage));
    };

    // The following functions generate 'formatter arrays' which hold an array
    // of SQLDataFormatter (or subclasses) objects that apply formatting to the
    // related data cell...

    protected SQLDataFormatter getSQLDataFormatterForColumn(int column) {
        return new SQLDataFormatter();
    };

    private SQLDataFormatter[] getSQLDataFormatterArray() {
        // Can access MaxColumns, which should represent the num columns in
        // result!
        // Can access 'metadata' object, which should have been set before now!

        // BE AWARE OF THE DIFFERENCE IN ARRAY NUMBERING, SQL columns start at
        // 1,
        // Java arrays start at 0!!!

        SQLDataFormatter[] tempArray = new SQLDataFormatter[resultsNumColumns];

        for (int column = 1; column <= resultsNumColumns; column++) {
            tempArray[column - 1] = getSQLDataFormatterForColumn(column);
        }
        ;

        return tempArray;
    };

    protected String getPreTableHTML() {
        return "";
    };

    protected void verifyQueryAndOptions() throws Exception {
        // Dummy class does nothing... override in subclass if needed...
    };

    protected String getPostTableHTML() {
        String firstPart, secondPart = "";
        String extra = "";
        if (row >= rowsTotalUpperLimit) {
            extra = " <b> at least (i.e. before it was halted) </b> ";
        }

        if (getDoRowTotal()) {
            if (row == 1) {
                firstPart = "      (Query returned 1 result row, \n ";
            } else {
                firstPart = "      (Query returned " + extra + row + " result rows, \n";
            }

            if (rowsWrittenToHTML == 0) {
                secondPart = "      no rows are shown in the displayed table.) \n";
            } else if (row <= rowsWrittenToHTML) {
                secondPart = "      all rows are shown in the displayed table.) \n";
            } else if (rowsWrittenToHTML == 1) {
                secondPart = "      only the first row is shown in the displayed table.) \n";
            } else {
                secondPart = "      only the first " + rowsWrittenToHTML + " rows are shown in the displayed table.) \n";
            }
        } else {
            if (row <= rowsWrittenToHTML) {
                if (row == 1) {
                    firstPart = "      (Query returned 1 result row, \n";
                } else {
                    firstPart = "      (Query returned " + row + " result rows, \n";
                }

                secondPart = "      all rows are shown in the displayed table.) \n";
            } else if (rowsWrittenToHTML == 0) {
                firstPart = "      (Query returned more than 0 result rows, \n";
                secondPart = "      no rows are shown in the displayed table.) \n";
            } else if (rowsWrittenToHTML == 1) {
                firstPart = "      (Query returned more than 1 result row, \n";
                secondPart = "      only the first row is shown in the displayed table.) \n";
            } else {
                firstPart = "      (Query returned more than " + rowsWrittenToHTML + " result rows, \n";
                secondPart = "      only the first " + rowsWrittenToHTML + " rows are shown in the displayed table.) \n";
            }
        }

        return "    <p> \n" + firstPart + secondPart + "    </p> \n\n";
    };

    protected String getInsertedTableCellsHeaderHTML() {
        return "        <th>&nbsp;</th> \n";
    };

    protected String getInsertedTableCellsDataHTML() {
        return "        <td nowrap class=\"RowHeader\" bgcolor=\"" + getHTMLTableColorRowHeader() + "\"><b>"
                + row + "</b></td> \n"; //nowrap=\"nowrap\"
    };

    
    public Statement setConnection(String [] servers, String database, String archive, HttpSession sess, String startServer){
        try {
        
        databaseConnection=DatabaseConnection.getConnection(servers,database,archive,sess,startServer);
        databaseConnection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        DatabaseMetaData dbmd=databaseConnection.getMetaData();
        //database = dbmd.getDatabaseProductName();
        databaseQuery = databaseConnection.createStatement();       
        
        return databaseQuery;
        }
        catch (SQLException se){
            logger.error(se);
            return null;
        }
        catch (Exception e){
            logger.error(e);
            return null;
        }
    }
    
    public Statement setConnection(){
        try {
        databaseConnection = DriverManager.getConnection(databaseURL);
        databaseConnection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        DatabaseMetaData dbmd=databaseConnection.getMetaData();
        //database = dbmd.getDatabaseProductName();
        databaseQuery = databaseConnection.createStatement();
        
        
        return databaseQuery;
        }
        catch (SQLException se){
            logger.error(se);
            return null;
        }
    }
    /**
     * Execute the SQL query on the provided JDBC server and store the results
     * in the htmlResults StringBuffer ready for later retrieval...
     * 
     * @author Alan Maxwell
     */
    public void run() {
        startTimeStr=Calendar.getInstance().getTime().toString();
        logger.debug("RetrieverThread,Status,Start of run");
        setQueryProcessing(true);
        setQueryTerminated(false);

        StringBuffer resultsStringBuffer = new StringBuffer("");

        int runningNumber = getNextCSVNumber();

        String uniqID = String.valueOf(Calendar.getInstance().get(Calendar.DAY_OF_MONTH)) + "_"
                + String.valueOf(Calendar.getInstance().get(Calendar.HOUR_OF_DAY)) + "_"
                + String.valueOf(Calendar.getInstance().get(Calendar.MINUTE)) + "_"
                + String.valueOf(Calendar.getInstance().get(Calendar.SECOND)) + "_" + runningNumber;
        resultsURL=getOutputBaseURL()+ getOutputAppendage() + "results" + uniqID + ".html";
        logger.debug(uniqID);
        //retMgr.addRetriever(uniqID, Thread.currentThread());
        //retMap.addRetriever(uniqID, this);

        PrintWriter CSVWriter = null;
        OutputStream CSVOutputStream = null;
        // WSASQLRetrieverThread wsa=(WSASQLRetrieverThread)smap.get(uniqID);
        //logger.warn(String.valueOf(wsa.getOutputFormat()));
        //logger.warn(String.valueOf(retMgr.getNumberRetriever() + " howdy"));
        //logger.warn(String.valueOf(retMgr.isRunning(uniqID) + " howdy"));
        // Cache some variables to avoid repeated thread-locked access (slow)
        int OutputFormatCache = getOutputFormat();

        if (OutputFormatCache != OUTPUT_NONE) {
            File testFile= new File(getOutputBaseDIR() + getOutputAppendage());
            if (!testFile.exists() || !testFile.isDirectory()) {
                generateErrorHTML("temp directory not available for writing.");
                try {
                databaseConnection.close();
                } catch (SQLException se) {
                    
                }
                
                testFile=null;
                return;
            }
            testFile=null;
          
        }
        
        Thread cancelRunner=null;
        CancelThread CThread=null;  
        
        try {
            //   System.out.println ("1a");
            try {
                // Main execution here - retrieving of query and formatting
                // results...
               // if (serverInfo == null) {
                if (databaseURL ==null) {
                    generateErrorHTML("<b>Error in run():</b> No server/database URL provided.");
                    return;
                }
                ;

                // Open database connection...
               // databaseConnection = DriverManager.getConnection(serverInfo.databaseURL, serverInfo.dbUsername,
                      //  serverInfo.dbPassword);
                //databaseConnection = DriverManager.getConnection(databaseURL);
                //databaseQuery = databaseConnection.createStatement();
                //ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE
                //databaseQuery=setConnection();
                
  
                if (doCancelThread) {
                    CThread=new CancelThread(databaseQuery,Math.min(MAXTIMEOUT,timeout));
                    cancelRunner = new Thread(CThread);
                    cancelRunner.start();                   
                }
                else {             
                databaseQuery.setQueryTimeout(Math.min(MAXTIMEOUT,timeout));
                }
                databaseQuery.setFetchSize(500);
                // allow query to run on for a factor
                databaseQuery.setMaxRows((ROWSWRITTEN_UPPERLIMIT*FACTOR)+1);
                
                // Attempt to 'do' the query...
                int noBrowseCols=0;
                if (uploadFile == null) {
                    
                    ResultSetMetaData browsemd=SQLMethods.browseSQL(databaseConnection,SQLQuery);
                    if (doCancelThread) {                    
                        if (CThread.getCancelled()) {
                            throw new SQLException("Query statement cancelled by timeout.");
                        }  
                        }
                    databaseQuery.execute("set fmtonly off"); // double check
                    if (browsemd != null) {
                        noBrowseCols=browsemd.getColumnCount();
                        if (noBrowseCols > 0) {
                        databaseQuery.setMaxRows(((((int) Math.ceil(ROWSWRITTEN_UPPERLIMIT / (noBrowseCols * 1000.)) * 1000))*FACTOR)+2);
                        }
                        /*
                        for (int i=0; i< noBrowseCols ;i++) {
                        logger.error(browsemd.getTableName(i+1));
                        }*/
                        }
                    else {
                        logger.info("null browsemd");
                    }
                    
                    //					databaseQuery.setQueryTimeout(30);
                   // results = databaseQuery.executeQuery("EXEC dbo.spWebExecuteSQL '"+SQLQuery+" ','"+
                     //             user+"','"+ipAddress+"'");
                    
                    if (isQueryTerminated()) {
                        return;
                    }
                    startTime=System.currentTimeMillis();
                    results = databaseQuery.executeQuery(SQLQuery);
                    

                } else {
                    StringBuffer SQLQueryBuffer = null;
                    startTime=System.currentTimeMillis();

                    try {
                        String checkSQL=null;
                        String where="";
                        
                        String otherTables="";
                        
                        if (xOtherTables !=null && !xOtherTables.equals("")) {
                            otherTables=","+xOtherTables;
                        }
                        
                        if (xWhere !=null && !xWhere.equals("")) {                          
                            checkSQL="select "+xSelect+" from "+xTable+otherTables+" where "+xWhere;
                            where=" and "+xWhere;
                        }
                        else {
                            checkSQL="select "+xSelect+" from "+xTable;
                        }
                        
                        SQLMethods.checkValidSQL(databaseConnection,checkSQL);
                        if (!upNamePresent) {
                        databaseQuery.executeUpdate("create table #upload  (upload_ID int,upload_RA float,upload_Dec float)");
                        }
                        else {
                            databaseQuery.executeUpdate("create table #upload  (upload_ID int,upload_RA float,upload_Dec float,upload_name varchar(24))");
                        }
                        databaseQuery.executeUpdate("create table #proxtab  (upID int,archiveID bigint ,distance float primary key (upID,archiveID))");
 
                        
                        SQLQueryBuffer = new StringBuffer("");
                                

                        //	SQLQuery="create table #upload (up_ra float, up_dec
                        // float, up_id int, up_name varchar(32)) ";

                        File file = new File(uploadFile);
                        FileReader fr = new FileReader(file);
                        BufferedReader in = new BufferedReader(fr);
                        String line;
                        int count=0;
                        Object [] upCoords;
                        double upRA;
                        double upDec;
                        long upMatchID;
                        databaseConnection.setAutoCommit(false);
                        boolean uploadIDPres=true;
                        if (upNamePresent) {
                            uploadIDPres=false;
                        }
                        while ( ((line=in.readLine()) !=null) && !isQueryTerminated() ){
                			count=count+1;
                			upCoords=StringToRADec.getRADec(line.trim(),uploadIDPres);
                			upRA = ((Double)upCoords[0]).doubleValue();
                			upDec = ((Double)upCoords[1]).doubleValue();
                			upMatchID=((Long)upCoords[2]).longValue();
                			if (upRA >=0.0 && upRA <= 360.0 && upDec >= -90.0 && upDec <= 90.0){
                			    if (!upNamePresent) {
                			    databaseQuery.addBatch("insert into #upload  values ("+
                			            count +","+upRA+","+upDec+"	)");
                			    }
                			    else {
                			        databaseQuery.addBatch("insert into #upload  values ("+
                    			            count +","+upRA+","+upDec+",'"+upCoords[3].toString()+"'	)"); 
                			    }
                			
                		}


                		}
                        
                        databaseConnection.commit();
                        
                        int [] updateCounts=databaseQuery.executeBatch();
                        databaseConnection.setAutoCommit(true);
                        resultsStringBuffer.append("<br>"+updateCounts.length+" rows uploaded<p>");
                        logger.info("Retriever thred Starting store procedure");
                        databaseQuery.executeUpdate("EXEC master..spWSACrossID "+xRadius/60.0+",'"+xDB+".."+xTable+" as "+xTable+"','"+xID+"',"+xNearest+",' "+where+" '");
                      //  SQLQueryBuffer.append("EXEC dbo.spWebExecuteSQL '"+SQLQuery+" ','"+
                      //          user+"','"+ipAddress+"'");
                        SQLQueryBuffer.append(SQLQuery);

                        // file.delete();
                    } catch (FileNotFoundException e) {
                    }
                    //		databaseQuery.setQueryTimeout(3);
                    //		resultsStringBuffer.append("timeout set");
                    results = databaseQuery.executeQuery(SQLQueryBuffer.toString());
                }
                
                resultsMetaData = results.getMetaData();
                // System.out.println ("2");
                frs.setRSMD(resultsMetaData);
                //resultsStringBuffer.append(getOutputBaseDIR() + " "+
                // getOutputAppendage());
                if (OutputFormatCache == OUTPUT_FITS) {
                fw = new FITSWriter(SQLQuery,database);
                if (uploadFile !=null) {
                    String [] comm={"CROSSID radius: "+xRadius,"CROSSID NEARBY or NEAREST 0 or 1: "+xNearest};
                    fw.setComments(comm);
                }
                }

                //resultsStringBuffer.append("heretada");
                //addToResultsHTML(resultsStringBuffer.toString());
                ParseVOT pVOT=null;
                resultsNumColumns = resultsMetaData.getColumnCount();
                int row_size=0;
                rowsWrittenUpperLimit = ((int) Math.ceil(ROWSWRITTEN_UPPERLIMIT / (resultsNumColumns * 1000.)) * 1000) - 1;
                
                if (rowsWrittenUpperLimit < 1 ) {
                    rowsWrittenUpperLimit = 2;
                }
                /*
                if (rowsWrittenUpperLimit > ROWSWRITTEN_UPPERLIMIT) {
                    rowsWrittenUpperLimit = ROWSWRITTEN_UPPERLIMIT;
                }
                */

                rowsTotalUpperLimit = FACTOR * (rowsWrittenUpperLimit + 1);
                // System.out.println(rowsWrittenUpperLimit);
                columnNames = new String[resultsNumColumns];

  
                
                switch (OutputFormatCache) {
                case OUTPUT_NONE:
                    fileDescr="";
                    break; // case OUTPUT_NONE does nothing
                case OUTPUT_FITS:
                    fileDescr="FITS";
                    FITSName = "results" + uniqID; //getNextCSVNumber()
                    savedFilename = FITSName + ".fits";
                    File FITSFile = new File(getOutputBaseDIR() + getOutputAppendage() + savedFilename);
                    if (FITSFile.exists()) {
                        FITSFile.delete();
                    }

                    fw.setFileName(getOutputBaseDIR() + getOutputAppendage() + savedFilename);
                    
                    fw.setRSMD(resultsMetaData);
                    

                    break;
                case OUTPUT_CSV:
                    
                    CSVName = "results" + uniqID; //getNextCSVNumber()

                    switch (getOutputCompression()) {
                    case COMPRESS_NONE:
                        savedFilename = CSVName + ".csv";
                        fileDescr="CSV ASCII";
                        try {

                            CSVOutputStream = new BufferedOutputStream(new FileOutputStream(getOutputBaseDIR()
                                    + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE);
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_NONE
                    case COMPRESS_ZIP:
                        savedFilename = CSVName + ".zip";
                        fileDescr="zipped CSV ASCII";
                        try {
                            CSVOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(
                                    getOutputBaseDIR() + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE));

                            // Prepare the zip
                            ((ZipOutputStream) CSVOutputStream).setLevel(9);
                            ((ZipOutputStream) CSVOutputStream).putNextEntry(new ZipEntry(CSVName + ".csv"));
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_ZIP
                    case COMPRESS_GZIP:
                        savedFilename = CSVName + ".csv.gz";
                        fileDescr="gzipped CSV ASCII";
                        try {
                            CSVOutputStream = new GZIPOutputStream(new BufferedOutputStream(new FileOutputStream(
                                    getOutputBaseDIR() + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE));
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_GZIP
                    } // switch

                    if (CSVOutputStream != null) {
                        try {
                            CSVWriter = new PrintWriter(CSVOutputStream, true);
                        } catch (Exception e) {
                            CSVWriter = null;
                        }
                        ;
                    }
                    ;

                    break; // case OUTPUT_CSV

                case OUTPUT_VOT:
                    pVOT=new ParseVOT(SQLQuery,database);
                    frs.setVOT(true);
                    pVOT.setRSMD(resultsMetaData);
                    CSVName = "results" + uniqID; //getNextCSVNumber()

                    switch (getOutputCompression()) {
                    case COMPRESS_NONE:
                        savedFilename = CSVName + ".xml";
                        fileDescr="VOTable ASCII";
                        try {

                            CSVOutputStream = new BufferedOutputStream(new FileOutputStream(getOutputBaseDIR()
                                    + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE);
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_NONE
                    case COMPRESS_ZIP:
                        savedFilename = CSVName + ".zip";
                        fileDescr="zipped VOTable ASCII";
                        try {
                            CSVOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(
                                    getOutputBaseDIR() + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE));

                            // Prepare the zip
                            ((ZipOutputStream) CSVOutputStream).setLevel(9);
                            ((ZipOutputStream) CSVOutputStream).putNextEntry(new ZipEntry(CSVName + ".xml"));
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_ZIP
                    case COMPRESS_GZIP:
                        savedFilename = CSVName + ".xml.gz";
                        fileDescr="gzipped VOTable ASCII";
                        try {
                            CSVOutputStream = new GZIPOutputStream(new BufferedOutputStream(new FileOutputStream(
                                    getOutputBaseDIR() + getOutputAppendage() + savedFilename), OUTPUT_BUFFERSIZE));
                        } catch (Exception e) {
                            CSVOutputStream = null;
                        }
                        break; // case COMPRESS_GZIP
                    } // switch

                    if (CSVOutputStream != null) {
                        try {
                            CSVWriter = new PrintWriter(CSVOutputStream, true);
                        } catch (Exception e) {
                            CSVWriter = null;
                        }
                        ;
                    }
                    ;
                    CSVWriter.println(pVOT.getVOTHeader());
                    CSVWriter.println(pVOT.setRSMD(resultsMetaData));
                    break; // case OUTPUT_VOT

                } // switch (OutputFormatCache)

                // System.out.println ("3");
                
                    
                if (doShowMF) {
                    showMF=ShowCols.areColsInRS(showMFArr,resultsMetaData);
                    if (showMF) {
                    resultsStringBuffer.append("The mfLink column can be used to display and download the associated multiframe.<p>");
                    }
                }
                if (doShowGIF) {
                    showGIF=ShowCols.areColsInRS(showGIFArr,resultsMetaData);
                    if (showGIF) {
                    resultsStringBuffer.append("The getFSLink column can be used to display 1 arcmin image cut-outs around the RA/Dec of the object.<p>");
                    }
                }
                if (doShowGIM) {
                    showGIM=ShowCols.areColsInRS(showGIMArr,resultsMetaData);
                    if (showGIM) {
                    resultsStringBuffer.append("The getMFLink column can be used to display 1 arcmin image cut-outs around the RA/Dec of the object.<p>");
                    }
                }
                resultsStringBuffer.append(getPreTableHTML());

                resultsStringBuffer
                        .append(
                                //"<form action=\"http://www-wfau.roe.ac.uk/~sss/cgi-bin/checkbox.cgi\" method=post>"
                                 "    <table border=\"1\" cellspacing=\"0\" cellpadding=\"3\" " + "bgcolor=\""
                                + getHTMLTableColorRowOdd() + "\"> \n");

                boolean GenerateHTML = true; // Set properly elsewhere...
                boolean doForm = false;
                boolean RAPresent = false;
                boolean DecPresent = false;
                int RACol = 0;
                int DecCol = 0;
                if (resultsNumColumns > 0) // We have at least one column
                                           // returned!!!
                {
                    if (GenerateHTML == true) {
                        resultsStringBuffer.append("      <tr bgcolor=\"" + getHTMLTableColorRowHeader() + "\"> \n");

                        // Insert (if any) extra cells before real data, can be
                        // overridden
                        // in subclasses - defaults to printing the row
                        // number...
                        resultsStringBuffer.append(getInsertedTableCellsHeaderHTML());
                    }
                    ;
                    
 
                    //resultsStringBuffer.append("<p>"+showMF+"<p>");
                    
                    row_size=SQLMethods.getSizeOfRS(resultsMetaData);
                    
                    for (int column = 1; column <= resultsNumColumns; column++) {
                        columnNames[column - 1] = resultsMetaData.getColumnName(column);

                        if (columnNames[column - 1].equalsIgnoreCase("RA")) {
                            RACol = column - 1;
                            RAPresent = true;
                        }

                        if (columnNames[column - 1].equalsIgnoreCase("DEC")) {
                            DecCol = column - 1;
                            DecPresent = true;
                        }
                    }
                    if (GenerateHTML ==true && showMF) {
                        resultsStringBuffer.append("        <th nowrap=\"nowrap\">" + "mfLink" + "</th> \n");
                    }
                    if (GenerateHTML ==true && showGIF) {
                        resultsStringBuffer.append("        <th nowrap=\"nowrap\">" + "getFSLink" + "</th> \n");
                    }
                    
                    if (GenerateHTML ==true && showGIM) {
                        resultsStringBuffer.append("        <th nowrap=\"nowrap\">" + "getMFLink" + "</th> \n");
                    }

                    if (GenerateHTML == true && RAPresent && DecPresent) {

                     //   resultsStringBuffer.append("        <th nowrap=\"nowrap\">" + "&nbsp;" + "</th> \n");
                    }
                    ;

                    if ((OutputFormatCache == OUTPUT_CSV) && (CSVWriter != null)) {   
                        CSVWriter.print(ACSVWriter.printHeader(resultsMetaData,SQLQuery,database));
                    }
                    
                    for (int column = 1; column <= resultsNumColumns; column++) {
                        columnNames[column - 1] = resultsMetaData.getColumnName(column);

                        if (GenerateHTML == true) {
                            if (columnNames[column - 1].length() < 1) {
                                resultsStringBuffer.append("        <th nowrap>" + "&nbsp;" + "</th> \n");
                            } else {
                                resultsStringBuffer.append("        <th nowrap>" + columnNames[column - 1]
                                        + "</th> \n"); //nowrap=\"nowrap\"
                            }
                            ;
                        }
                        ;

                        // Also, if needed, generate the header row for the CSV
                        // file...
                       /*f ((OutputFormatCache == OUTPUT_CSV) && (CSVWriter != null)) {
                           
                           
                            if (column == resultsNumColumns) {
                                CSVWriter.println(columnNames[column - 1]);
                            } else {
                                CSVWriter.print(columnNames[column - 1] + ",");
                            }
                            ;
                        }
                        */
                        ; // if (GenerateCSV == true)

                    }
                    ;

                    if (GenerateHTML == true) {
                        resultsStringBuffer.append("      </tr> \n");
                    }
                }
                ;

                columnFormats = getSQLDataFormatterArray();

                // Verify if the query is OK before continuing...
                verifyQueryAndOptions();

                // Now process the results (if any)...
                row = 0;
                String[] strArray;
                String[] votArray;
                strArray = new String[resultsNumColumns];
                votArray = new String[resultsNumColumns]; 
                
                 int maxRowsHTML=rowsHTML;
                 if (resultsNumColumns > 0) {
                     maxRowsHTML = Math.min(rowsHTML,(int)(MAXHTMLCELLS/resultsNumColumns));
                 }
                 
                 if (maxRowsHTML > 0) {
                     GenerateHTML=true;
                 }
                 else {
                     GenerateHTML=false;
                 }
                 
                 if (doCancelThread) {                    
                 if (CThread.getCancelled()) {
                     AnySQLErrors=true;
                     throw new SQLException("Query statement cancelled by timeout.");
                 }  
                 }
                 
                while ((!isQueryTerminated())
                        && (((OutputFormatCache != OUTPUT_NONE) && row <= rowsWrittenUpperLimit) || (GenerateHTML == true))
                        && (results.next())) {
                    row++;
                    
                   // Thread.sleep(100);
                  //  GenerateHTML = ((row <= rowsHTML) && (row <= HTMLROWS_UPPERLIMIT));
                    //GenerateHTML = (row <= maxRowsHTML);
                    // Put in the start of the HTML data row, before doing the
                    // FOR...
                    // and also the first data element (the generated row
                    // number)...
                    
                    if ((GenerateHTML == true) || (OutputFormatCache == OUTPUT_CSV)) {
                        strArray = frs.setRS(results);
                    }
                    if ((OutputFormatCache == OUTPUT_VOT)) {
                        votArray=pVOT.setRS(results);
                    }
                    
                    
                    if (GenerateHTML == true) {
                        //GenerateHTML = (row <= maxRowsHTML);
                        rowsWrittenToHTML++;
                        if ((row % 2) == 0) {
                            resultsStringBuffer.append("      <tr bgcolor=\"" + getHTMLTableColorRowEven()
                                    + "\" class=\"RowEven\"> \n");
                        } else {
                            resultsStringBuffer.append("      <tr bgcolor=\"" + getHTMLTableColorRowOdd()
                                    + "\" class=\"RowOdd\"> \n");
                        }
                        ;

                        resultsStringBuffer.append(getInsertedTableCellsDataHTML());
                     
                        if (GenerateHTML && showMF) {
                            resultsStringBuffer.append(ShowCols.getMFLinkCell(results)); 
                        }
                        if (GenerateHTML && showGIF) {
                            resultsStringBuffer.append(ShowCols.getGIFLinkCell(results,database)); 
                        }
                        if (GenerateHTML && showGIM) {
                            resultsStringBuffer.append(ShowCols.getGIMLinkCell(results,database)); 
                        }
                        /*
                        if (RAPresent && DecPresent && GenerateHTML) {
                               resultsStringBuffer.append("        <td align=\"right\" nowrap=\"nowrap\">"
                                       + "<input type=checkbox name=ch value=\"" + strArray[RACol].trim() + ":"
                                       + strArray[DecCol].trim() + "\">" + "</td> \n");
                                       
                           }
                        */
                        for (int column = 1; column <= resultsNumColumns; column++) {  
                            
                                columnData = strArray[column - 1].trim();
                                resultsStringBuffer.append("<td align=\"right\" nowrap>"
                                        + columnFormats[column - 1].formatDataForHTML(columnData) + "</td> \n"); //nowrap=\"nowrap\"
                        }
                        
                            resultsStringBuffer.append("</tr> \n");
                            GenerateHTML = (row+1 <= maxRowsHTML) && resultsStringBuffer.length() < MAXRESBUFFERSIZE;
                    }
                    


                    if ((OutputFormatCache == OUTPUT_FITS)) {
                        fw.setRS(results);
                    }

 

                    for (int column = 1; column <= resultsNumColumns; column++) {
                        // Extract the raw data for this column as a string
                        // (safest as string)

                        /*
                         * if (results.getString(column) == null) { columnData =
                         * null; } else { columnData =
                         * results.getString(column).trim(); };
                         */

                        // Firstly, if we are still within our requested display
                        // limits
                        // generate a header row for the HTML table for this
                        // database row...

                        ; // if (GenerateHTML == true)

                        // Then, with the same returned data, we now write it
                        // out to the CSV...
                        if ((OutputFormatCache == OUTPUT_CSV) && (CSVWriter != null)) {
                            //columnData = strArray[column-1];
                            if (column == resultsNumColumns) {
                                CSVWriter.println(
                                //columnFormats[column -
                                // 1].formatDataForCSV(columnData)
                                        strArray[column - 1]);
                            } else {
                                CSVWriter.print(
                                //columnFormats[column -
                                // 1].formatDataForCSV(columnData) + ","
                                        strArray[column - 1] + ",");
                            }
                            ;
                        }
                        ; // if (GenerateCSV == true)

                        if ((OutputFormatCache == OUTPUT_VOT) && (CSVWriter != null)) {
                            //columnData = strArray[column-1];
                            if (column == 1) {
                                CSVWriter.print("<TR>");
                            }
                            CSVWriter.print("<TD>" + votArray[column - 1] + "</TD>");
                                
                                
                            if (column == resultsNumColumns) {
                                    CSVWriter.println("</TR>");
                                }
                            
                        }
                        ; // if (GenerateCSV == true)

                    }
                    ; // for

                    // Put in the start of the HTML data row, before doing the
                    // FOR...
  
                }
                ; // END of while results.next()...

                // Now do an extra count of the total number of rows that would
                // have
                // been returned if the above while loop was stopped
                // prematurely. If
                // the above while cycled through all the rows, this will do
                // nothing.
                rowsWrittenToFile = row;
//                while ((!isQueryTerminated()) && (getDoRowTotal()) && (results.next() && row < rowsTotalUpperLimit)) {
                while ((!isQueryTerminated()) && doRowTotal && (results.next() && row < rowsTotalUpperLimit)) {
                    row++;
                }
                ;
                
                
                if (doCancelThread) {                    
                    if (CThread.getCancelled()) {
                        throw new SQLException("Query statement cancelled by timeout.");
                    }  
                    }
                if (doCancelThread) {
                    CThread.setFinished();
                }
               if (isQueryTerminated()){
                   resultsStringBuffer.insert(0,"WARNING: Stopped by request" );
               }

                resultsStringBuffer.append("    </table>\n\n");
                //resultsStringBuffer.append(rowsTotalUpperLimit +" " + row + " "+rowsWrittenUpperLimit);
                resultsStringBuffer.append(getPostTableHTML());

                if ((OutputFormatCache == OUTPUT_FITS)) {
                    fw.close();
                    if (dataOutputCompression == 2) {

                        try {
                            // Create the GZIP output stream
                            String outFilename = getOutputBaseDIR() + getOutputAppendage() + savedFilename + ".gz";
                            GZIPOutputStream out = new GZIPOutputStream(new FileOutputStream(outFilename));

                            // Open the input file
                            String inFilename = getOutputBaseDIR() + getOutputAppendage() + savedFilename;
                            FileInputStream in = new FileInputStream(inFilename);

                            // Transfer bytes from the input file to the GZIP
                            // output stream
                            byte[] buf = new byte[1024];
                            int len;
                            while ((len = in.read(buf)) > 0) {
                                out.write(buf, 0, len);
                            }
                            in.close();

                            // Complete the GZIP file
                            out.finish();
                            out.close();
                            savedFilename = FITSName + ".fits.gz";
                            fileDescr="gzipped FITS";
                        } catch (IOException e) {
                        }
                    }
                }

                if ((OutputFormatCache == OUTPUT_CSV) && (CSVWriter != null)) {
                    CSVWriter.flush(); // Ensure all data is finished

                    if (getOutputCompression() == COMPRESS_ZIP) {
                        ((ZipOutputStream) CSVOutputStream).closeEntry();
                    }
                    ;

                    CSVWriter.close();
                }
                ;

                if ((OutputFormatCache == OUTPUT_VOT) && (CSVWriter != null)) {
                    CSVWriter.print(pVOT.getFooter());

                    CSVWriter.flush(); // Ensure all data is finished

                    if (getOutputCompression() == COMPRESS_ZIP) {
                        ((ZipOutputStream) CSVOutputStream).closeEntry();
                    }
                    ;

                    CSVWriter.close();
                }
                ;

                if (OutputFormatCache != OUTPUT_NONE) {
                    int rowtemp = rowsWrittenUpperLimit + 1;
                    if (row > rowsWrittenToFile) {
                        resultsStringBuffer.append("<p><b> NB The number of rows written to the output file "
                                + "reached the maximum allowed " + rowtemp + "</b>");
                    }
                }
                
                
                
                
                if (OutputFormatCache != OUTPUT_NONE) {
                    String fileLink=getOutputBaseURL() + getOutputAppendage() + savedFilename;
                    resultsStringBuffer.append("<p> \n" + "<table cellspacing=\"0\" cellpadding=\"3\"><tr><td align=\"right\">" +
                    		"<a href=\""+fileLink+ "\"><img src=\"" + getOutputBaseURL()
                            + "images/icons/save.gif\" border=\"0\"></a></td>" + "<td><a href=\""
                            + fileLink + "\">"
                            + "Download Results File </a>, your results in a "+fileDescr+" file \n" + "(Contains <b>");

                    if (rowsWrittenToFile == 1) {
                        resultsStringBuffer.append("1 row</b>, ");
                    } else {
                        resultsStringBuffer.append(rowsWrittenToFile + " rows</b>, ");
                    }
                    ;
                    File sFile = new File(getOutputBaseDIR() + getOutputAppendage() + savedFilename);

                    long sFileSize = sFile.length();

                    if (sFileSize > 1000000) {
                        resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(sFileSize / 1024.0 / 1024.0)
                                + " MB) \n");
                    } else if (sFileSize > 1000) {
                        resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(sFileSize / 1024.0) + " KB) \n");
                    } else {
                        resultsStringBuffer.append(sFileSize + " bytes) \n");
                    }
                    ;

                    resultsStringBuffer.append("</td> \n");
                  if (OutputFormatCache == OUTPUT_FITS || OutputFormatCache == OUTPUT_VOT){
                    resultsStringBuffer
                            .append("<tr class=\"Normal\"  bgcolor=\"#FFFFFF\"><td align=\"right\">\n"
                                    + "<a href=\"http://surveys.roe.ac.uk:8080/ssa/launch.jsp?file="
                                    + fileLink 
                                    + "\"><img src=\"http://surveys.roe.ac.uk/ssa/topcat.gif\" border=\"0\"></a> </td>"
                                    + "<td><a href=\"http://surveys.roe.ac.uk:8080/ssa/launch.jsp?file="
                                    + fileLink 
                                    + "\""
                                    + ">Launch file in Topcat</a> (requires Java 1.4 and Java Web Start, approx 3Mb download for Topcat application)</td>");
                }
                  
                 /*  save to myspace doesn't work
                  resultsStringBuffer
                  .append("<tr class=\"Normal\" bgcolor=\"#FFFFFF\"><td align=\"right\"> \n"
                          + "<a href=\"http://surveys.roe.ac.uk:8080/wsa/saveToMySpace.jsp?file="
                          + fileLink
                          + "\"><img src=\"http://surveys.roe.ac.uk/wsa/ag.gif\" border=\"0\"></a></td>"
                          + "<td><a href=\"http://surveys.roe.ac.uk:8080/wsa/saveToMySpace.jsp?file="
                          + fileLink
                          + "\""
                          + ">Save file to MySpace</a> (requires an AstroGrid account, Java 1.5 and Java Web Start to launch, uses AstroGrid workbench, large first download)</td></table>");
                          */
                  resultsStringBuffer.append("</table>");
                }
                
                
                
  /*              

                if (OutputFormatCache == OUTPUT_FITS) {
                    resultsStringBuffer.append("    <p> \n" + "      <img src=\"" + getOutputBaseURL()
                            + "images/icons/save.gif\" \n" + "alt=\"Save icon\" /> \n" + "      <a href=\""
                            + getOutputBaseURL() + getOutputAppendage() + savedFilename + "\">"
                            + "Download Results File </a>, your results in a FITS file \n" + "      (Contains <b>");

                    if (rowsWrittenToFile == 1) {
                        resultsStringBuffer.append("1 row</b>, ");
                    } else {
                        resultsStringBuffer.append(rowsWrittenToFile + " rows</b>, ");
                    }
                    ;
                    File FITSFile = new File(getOutputBaseDIR() + getOutputAppendage() + savedFilename);

                    long FITSFileSize = FITSFile.length();

                    if (FITSFileSize > 1000000) {
                        resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(FITSFileSize / 1024.0 / 1024.0)
                                + " MB) \n");
                    } else if (FITSFileSize > 1000) {
                        resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(FITSFileSize / 1024.0) + " KB) \n");
                    } else {
                        resultsStringBuffer.append(FITSFileSize + " bytes) \n");
                    }
                    ;

                    resultsStringBuffer.append("</p> \n\n");

                    resultsStringBuffer
                            .append("    <p> \n"
                                    + "<img src=\"http://surveys.roe.ac.uk/ssa/topcat.gif\">"
                                    + "<a href=\"http://surveys.roe.ac.uk:8080/ssa/launch.jsp?file="
                                    + getOutputBaseURL()
                                    + getOutputAppendage()
                                    + savedFilename
                                    + "\""
                                    + ">Launch file in Topcat</a> (requires Java 1.4 and Java Web Start, approx 3Mb download for Topcat application)<p>");
                }

                if (OutputFormatCache == OUTPUT_CSV || OutputFormatCache == OUTPUT_VOT) {
                    if (CSVWriter != null) {
                        resultsStringBuffer.append("    <p> \n" + "      <img src=\"" + getOutputBaseURL()
                                + "images/icons/save.gif\" \n" + "alt=\"Save icon\" /> \n" + "      <a href=\""
                                + getOutputBaseURL() + getOutputAppendage() + savedFilename + "\">"
                                + "Download Results File </a>, your results in a ASCII/VOTable file \n"
                                + "      (Contains <b>");

                        if (rowsWrittenToFile == 1) {
                            resultsStringBuffer.append("1 row</b>, ");
                        } else {
                            resultsStringBuffer.append(rowsWrittenToFile + " rows</b>, ");
                        }
                        ;

                        File CSVFile = new File(getOutputBaseDIR() + getOutputAppendage() + savedFilename);

                        long CSVFileSize = CSVFile.length();

                        if (CSVFileSize > 1000000) {
                            resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(CSVFileSize / 1024.0 / 1024.0)
                                    + " MB) \n");
                        } else if (CSVFileSize > 1000) {
                            resultsStringBuffer.append(HTMLStrings.numbersTo1DP.format(CSVFileSize / 1024.0)
                                    + " KB) \n");
                        } else {
                            resultsStringBuffer.append(CSVFileSize + " bytes) \n");
                        }
                        ;

                        resultsStringBuffer.append("</p> \n\n");

                        if (OutputFormatCache == OUTPUT_VOT) {
                            resultsStringBuffer
                                    .append("    <p> \n"
                                            + "<img src=\"http://surveys.roe.ac.uk/ssa/topcat.gif\">"
                                            + "<a href=\"http://surveys.roe.ac.uk:8080/ssa/launch.jsp?file="
                                            + getOutputBaseURL()
                                            + getOutputAppendage()
                                            + savedFilename
                                            + "\""
                                            + ">Launch file in Topcat</a> (requires Java 1.4 and Java Web Start, approx 3Mb download for Topcat application)<p>");
                        }

                    } else {
                        resultsStringBuffer.append("    <p> \n"
                                + "      Sorry - the server experienced an unknown problem while creating "
                                + "your ASCII/VOTable file, please try again later.\n" + "    </p> \n\n");
                    }
                    ;
                }
                ;
*/
                if (RAPresent && DecPresent && row > 0) {
                    /*
                    resultsStringBuffer
                            .append("<SCRIPT LANGUAGE=\"javascript\">"
                                    + "function checkAll(){"
                                    + "for (var i=0;i<document.forms[0].ch.length;i++)"
                                    + "document.forms[0].ch[i].checked = true;}"
                                    + "function uncheckAll(){"
                                    + "for (var i=0;i<document.forms[0].ch.length;i++)"
                                    + "document.forms[0].ch[i].checked = false;}"
                                    + "</script>"
                                    + "To extract images centred on the RA and Decs returned in the above table "
                                    + "check the boxes of the objects you wish to view, select the waveband(s) "
                                    + "required and enter a size."
                                    + "<p><input class=\"FontSans\" type=\"button\" name=\"selectall\" value=\"&nbsp;check all&nbsp;\" onclick=\"checkAll();\">"
                                    + "&nbsp&nbsp;<input class=\"FontSans\" type=\"button\" name=\"selectall\" value=\"uncheck all\" onclick=\"uncheckAll();\">"
                                    + "<p>Survey/Waveband (tick at least one box):<br>"
                                    + "UKJ-<INPUT TYPE=\"checkbox\" NAME=\"waveband1\" VALUE=\"1\">"
                                    + "&nbsp&nbsp UKR-<INPUT TYPE=\"checkbox\" NAME=\"waveband2\" VALUE=\"1\">"
                                    + "&nbsp&nbsp UKI-<INPUT TYPE=\"checkbox\" NAME=\"waveband3\" VALUE=\"1\">"
                                    + "&nbsp&nbsp ESO-<INPUT TYPE=\"checkbox\" NAME=\"waveband4\" VALUE=\"1\">"
                                    + "&nbsp&nbsp PAE-<INPUT TYPE=\"checkbox\" NAME=\"waveband5\" VALUE=\"1\"><p>"
                                    + "Size of extracted box: <input type=\"text\" name=\"size\" value=\"1\" size=4 maxlength=4>"
                                    + " arcmin (maximum 3)<p>"
                                    + "<input class=\"FontSans\" type=submit value=\"getThumbnails\">");
                                    */
                }

                //resultsStringBuffer.append("</form>\n");

                // Finally, copy the results into the string...
                
                //Statement testStmt=databaseConnection.createStatement();
                databaseQuery.execute("select 1");
                
                
                
                if (updateWebqueries) {
                    if (SQLQuery.toLowerCase().indexOf("listwebqueries") < 0) {
                long elapsedTime=System.currentTimeMillis()-startTime;
                
                try {
                    databaseQuery.execute(SQLMethods.getWebqueriesString(SQLQuery,row,elapsedTime,user,ipAddress,database,row_size));
                }
                catch (SQLException se ) {
                    // do nothing
                    AnySQLErrors=true;
                    resultsStringBuffer.append(se);
                }
                
                }
                }
                
                if (qID > 0) {
                    try {
                        databaseQuery.execute(SQLMethods.getUpdateQStatusSQLURL(qID,2,resultsURL));

                    }
                    catch (SQLException se ) {
                        // do nothing
                        resultsStringBuffer.append(se);
                    }
                }
                //resultsStringBuffer.append(Calendar.getInstance().getTime().toString());
                clearResultsHTML();
                addToResultsHTML(extraHTMLMsg);
                if (showSQL) {
                    //addToResultsHTML("<p>Submitted query: "+HTMLFormatter.forHTMLTag(SQLQuery.trim())+"<p>");
                }
                addToResultsHTML(resultsStringBuffer.toString());
            } catch (SQLException se) {
                AnySQLErrors=true;
                if (qID >0 && AnySQLErrors){
                    try {
                    Statement errStmt=databaseConnection.createStatement();
                    errStmt.execute(SQLMethods.getUpdateQStatusSQLURL(qID,3,resultsURL)); }
                    catch (SQLException se1 ) {
                        logger.info(se1);
                    }
                }
                System.err.println("SQLRetrieverThread::run(): an SQLException -> " + se.getMessage()
                        + se.getErrorCode());
                String extraInfo="";
                if (se.getMessage().toLowerCase().indexOf("cancelled") >= 0 ) {
                    extraInfo="<br>Query ran for "+ParseCoords.formatP( ((System.currentTimeMillis()-startTime)/1000.0),1)+" secs<br>Timeout set at "+timeout;
                }
                generateErrorHTML("<b>SQL Error:</b> " + se.getMessage()+extraInfo);
            } catch (Exception e) {
                // General exception catch-all. Any exceptions in run() that
                // fall
                // through are trapped and neatly formatted here...

                System.err.println("SQLRetrieverThread::run(): Exception -> " + e.toString());
                logger.error(getStackTraceAsString(e));
                generateErrorHTML("<b>Error in run():</b> " + e.toString());
            }
            ;
        } finally {
  
            logger.debug("and finally....");
            setQueryProcessing(false);
          //  retMap.delRetriever(uniqID);
            /*
             * setDBConnectionClosed(); System.out.println("closing");
             */

            try {
              
                databaseConnection.close();
 
            } catch (SQLException se) {
            }
            ;

            // Free up used resources...
            CSVWriter = null;
            CSVOutputStream = null;
            resultsStringBuffer = null;
            frs=null;
            fw=null;
            resultsMetaData = null;
            if (results != null) {
                try {
                    //databaseQuery.cancel(); // was commented out
                    results.close();
                    //System.out.println("here2"+Calendar.getInstance().getTime());

                } catch (SQLException se) {
                    //System.out.println("here3"+Calendar.getInstance().getTime()
                    // + se.getMessage());
                }
                ;
            }
            ;

            results = null;

            if (databaseQuery != null) {
                try {
                    //databaseQuery.cancel();
                    databaseQuery.close();
                    //        	System.out.println("here3"+Calendar.getInstance().getTime()
                    // );
                } catch (SQLException se) {
                }
                ;
            }
            ;

            databaseQuery = null;

            databaseConnection = null;
            if (doCancelThread) {
                try {
                cancelRunner.interrupt();
                }
                catch (Exception e){
                    
                }
                cancelRunner=null;
                CThread=null;
            }
            if (isMightBeBackground()) {
                int waitCount = 0;
                while (getEmailAddress() == null && waitCount < 15) {
                    logger.debug("hereibe " + waitCount);
                    waitCount = waitCount + 1;
                    try {
                        Thread.sleep(1000);
                    } catch (InterruptedException ie) {
                        // Do nothing when interrupted...
                    }

                } //end of while

                //  System.out.println("email "+getEmailAddress() +" " +
                // Calendar.getInstance().getTime() );
                if (listDriven) {
                    try {

                    URL url = new URL (listDrivenURL+"?file="+getOutputBaseDIR()+getOutputAppendage()+savedFilename+"&email="+getEmailAddress()+"&aperture="+listDrivenAper);

                    logger.info(listDrivenURL+url.getFile());
                    URLConnection uc = url.openConnection();
                    InputStream content = (InputStream)uc.getInputStream();
                    BufferedReader in   = 
                      new BufferedReader (new InputStreamReader (content));
                    String wLine;
                    StringBuffer rsb=new StringBuffer("");
                    while ((wLine = in.readLine()) != null) {
                      rsb.append(wLine);
                    }
                    listDrivenRes=rsb.toString();
                  } catch (MalformedURLException e) {
                      logger.error(e.getMessage());
                      listDrivenRes="URL ERROR "+e.getMessage();
                  } catch (IOException e) {
                    logger.error(e.getMessage());
                    listDrivenRes="IO ERROR "+e.getMessage();
                  }
            }
                    
                    
                
                if (getEmailAddress() != null) {
                    if (getEmailAddress().length() > 3) {
                        String HTMLFileName = getOutputBaseDIR() + getOutputAppendage() + "results" + uniqID + ".html";
                        File HTMLFile = new File(HTMLFileName);
                        try {
                            HTMLFile.createNewFile();
                            FileWriter HTMLfw = new FileWriter(HTMLFile);
                            PrintWriter HTMLpw = new PrintWriter(HTMLfw);
                            HTMLpw.println(HTMLStrings.getHTMLHeadWithScript("WSA SQL Query Results",
                            //              "http://grendel12.roe.ac.uk/~avo/ssa/" +
                            // "configurestyles.js"));
                                    "http://surveys.roe.ac.uk/wsa/" + "configurestyles.js"));

                            HTMLpw.println("<h3>WSA Database - SQL Query Results</h3>");
                            if (uploadFile != null) {
                                HTMLpw.println("<p> CrossID parameters:");
                                HTMLpw.println("<br> radius: "+xRadius);
                                HTMLpw.println("<br> Nearby(0) or Nearest (1): "+xNearest);
                                HTMLpw.println("\n<!--"+SQLQuery.trim()+"--><p>");
                                
                                
                            }
                            if (extraHTMLMsg.length() >0 ) {
                                HTMLpw.println("<p> "+extraHTMLMsg+"<p>");
                            }
                            if (showSQL) {
                            HTMLpw.println("<p>Submitted query: "+HTMLFormatter.forHTMLTag(SQLQuery.trim())+"<p>");
                            }
                            HTMLpw.println("<b>Start:</b> "+startTimeStr+" <b>End:</b> "+Calendar.getInstance().getTime()+"<p>Database: "+database+"<p>");
                            HTMLpw.println(htmlResults.toString());
                            HTMLpw.println("</body> </html>");
                            HTMLfw.close();

                        } catch (IOException e) {
                        }
                        try {
                            java.util.Properties properties = System.getProperties();
                            Session session = Session.getInstance(properties, null);
                            // Construct a message
                            MimeMessage message = new MimeMessage(session);
                            message.setFrom(new InternetAddress("wsa-support@roe.ac.uk"));
                            message.addRecipient(Message.RecipientType.TO, new InternetAddress(getEmailAddress()));
                            message.addRecipient(Message.RecipientType.BCC, new InternetAddress("mar@roe.ac.uk"));
                            message.setSubject("WFCAM Science Archive");
                            if (listDriven) {
                                message.setText("The intermediate results of your query are are\n" + getOutputBaseURL()
                                        + getOutputAppendage() + "results" + uniqID + ".html\nThese results have been forwarded to the list driven" +
                                        		"photomtery service and you will receive another email when that has completed (this could take several hours).\n"+listDrivenRes);
                                } 
                            
                            else {
                            message.setText("The results of your query can be retrieved from \n" + getOutputBaseURL()
                                    + getOutputAppendage() + "results" + uniqID + ".html");
                            }

                            //message.setText(getOutputBaseURL() +
                            // getOutputAppendage() +"results"+ uniqID +
                            // ".html");

                            // Connect to the transport
                            Transport transport = session.getTransport("smtp");
                            transport.connect("mail.roe.ac.uk", "", "");

                            // Send the message and close the connection
                            transport.sendMessage(message, message.getAllRecipients());
                            transport.close();
                        } catch (MessagingException me) {
                            logger.error(me);
                            logger.error("EMAILERROR "+getEmailAddress()+"\nThe results of your query can be retrieved from \n" + getOutputBaseURL()
                                    + getOutputAppendage() + "results" + uniqID + ".html");
                           // System.out.println(me.getMessage());
                        }
                        catch (Exception e) {
                            logger.error(e);
                            logger.error("EMAILERROR "+getEmailAddress()+"\nThe results of your query can be retrieved from \n" + getOutputBaseURL()
                                    + getOutputAppendage() + "results" + uniqID + ".html");
                           // System.out.println(me.getMessage());
                        }

                    }
                }

            } // end of ismightbebackground

            System.gc();

        } // end of finally
        
    } // end of run
    public String getStackTraceAsString(Exception exception) 
    { 
    StringWriter sw = new StringWriter(); 
    PrintWriter pw = new PrintWriter(sw); 
    pw.print(" [ "); 
    pw.print(exception.getClass().getName()); 
    pw.print(" ] "); 
    pw.print(exception.getMessage()); 
    exception.printStackTrace(pw); 
    return sw.toString(); 
    }
}; // END of class SQLRetrieverThread