package com.sap.dbmtk.demo;

import java.awt.Window;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.regex.Pattern;

import javax.swing.SwingWorker;

import org.apache.log4j.Logger;

import com.sap.dbmtk.demo.ui.ResultSetTableModel;
import com.sap.dbmtk.demo.ui.SqlProgressDialog;
import com.sap.dbmtk.demo.utils.Configuration;
import com.sap.dbmtk.demo.utils.DbUtils;
import com.sap.dbmtk.demo.utils.SwingUtils;
import com.sybase.jdbcx.EedInfo;

public class SqlStatement
{
	private static Logger _logger = Logger.getLogger(SqlStatement.class);

	public static final String  PROPKEY_EXEC_WAITFOR_POPUP_GRACETIME = "SqlStatement.exec.waitfor.popup.gracetime";
	public static final int     DEFAULT_EXEC_WAITFOR_POPUP_GRACETIME = 1000;
	
	public static final String  PROPKEY_USE_PKG_VARIABLES            = "SqlStatement.usePkgVariables";
	public static final boolean DEFAULT_USE_PKG_VARIABLES            = true;

	public static boolean _staticUsePkgVariables = DEFAULT_USE_PKG_VARIABLES;
	public static void    setUsePkgVariables(boolean toVal) { _staticUsePkgVariables = toVal; }
	public static boolean setUsePkgVariables()              { return _staticUsePkgVariables; }

	private String                  _sql         = null;
	private String                  _sqlLastUsed = null;
	private Type                    _type        = null;
	private List<SqlStatementParam> _params      = null;
	
	private List<ResultSetTableModel> _rstmList = new ArrayList<ResultSetTableModel>();
	private int _rowCount = 0;

	private int     _execWaitforPopupGracetimeInMs = Configuration.getCombinedConfiguration().getIntProperty    (PROPKEY_EXEC_WAITFOR_POPUP_GRACETIME, DEFAULT_EXEC_WAITFOR_POPUP_GRACETIME);
	private boolean _usePkgVariables               = Configuration.getCombinedConfiguration().getBooleanProperty(PROPKEY_USE_PKG_VARIABLES,        _staticUsePkgVariables);

	public enum Type
	{
		LANGUAGE, 
		PREPARED, 
		CALLABLE
	};

	public SqlStatement(Type type, String sql)
	{
		this(type, sql, null);
	}

	public SqlStatement(Type type, String sql, List<SqlStatementParam> params)
	{
		_type   = type;
		_sql    = sql;
		_params = params;
	}


	public Type getType()
	{
		return _type;
	}
	
	public String getSql()
	{
		return _sql;
	}
	
	private String getSql(Connection conn)
	{
		String sql = sqlSubstitution(conn, _sql);
		
		_logger.debug("getSql(conn): fixedSql=|"+sql+"|.");
		_sqlLastUsed = sql;
		
		return sql;
	}
	
	public String getSqlLastUsed()
	{
		return _sqlLastUsed;
	}
	
	/**
	 * Transforms various internal variables into real text<br>
	 * like "call ${PKG:pkgName}memberName" into: "call pkgName.memberName" for Oracle or "call pkgName__memberName" for ASE
	 * 
	 * @return
	 */
	private String sqlSubstitution(Connection conn, String inputSql)
	{
//		String val = "abc ${PKG:pkgName1}memberName1";
		String val = inputSql; 
		_logger.debug("sqlSubstitution(): INPUT: inputSql='"+val+"'.");

		Pattern compiledRegex = Pattern.compile("\\$\\{.+\\}");
		while( compiledRegex.matcher(val).find() )
		{
			String varVal      = null;
			String varStr      = val.substring( val.indexOf("${")+2, val.indexOf("}") );
			String varName     = varStr;
			String varModifier = "";
			if (varStr.indexOf(':') >= 0)
			{
				int firstColon = varStr.indexOf(':');
				varName     = varStr.substring(0, firstColon);
				varModifier = varStr.substring(firstColon + 1);
			}

			_logger.debug("sqlSubstitution(): varName='"+varName+"', varModifyer='"+varModifier+"'.");
			
			if ( "PKG".equals(varName) )
			{
				varVal = "";
				if (varModifier != null)
				{
					boolean isOracle = isOracle(conn);

					if (isOracle)
						varVal = varModifier + "."; // Oracle separation char after a package name
					else
						varVal = varModifier + "__"; // DBMTK separation chars after a package name
				}
				
				if ( ! _usePkgVariables )
				{
					_logger.info("Found PACKAGE variable '${"+varStr+"}' but Package Variable substritution is DISABLED, so this will simply be removed.");
					varVal = "";
				}
			}
			else
			{
				_logger.warn("sqlSubstitution(): WARNING: Unknown variable '"+varName+"', simply removing it from the output.");
				varVal = "";
			}

			_logger.debug("sqlSubstitution(): Substituting varName '${"+varStr+"}' with value '"+varVal+"'.");

			// NOW substitute the VARIABLE with a real value...
			val = val.replace("${"+varStr+"}", varVal);
		}
		_logger.debug("sqlSubstitution(): AFTER: val='"+val+"'.");
		
		return val;
	}

	
	public void addParam(SqlStatementParam val)
	{
		if (_params == null)
			_params = new ArrayList<SqlStatementParam>();

		_params.add(val);
	}

	public List<SqlStatementParam> getParams()
	{
		if (_params == null)
			return Collections.emptyList();
		return _params;
	}


	/**
	 * Execute the LANGUAGE/PREPARED/CALLABLE statement and return first ResultSet for reading
	 * @param conn
	 * @return a ResultSet from {Statement|PreparedStatement|CallableStatement}.executeQuery()
	 * @throws SQLException
	 */
	public ResultSet executeQuery(ConnectionProvider connProvider)
	throws SQLException
	{
		Connection conn = connProvider.getConnection();
		String sql = getSql(conn);

		Statement         stmnt  = null;
		PreparedStatement pstmnt = null;
		CallableStatement cstmnt = null;

		if (Type.LANGUAGE.equals(getType()))
		{
			stmnt = conn.createStatement();
		}
		else if (Type.PREPARED.equals(getType()))
		{
			pstmnt = conn.prepareStatement(sql);
		}
		else if (Type.CALLABLE.equals(getType()))
		{
			sql = fixSqlParams(getParams(), sql, isOracle(conn));
			cstmnt = conn.prepareCall(sql);
		}

		SqlTraceWindow.getInstance().addSql(sql);
		SqlTraceWindow.getInstance().startTimer();

		// Set value for PreparedStatement/CallableStatement question marks
		setParameters(pstmnt, cstmnt);

		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
		ResultSet rs = null;
		if      (Type.LANGUAGE.equals(getType())) rs = stmnt.executeQuery(sql);
		else if (Type.PREPARED.equals(getType())) rs = pstmnt.executeQuery();
		else if (Type.CALLABLE.equals(getType()))
		{
			if (isOracle(conn))
				cstmnt.execute(); // ResultSet will be delivered as a OUTPUT parameter of SYS_REFCURSOR 
			else
				rs = cstmnt.executeQuery();
		}
		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);

		// Read OUPUT parameters
		int p = 0;
		for (SqlStatementParam param : getParams())
		{
			p++;
			if ( param.isOracleRsParam() )
			{
				ResultSet rsParam = (ResultSet) cstmnt.getObject(p);
				if (rs == null)
					rs = rsParam;
				param.setOutputParamValue( rsParam );

        		SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
			else if (param.isOutput())
			{
				param.setOutputParamValue( cstmnt.getObject(p) );

				SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
		}

		// If we close the statement the ResultSet might not be valid
		//getFirstNotNull(stmnt,pstmnt,cstmnt).close();
		
		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), rs);

		SqlTraceWindow.getInstance().reportTime("executeQuery() no ResultSet yet read");

		return rs;

//		// Not yet implemented, needs to do same stuff as in execute()... fixing prepared/callable Parameters etc...
//		String sql = getSql();
//		Statement stmnt = conn.createStatement();
//		ResultSet rs = stmnt.executeQuery(sql);
//		return rs;
//		throw new RuntimeException("Not yet implemented");
	}

	/**
	 * Execute the LANGUAGE/PREPARED/CALLABLE statement do not expect a ResultSet, only a row count will be returned
	 * @param conn
	 * @return A row count from {Statement|PreparedStatement|CallableStatement}.executeUpdate()
	 * @throws SQLException
	 */
	public int executeUpdate(ConnectionProvider connProvider)
	throws SQLException
	{
		Connection conn = connProvider.getConnection();
		String sql = getSql(conn);

		Statement         stmnt  = null;
		PreparedStatement pstmnt = null;
		CallableStatement cstmnt = null;

		if (Type.LANGUAGE.equals(getType()))
		{
			stmnt = conn.createStatement();
		}
		else if (Type.PREPARED.equals(getType()))
		{
			pstmnt = conn.prepareStatement(sql);
		}
		else if (Type.CALLABLE.equals(getType()))
		{
			sql = fixSqlParams(getParams(), sql, isOracle(conn));

			cstmnt = conn.prepareCall(sql);
		}

		SqlTraceWindow.getInstance().addSql(sql);
		SqlTraceWindow.getInstance().startTimer();

		// Set value for PreparedStatement/CallableStatement question marks
		setParameters(pstmnt, cstmnt);

		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
		int rowc = -1;
		if      (Type.LANGUAGE.equals(getType())) rowc = stmnt.executeUpdate(sql);
		else if (Type.PREPARED.equals(getType())) rowc = pstmnt.executeUpdate();
		else if (Type.CALLABLE.equals(getType())) rowc = cstmnt.executeUpdate();
		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);

		SqlTraceWindow.getInstance().addRowcount(rowc);

		// Read OUPUT parameters
		int p = 0;
		for (SqlStatementParam param : getParams())
		{
			p++;
			if ( param.isOracleRsParam() )
			{
				ResultSet rsParam = (ResultSet) cstmnt.getObject(p);
				param.setOutputParamValue( rsParam );

        		SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
			else if (param.isOutput())
			{
				param.setOutputParamValue( cstmnt.getObject(p) );

				SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
		}

		// Should we close this or not... (if it's a Oracle ResultSet parameter, it might not be readable, but then we should have used executeQuery() instead...)
		//getFirstNotNull(stmnt,pstmnt,cstmnt).close();

		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
		
		SqlTraceWindow.getInstance().reportTime("executeUpdate()");

		return rowc;

//		// Not yet implemented, needs to do same stuff as in execute()... fixing prepared/callable Parameters etc...
//		String sql = getSql();
//		Statement stmnt = conn.createStatement();
//		int rowc = stmnt.executeUpdate(sql);
//		return rowc;
//		throw new RuntimeException("Not yet implemented");
	}

	
	/**
	 * Execute the SQL you have passed.<br>
	 * On errors a GUI dialog will be showed... 
	 * 
	 * @param conn Using this connection
	 * @param owner the "parent" window, this could be null, but then the popup be "standalone"
	 * @param shortMessageOnErrors Header of the GUI message
	 * @return true on success, and false if it failed.
	 */
//	public boolean execute(Connection conn, Window owner, String shortMessageOnErrors)
//	{
//		try
//		{
//			execute(conn);
//			return true;
//		}
//		catch (SQLException e)
//		{
//			showErrorMessage(e, this, owner, shortMessageOnErrors);
//			return false;
//		}
//	}
	public boolean execute(ConnectionProvider connProvider, Window owner, String shortMessageOnErrors)
	{
		try
		{
//			execute(connProvider);
			executeWithGuiProgress(connProvider, owner);

			// If we arn't in AutoCommit COMMIT
			if ( ! connProvider.isAutoCommitEnabledAtConnect() )
				connProvider.getConnection().commit();

			SqlStatement.putWarningMessages(connProvider, null, null);

			return true;
		}
		catch (SQLException e)
		{
			// If we arn't in AutoCommit ROLLBACK
			if ( ! connProvider.isAutoCommitEnabledAtConnect() )
			{
				try { connProvider.getConnection().rollback(); }
				catch (SQLException ex2) { e.setNextException(ex2); }
			}

			showErrorMessage(e, this, owner, shortMessageOnErrors);
			return false;
		}
	}

	/**
	 * Execute the SQL you have passed.
	 * 
	 * @param conn Using this connection
	 * @throws SQLException if anything failed when executing
	 */
	public void executeWithGuiProgress(final ConnectionProvider connProvider, Window owner)
	throws SQLException
	{
		Connection conn = connProvider.getConnection();
		String sql = getSql(conn);

		// Build a String with SQL and it's parameters
		StringBuilder sb = new StringBuilder();
		sb.append(sql).append("\n");

		List<SqlStatementParam> params = getParams();
		if (params != null)
		{
			int p = 0;
			for (SqlStatementParam param : params)
			{
				p++;
				sb.append("-- ").append(p).append(": ").append(param).append("\n");
			}
		}


		final SqlProgressDialog progress = new SqlProgressDialog(owner, conn, sb.toString());

		// Execute in a Swing Thread
		SwingWorker<String, Object> doBgThread = new SwingWorker<String, Object>()
		{
			@Override
			protected String doInBackground() throws Exception
			{
				SqlStatement.this.execute(connProvider, progress);
				return null;
			}

		};
		doBgThread.addPropertyChangeListener(progress);
		doBgThread.execute();

		// A dialog will be visible until the SwingWorker is done (if bgThread takes less than 200ms, the vialog will be skipped)
		progress.waitForExec(doBgThread, _execWaitforPopupGracetimeInMs);

		// We will continue here, when results has been sent by server
		//System.out.println("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");

		try
		{
			doBgThread.get();
		}
		catch (Throwable originEx)
		{
			Throwable ex = originEx;
			if (ex instanceof ExecutionException)
				ex = ex.getCause();
			
			if (ex instanceof SQLException)
			{
				throw (SQLException) ex;
			}

			SwingUtils.showErrorMessage(owner, "Problems when executing Statement", 
				"<html>" +
				"<h2>Problems when executing Statement</h2>" +
				sql +
				"</html>", 
				originEx);
		}
	}

	/**
	 * Execute the SQL you have passed.
	 * 
	 * @param conn Using this connection
	 * @throws SQLException if anything failed when executing
	 */
	public void execute(ConnectionProvider connProvider, SqlProgressDialog progress)
	throws SQLException
	{
		Connection conn = connProvider.getConnection();
		String sql = getSql(conn);
		
		Statement         stmnt  = null;
		PreparedStatement pstmnt = null;
		CallableStatement cstmnt = null;

		if (Type.LANGUAGE.equals(getType()))
		{
			stmnt = conn.createStatement();
		}
		else if (Type.PREPARED.equals(getType()))
		{
			pstmnt = conn.prepareStatement(sql);
		}
		else if (Type.CALLABLE.equals(getType()))
		{
			sql = fixSqlParams(getParams(), sql, isOracle(conn));
			cstmnt = conn.prepareCall(sql);
		}

		SqlTraceWindow.getInstance().addSql(sql);
		SqlTraceWindow.getInstance().startTimer();

		// Set value for PreparedStatement/CallableStatement question marks
		setParameters(pstmnt, cstmnt);

		if (progress != null)
			progress.setCurrentSqlText(sql, 0, 0);

		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
		boolean hasRs = false;
		if      (Type.LANGUAGE.equals(getType())) hasRs = stmnt.execute(sql);
		else if (Type.PREPARED.equals(getType())) hasRs = pstmnt.execute();
		else if (Type.CALLABLE.equals(getType())) hasRs = cstmnt.execute();
		SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);

		int rowsAffected = 0;
		do
		{
			if (hasRs)
			{
				// Get next result set to work with
				ResultSet rs = getFirstNotNull(stmnt,pstmnt,cstmnt).getResultSet();

				SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), rs);
				addResultSet(rs, progress);
				SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), rs);

				rs.close();
			} 
			else // Treat update/row count(s) for NON RESULTSETS
			{
				// Java DOC: getUpdateCount() Retrieves the current result as an update count; if the result is a ResultSet object 
				//           or there are no more results, -1 is returned. This method should be called only once per result.
				// Without this else statement, some drivers might fail...
				
				rowsAffected = getFirstNotNull(stmnt,pstmnt,cstmnt).getUpdateCount();
				SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
				
				if (rowsAffected >= 0)
				{
					// DDL or update data displayed here
					_rowCount += rowsAffected;
					addRowCount(_rowCount);
				}
				else
				{
					// No more results to process...
				}
			} // end: no-resultset

			SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
			hasRs = getFirstNotNull(stmnt,pstmnt,cstmnt).getMoreResults();
			SqlStatement.putWarningMessages(connProvider, getFirstNotNull(stmnt,pstmnt,cstmnt), null);
		}
		while (hasRs || rowsAffected != -1);

		// Read OUPUT parameters
		int p = 0;
		for (SqlStatementParam param : getParams())
		{
			p++;
			if ( param.isOracleRsParam() )
			{
				ResultSet rs = (ResultSet) cstmnt.getObject(p);
				param.setOutputParamValue( addResultSet(rs, progress) );
        		rs.close();

        		SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
			else if (param.isOutput())
			{
				param.setOutputParamValue( cstmnt.getObject(p) );

				SqlTraceWindow.getInstance().addParamAfterExec(p, param);
			}
		}

		getFirstNotNull(stmnt,pstmnt,cstmnt).close();
		
		SqlTraceWindow.getInstance().reportTime("execute()");
	}
	private Statement getFirstNotNull(Statement stmnt, PreparedStatement pstmnt, CallableStatement cstmnt)
	{
		if ( stmnt != null) return  stmnt;
		if (pstmnt != null) return pstmnt;
		if (cstmnt != null) return cstmnt;
		throw new RuntimeException("No valid statement was found");
	}
	
	/**
	 * Oracle can't deliver ResultSets from a Stored Procedure in a "normal" way, it return them as OUTPUT parameters...<br> 
	 * So in the connected DBMS in NOT Oracle, then we need to remove those output parameters<br>
	 * Meaning:
	 * <ul>
	 *   <li>remove one ? in the SQL Statement</li> 
	 *   <li>remove the parameter itself from the parameter list</li> 
	 * </ul>
	 * @param params
	 * @param sql
	 * @param isOracle indication if the Connected RDBMS is Oracle
	 * 
	 * @return the new SQL Statement to execute
	 */
	private String fixSqlParams(List<SqlStatementParam> params, String sql, boolean isOracle)
	{
		if ( isOracle )
			return sql;
		
		if (params == null)
			return sql;

		ArrayList<SqlStatementParam> removeParams = new ArrayList<SqlStatementParam>();
		for (SqlStatementParam param : params)
		{
			if ( param.isOracleRsParam() )
				removeParams.add(param);
		}

		if (removeParams.size() > 0)
		{
			// remove some "?" from the sql statement
			for (int i=0; i<removeParams.size(); i++)
			{
				String newSql = sql.replaceFirst("\\s*\\?\\s*,?\\s*", ""); // [space]?[space][,][space]
				if (_logger.isDebugEnabled())
					_logger.debug("Removing Question Mark "+i+": before='"+sql+"', after='"+newSql+"'.");
				sql = newSql;
			}
			
			// remove the params from the list
			params.removeAll(removeParams);
		}
		return sql;
	}

	/**
	 * Set parameters for PreparedStatement or CallableStatements
	 * @param pstmnt
	 * @param cstmnt
	 * @throws SQLException
	 */
	private void setParameters(PreparedStatement pstmnt, CallableStatement cstmnt) 
	throws SQLException
	{
		List<SqlStatementParam> params = getParams();
		if (params != null)
		{
			int p = 0;
			for (SqlStatementParam param : params)
			{
				p++;

				SqlTraceWindow.getInstance().addParam(p, param);

				// PREPARED Statements
				if (pstmnt != null)
				{
					if (param.getValue() == null)
					{
						pstmnt.setNull(p, param.getSqlType());
					}
					else
					{
    					if      (param.getSqlType() == Types.BLOB)    pstmnt.setBytes     (p, (byte[])    param.getValue());
    					else if (param.getSqlType() == Types.CLOB)    pstmnt.setString    (p, (String)    param.getValue());
    					else if (param.getSqlType() == Types.NUMERIC) pstmnt.setBigDecimal(p, (BigDecimal)param.getValue());
    					else                                          pstmnt.setObject    (p, (Object)    param.getValue(), param.getSqlType());
					}
				}

				// CALLABLE Statements
				if (cstmnt != null)
				{
					if (param.isInput())
					{
						if (_logger.isDebugEnabled())
							_logger.debug("cstmnt.setObject(): IN: p="+p+", value='"+param.getValue()+"', sqlType="+ResultSetTableModel.getColumnJavaSqlTypeName(param.getSqlType()));

						if (param.getValue() == null)
						{
							cstmnt.setNull(p, param.getSqlType());
						}
						else
						{
	    					if      (param.getSqlType() == Types.BLOB)    cstmnt.setBytes     (p, (byte[])    param.getValue());
	    					else if (param.getSqlType() == Types.CLOB)    cstmnt.setString    (p, (String)    param.getValue());
	    					else if (param.getSqlType() == Types.NUMERIC) cstmnt.setBigDecimal(p, (BigDecimal)param.getValue());
	    					else                                          cstmnt.setObject    (p, (Object)    param.getValue(), param.getSqlType());
						}
					}
					else // OUTPUT variables
					{
						if (_logger.isDebugEnabled())
							_logger.debug("cstmnt.registerOutParameter(): OUT: p="+p+", sqlType="+ResultSetTableModel.getColumnJavaSqlTypeName(param.getSqlType()));

						cstmnt.registerOutParameter(p, param.getSqlType());
					}
				}
			}
		}
		
	}

	/**
	 * Are we connected to Oracle
	 * @param conn
	 * @return
	 */
	private boolean isOracle(Connection conn)
	{
		try
		{
			// Are we connected to ORACLE???
			DatabaseMetaData md = conn.getMetaData();
			String dbmsProductName = md.getDatabaseProductName();
			boolean isOracle = DbUtils.isProductName(dbmsProductName, DbUtils.DB_PROD_NAME_ORACLE);

			SqlTraceWindow.getInstance().setDatabaseProductName(dbmsProductName);

			return isOracle;
		}
		catch (SQLException e)
		{
			_logger.warn("Problems checking if we are connected to Oracle or some other DBMS System");
			return false;
		}
	}


//	private ResultSet executeProc(CallableStatement cstmnt, List<SqlStatementParam> params, boolean isOracle)
//	throws SQLException
//	{
//		if (isOracle)
//		{
////			cstmnt.registerOutParameter(1, SqlStatement.ORACLE_CURSOR_TYPE);
////			cstmnt.execute();
////			ResultSet rs = (ResultSet) cstmnt.getObject(1);
//
//			cstmnt.execute();
//
//			// NOTE: the below only returns the FIRST result set, we could add them to a list...
//			ResultSet rs = null;
//			int p = 0;
//			for (SqlStatementParam param : params)
//			{
//				p++;
//				if ( param.isOracleRsParam() )
//				{
//					rs = (ResultSet) cstmnt.getObject(p);
//					return rs;
//				}
//			}
//			return rs;
//		}
//		
//		return cstmnt.executeQuery();
//	}

	/**
	 * Get parameter content<br>
	 * If it's a OUTPUT parameter of a stored proc, the content would be the OUTPUT value after execution.
	 * 
	 * @param name The specified name of the parameter when it was added
	 * @return
	 */
	public Object getParameterValue(String name)
	{
		for (SqlStatementParam p : getParams())
		{
			if (name.equals(p.getParamName()))
				return p.getValue();
		}
//		throw new ParameterNotFoundException("Parameter '"+name+"' wasn't found.");
		return null;
	}

	/**
	 * Total rowcount for all ResultSets and other rowcount's when the statements has been executed.
	 * @return
	 */
	public int getRowCount()
	{
		int rstmRowCount = 0;
		for (ResultSetTableModel rstm : _rstmList)
			rstmRowCount += rstm.getRowCount();
		
		return _rowCount + rstmRowCount;
	}

	private void addRowCount(int rowc)
	{
		_rowCount += rowc;
		
		SqlTraceWindow.getInstance().addRowcount( rowc );
	}

	private ResultSetTableModel addResultSet(ResultSet rs, SqlProgressDialog progress)
	throws SQLException
	{
		if (progress != null)
			progress.setState("Reading ResultSet.");

		ResultSetTableModel rstm = new ResultSetTableModel(rs, false, getSql(), progress);
		_rstmList.add(rstm);
		
		SqlTraceWindow.getInstance().addRowcount( rstm.getRowCount() );
		
		return rstm;
	}

	/**
	 * How many result sets objects do we have 
	 * @return
	 */
	public int getResultSetCount()
	{
		return _rstmList.size();
	}

	/**
	 * Get a specific result set object
	 * @param pos
	 * @return
	 */
	public ResultSetTableModel getResultSet(int pos)
	{
		return _rstmList.get(pos);
	}
	
	/**
	 * A list of ResultSetTableModel objects
	 * @return never null. If no result set objects, a empty list will be returned
	 */
	public List<ResultSetTableModel> getResultSetList()
	{
		return _rstmList;
	}


	public static void dbmsOutput(Connection conn, boolean enable)
	{
		String product = DbUtils.getProductName(conn);
		
		// Oracle and DB2
		if (DbUtils.isProductName(product, DbUtils.DB_PROD_NAME_ORACLE, DbUtils.DB_PROD_NAME_DB2_UX))
		{
			if ( enable )
			{
				try
				{
					CallableStatement stmt = conn.prepareCall("{call dbms_output.enable(?) }");
					
					int initSize = 1000000;
					stmt.setInt(1, initSize);
					stmt.execute();

					_logger.info("ENABLING Oracle/DB2 DBMS_OUTPUT, suceeded");
				}
				catch (Exception e)
				{
					_logger.warn("Problem occurred while trying to enable Oracle/DB2 DBMS_OUTPUT. Caught:" + e);
				}
			}
			else // not selected, check if it has been disabled otherwise do it
			{
				try
				{
					CallableStatement stmt = conn.prepareCall("{call dbms_output.disable }");
					stmt.execute();

					_logger.info("DISABLING Oracle DBMS_OUTPUT, suceeded");
				}
				catch (Exception e)
				{
					_logger.warn("Problem occurred while trying to disable Oracle DBMS_OUTPUT. Caught:" + e);
				}
			}
		}

		// ASE with SAP DBMTK RuntimeComponent installed
		if (DbUtils.isProductName(product, DbUtils.DB_PROD_NAME_SYBASE_ASE, DbUtils.DB_PROD_NAME_SYBASE_ASA, DbUtils.DB_PROD_NAME_SYBASE_IQ ))
		{
			if ( enable )
			{
				try
				{
					CallableStatement stmt = conn.prepareCall("{call xp_dbmtk_enable_buffered_output}");
					
//					int initSize = 1000000;
//					stmt.setInt(1, initSize);
					stmt.execute();

					_logger.info("ENABLING DBMTK Runtime Component for DBMS_OUTPUT emulation, suceeded");
				}
				catch (Exception e)
				{
					_logger.warn("Problem occurred while trying to enable DBMTK Runtime Component for DBMS_OUTPUT emulation. Caught:" + e);
				}
			}
			else // not selected, check if it has been disabled otherwise do it
			{
				try
				{
					CallableStatement stmt = conn.prepareCall("{call xp_dbmtk_disable_buffered_output}");
					stmt.execute();

					_logger.info("DISABLING DBMTK Runtime Component for DBMS_OUTPUT emulation, suceeded");
				}
				catch (Exception e)
				{
					_logger.warn("Problem occurred while trying to disable DBMTK Runtime Component for DBMS_OUTPUT emulation. Caught:" + e);
				}
			}
		}
	}

	public static String getDbmsOutput(ConnectionProvider connProvider)
	{
		Connection conn = null;
		try { conn = connProvider.getConnection(); }
		catch(SQLException notConnected) { return ""; }

		String product = DbUtils.getProductName(conn);

		String result = "";
		
		// Get Oracle/DB2 specific DBMS_OUTPUT  messages
		if (DbUtils.isProductName(product, DbUtils.DB_PROD_NAME_ORACLE, DbUtils.DB_PROD_NAME_DB2_UX))
		{
			try
			{
				CallableStatement stmt = conn.prepareCall("{call dbms_output.get_line(?,?)}");
				stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
				stmt.registerOutParameter(2, java.sql.Types.NUMERIC);
				int status = 0;
				do
				{
					stmt.execute();
					String msg = stmt.getString(1);
					status = stmt.getInt(2);

					_logger.debug("Oracle/DB2 DBMS_OUTPUT.GET_LINE: status=" + status + ", msg='" + msg + "'.");
					if (msg != null)
					{
						result += "<i><font color=\"blue\">DBMS_OUTPUT.GET_LINE: </font></i>" + msg + "\n";
						connProvider.addWarningMessage( new DbMessageDbmsOutput(msg, product));
					}

				} while (status == 0);
				_logger.debug("End of Oracle/DB2 DBMS_OUTPUT!");
				stmt.close();
			}
			catch (Exception e)
			{
				_logger.warn("Problem occurred during dump of Oracle DBMS_OUTPUT. Caught: " + e);
			}
		} // end: Getting Oracle: DBMS_OUTPUT

		// ASE with DBMTK RuntimeComponent installed
		if (DbUtils.isProductName(product, DbUtils.DB_PROD_NAME_SYBASE_ASE, DbUtils.DB_PROD_NAME_SYBASE_ASA, DbUtils.DB_PROD_NAME_SYBASE_IQ ))
		{
			try
			{
				CallableStatement stmt = conn.prepareCall("{call xp_dbmtk_get_buffered_output(?,?)}");
				stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
				stmt.registerOutParameter(2, java.sql.Types.INTEGER);
				int status = 0;
				do
				{
					stmt.execute();
					String msg = stmt.getString(1);
					status = stmt.getInt(2);

					_logger.debug("DBMTK Runtime Component for DBMS_OUTPUT emulation xp_dbmtk_get_buffered_output: status=" + status + ", msg='" + msg + "'.");
					if (msg != null)
					{
						result += "<i><font color=\"blue\">DBMTK_GET_BUFFERED_OUTPUT: </font></i>" + msg + "\n";
						connProvider.addWarningMessage( new DbMessageDbmsOutput(msg, product));
					}

				} while (status == 0);
				_logger.debug("End of DBMTK Runtime Component for DBMS_OUTPUT.GET_LINE emulation using xp_dbmtk_get_buffered_output!");
				stmt.close();
			}
			catch (Exception e)
			{
				_logger.warn("Problem occurred during dump of DBMTK Runtime Component for DBMS_OUTPUT emulation. Caught: " + e);
			}
		}
		
		return result;
	}

	/**
	 * 
	 * @param sqe
	 * @return
	 */
	public static String getSqlMessages(SQLException sqe, List<DbMessage> list)
	{
		StringBuilder sbOut = new StringBuilder();
		StringBuilder sb    = new StringBuilder();
		while (sqe != null)
		{
			if(sqe instanceof EedInfo)
			{
				// Error is using the additional TDS error data.
				EedInfo eedi = (EedInfo) sqe;
				if(eedi.getSeverity() > 10)
				{
					boolean firstOnLine = true;
					sb.append("Msg "   + sqe.getErrorCode() +
							", Level " + eedi.getSeverity() + 
							", State " + eedi.getState() + 
							":\n");

					if( eedi.getServerName() != null)
					{
						sb.append("Server '" + eedi.getServerName() + "'");
						firstOnLine = false;
					}
					if(eedi.getProcedureName() != null)
                    {
						sb.append( (firstOnLine ? "" : ", ") +
								"Procedure '" + eedi.getProcedureName() + "'");
						firstOnLine = false;
                    }
					sb.append( (firstOnLine ? "" : ", ") +
							"Line "        + eedi.getLineNumber() +
							", Status "    + eedi.getStatus() + 
							", TranState " + eedi.getTranState() + 
							":\n");
				}

				// Now print the error or warning
				String msg = sqe.getMessage();
				sb.append(msg);
				if ( ! msg.endsWith("\n") )
					sb.append("\n");
			}
			else
			{
//				// SqlState: 010P4 java.sql.SQLWarning: 010P4: An output parameter was received and ignored.
//				if ( ! "010P4".equals(sqe.getSQLState()) )
//				{
//					sb.append("Unexpected exception : " +
//							"SqlState: " + sqe.getSQLState()  +
//							" " + sqe.toString() +
//							", ErrorCode: " + sqe.getErrorCode() + "\n");
//				}
				String msg = sqe.getMessage();
				sb.append("ErrorCode ").append(sqe.getErrorCode()).append(", SQLState ").append(sqe.getSQLState()).append(", ExceptionClass: ").append(sqe.getClass().getName()).append("\n");
				sb.append(msg);
				if ( ! msg.endsWith("\n") )
					sb.append("\n");
			}
			if (list != null)
			{
				list.add(new DbMessageAsePrint(sb.toString()));
			}
			sbOut.append(sb);
			sb.setLength(0);

			sqe = sqe.getNextException();
		}
		return sbOut.toString();
	}

	public static void showErrorMessage(Throwable ex, SqlStatement sqlStmnt, Window guiOwner, String headerText)
	{
		SqlTraceWindow.getInstance().addException(ex);

		String msg = null;
		if (ex instanceof SQLException)
			msg = SqlStatement.getSqlMessages((SQLException)ex, null).replace("\n", "<br>");

		String definedSql  = null;
		String lastUsedSql = null;
		if (sqlStmnt != null)
		{
			definedSql  = sqlStmnt.getSql();
			lastUsedSql = sqlStmnt.getSqlLastUsed();
		}
		if (definedSql  == null) definedSql  = (sqlStmnt == null ? "SqlStmnt was NULL" : "null");
		if (lastUsedSql == null) lastUsedSql = (sqlStmnt == null ? "SqlStmnt was NULL" : "null");

		boolean showExtendedErrorInfo = Configuration.getCombinedConfiguration().getBooleanProperty(DemoApp.PROPKEY_showExtErrInfo, DemoApp.DEFAULT_showExtErrInfo);
		String extendedErrorMsg = "";
		if (showExtendedErrorInfo)
		{
			if (definedSql.equals(lastUsedSql))
			{
				extendedErrorMsg = (msg == null ? "" : "<br><b>Decoded SQL Message</b>: <br>" + msg)
						+ "<br>"
						+ "<b>SQL Statement executed</b>:"
						+ "<pre>"
						+ lastUsedSql
						+ "</pre>";
			}
			else
			{
				extendedErrorMsg = (msg == null ? "" : "<br><b>Decoded SQL Message</b>: <br>" + msg)
						+ "<br>"
						+ "<b>Defined SQL Statement</b>:"
						+ "<pre>"
						+ definedSql
						+ "</pre>"
        				+ "<br>"
        				+ "<b>SQL Statement executed</b>:"
        				+ "<pre>"
        				+ lastUsedSql
        				+ "</pre>";
			}
		}

		String htmlMsg = 
				"<html>"
				+ headerText + "<br>"
				+ "<br>"
				+ "<b>Exception</b>: <br>" 
				+ ex + "<br>" 
				+ extendedErrorMsg
				+ "</html>";
		SwingUtils.showErrorMessage(guiOwner, "Problem", htmlMsg, ex);

		// And write some stuff to the log as well
		if (definedSql.equals(lastUsedSql))
			_logger.info("Problem when executing sql: "+definedSql);
		else
			_logger.info("Problem when executing sql: definedSql='"+definedSql+"', executedSql='"+lastUsedSql+"'.");
	}

//	public static void putWarningMessages(ConnectionProvider connProvider)
//	{
//		putWarningMessages(connProvider, null, null);
//	}
	public static void putWarningMessages(ConnectionProvider connProvider, Statement stmnt, ResultSet rs)
	{
		boolean showExtendedErrorInfo = Configuration.getCombinedConfiguration().getBooleanProperty(DemoApp.PROPKEY_showExtErrInfo, DemoApp.DEFAULT_showExtErrInfo);
		if ( ! showExtendedErrorInfo )
			return;
		
		// SQLWarnings, on Connection
		try 
		{ 
			Connection conn = connProvider.getConnection(); 
			SqlStatement.getSqlMessages(conn.getWarnings(), connProvider.getWarningMessages(false));
			conn.clearWarnings();
		}
		catch (SQLException ignore) {}

		// SQLWarnings, on Statement
		if (stmnt != null)
		{
			try 
			{ 
				SqlStatement.getSqlMessages(stmnt.getWarnings(), connProvider.getWarningMessages(false));
				stmnt.clearWarnings(); 
			}
			catch (SQLException ignore) {}
		}

		// SQLWarnings, on ResultSet
		if (rs != null)
		{
			try 
			{ 
				SqlStatement.getSqlMessages(rs.getWarnings(), connProvider.getWarningMessages(false));
				rs.clearWarnings(); 
			}
			catch (SQLException ignore) {}
		}
	}

	public static void showWarningMessages(ConnectionProvider connProvider, Window guiOwner, String headerText)
	{
		StringBuilder sb = new StringBuilder();

		boolean showExtendedErrorInfo = Configuration.getCombinedConfiguration().getBooleanProperty(DemoApp.PROPKEY_showExtErrInfo, DemoApp.DEFAULT_showExtErrInfo);
		if ( ! showExtendedErrorInfo )
			return;

		// DBMS_OUTPUT
		boolean dbmsOutputIsEnabled = Configuration.getCombinedConfiguration().getBooleanProperty(DemoApp.PROPKEY_enableDbmsOutput, DemoApp.DEFAULT_enableDbmsOutput);
		if ( dbmsOutputIsEnabled )
		{
			getDbmsOutput(connProvider); // DBMS OUTPUT is also stored in ConnectionProvider.getWarningMessages()
		}

		List<DbMessage> warnList = connProvider.getWarningMessages(true);
		if (warnList.isEmpty())
			return;
		
		sb.append("<html>");
		sb.append(headerText).append("<br>");
		sb.append("<br>");
		sb.append("<b>Messages</b>: <br>"); 

		for (DbMessage dbm : warnList)
		{
			sb.append(dbm.toHtml());
			sb.append("<br>");
		}
		
		sb.append("</html>");

		SwingUtils.showInfoMessage(guiOwner, "Warning/Information messages", sb.toString());
	}
}
