import java.io.*;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.security.auth.login.LoginException;
import javax.servlet.*;
import javax.servlet.http.*;
import net.mar.RetrieverManager;
import net.mar.RetrieverMapper;

import org.apache.log4j.Logger;
import org.astrogrid.community.common.exception.CommunityIdentifierException;
import org.astrogrid.community.common.exception.CommunitySecurityException;
import org.astrogrid.community.common.exception.CommunityServiceException;
import org.astrogrid.community.common.ivorn.CommunityAccountIvornFactory;
import org.astrogrid.community.common.security.data.SecurityToken;
import org.astrogrid.community.resolver.*;
import org.astrogrid.community.resolver.exception.CommunityResolverException;
import org.astrogrid.common.*;
import org.astrogrid.config.Config;
import org.astrogrid.config.SimpleConfig;
import org.astrogrid.registry.RegistryException;
import org.astrogrid.store.Ivorn;

import uk.ac.roe.wfau.SQLMethods;


public class XMLSQL extends HttpServlet {

  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();
    try {
        // Class.forName
//("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
     } catch (ClassNotFoundException e) {
         out.println ("Could not load the driver");
     }
     // create a connection object
     String user, pass,database,sqlStmt,server;
     out.println("user, pass,database,server,sqlFile");
     user="vhsro";
     pass="vhsropw";
     database="VHSv20110816";
     server="ramses3";
     
     String sqlFile="/usr/share/tomcat5/webapps/vdfs/rgm.sql";
     sqlStmt=readFileAsString(sqlFile);
     
     //out.println(sqlStmt);
     Properties info=new Properties();
     int i=0;
     try {
         String parsedSqlStmt=SQLMethods.parseSQL(sqlStmt,9096);
     String durl="jdbc:jtds:sqlserver://"+server+":1433/" +	database+";user="+user+";password="+pass;
     Connection conn = DriverManager.getConnection(durl);
     conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement stmt = conn.createStatement();
    //stmt.setMaxRows()
    //stmt.execute("set showplan_xml on");
    stmt.execute("set showplan_all on");
    ResultSet rrs =stmt.executeQuery(parsedSqlStmt);

    int ii=0;
    
    while (rrs.next()) {
      // out.println(ii+" "+rrs.getLong("estimaterows"));
        ii++;
    }
    //stmt.execute("set showplan_xml off;");
    stmt.execute("set showplan_all off;");
    
    stmt.execute("set showplan_all off;");
    ResultSetMetaData rsmd=SQLMethods.browseSQL(conn,parsedSqlStmt);
    out.println(rsmd);
    stmt.setMaxRows(300000001);
    stmt.execute("set fmtonly off");
    rrs =stmt.executeQuery(parsedSqlStmt);
    ii=0;
    while (rrs.next()) {
          // out.println(ii+" "+rrs.getLong("estimaterows"));
            ii++;
        }
    out.println(ii);

stmt.close();
stmt=null;
conn.close();
}
catch (SQLException se){
//out.println("sQL error");
out.println(se.getErrorCode());
out.println(se.toString());
out.println(se.getCause());
out.println(se.toString().indexOf(" cancelled at the us"));
out.println("rows "+i);
}
catch (Exception e){
}
  }
  private static String readFileAsString(String filePath)
  throws java.io.IOException{
      StringBuffer fileData = new StringBuffer(1000);
      BufferedReader reader = new BufferedReader(
              new FileReader(filePath));
      char[] buf = new char[1024];
      int numRead=0;
      while((numRead=reader.read(buf)) != -1){
          String readData = String.valueOf(buf, 0, numRead);
          fileData.append(readData);
          buf = new char[1024];
      }
      reader.close();
      return fileData.toString();
  }
  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()

}