<%@page  contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="error.jsp"%>
<%
try 
{
	String theDriver	  = request.getParameter("Driver");
	String theConnString  = request.getParameter("ConnectionString");
	String theOpCode	  = request.getParameter("opCode");
	String theUserName	  = request.getParameter("UserName");
	String thePassword	  = request.getParameter("Password");

	if (theDriver != null && theConnString!=null)
	{
		Driver aDriver = (Driver)Class.forName(theDriver).newInstance();

		Connection aConn=null;

		if (request.getParameter("Timeout") != null)
		{
			int timeOut = Integer.parseInt(request.getParameter("Timeout"));
			DriverManager.setLoginTimeout(timeOut);
		}

		if ((theUserName !=null) || (thePassword !=null))
		{
			if ((theUserName.length() > 0) || (thePassword.length() > 0))
			{
				aConn= DriverManager.getConnection(theConnString,theUserName,thePassword);
			}
			else
			{
				aConn= DriverManager.getConnection(theConnString);
			}
		}
		else
		{
			aConn= DriverManager.getConnection(theConnString);
		}

		if ((aConn!=null) && (theOpCode!= null))
		{
			if (theOpCode.equals("GetTables"))
			{
				out.println(GetTables(aConn,request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("GetViews"))
			{
				out.println(GetViews(aConn,request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("GetProcedures"))
			{
				out.println(GetProcedures(aConn,request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("GetColsOfTable"))
			{
				out.println(GetColumns(aConn,request.getParameter("TableName"),request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("GetKeysOfTable"))
			{
				out.println(GetPrimaryKeys(aConn,request.getParameter("TableName"),request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("GetParametersOfProcedure"))
			{
				out.println(MarshallRecordsetIntoHTML(GetParametersOfProcedure(aConn,request.getParameter("ProcName"),request.getParameter("Schema"),request.getParameter("Catalog"))));
			}
			else if (theOpCode.equals("ExecuteSQL"))
			{
				out.println(ExecuteSQL(aConn,request.getParameter("SQL"),request.getParameter("MaxRows")));
			}
			else if (theOpCode.equals("ExecuteSP"))
			{
				out.println(ExecuteSP(out,aConn,request.getParameter("ExecProcName"),0,request.getParameter("ExecProcParameters"),request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("ReturnsResultSet"))
			{
				out.println(ReturnsResultSet(aConn,request.getParameter("RRProcName"),request.getParameter("Schema"),request.getParameter("Catalog")));
			}
			else if (theOpCode.equals("SupportsProcedure"))
			{
				out.println(SupportsProcedure(aConn));
			}
			else if (theOpCode.equals("GetProviderTypes"))
			{
				//TO DO;
			}
			else if (theOpCode.equals("IsOpen"))
			{
				out.println(TestOpen(aConn));
			}

			if (aConn != null)
			{
				aConn.close();
			}
		}
	}
}
catch (Exception e)
{
	out.println(HandleException(e));
}
%>


<%!

public static final int IsDefaultType = 0;
public static final int IsParamType = 1;
public static final int IsDataType = 2;

public String GetTables(Connection aConn,String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed())
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				String[] atypes = {"TABLE"};

				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}
				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				return MarshallRecordsetIntoHTML(dm.getTables(CatalogName,SchemaName,null,atypes));
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}

public String GetViews(Connection aConn,String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed())
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				String[] atypes = {"VIEW"};

				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}

				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				return MarshallRecordsetIntoHTML(dm.getTables(CatalogName,SchemaName,null,atypes));
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}

public String GetProcedures(Connection aConn,String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed())
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}

				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				return MarshallRecordsetIntoHTML(dm.getProcedures(CatalogName,SchemaName,null));
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}

public String GetColumns(Connection aConn,String TableName, String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed() && (TableName!=null) && (TableName.length()>0))
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}

				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				return MarshallRecordsetIntoHTML(dm.getColumns(CatalogName,SchemaName,TableName,null));
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}

public String GetPrimaryKeys(Connection aConn,String TableName, String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed() && (TableName!=null) && (TableName.length()>0))
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}

				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				return MarshallRecordsetIntoHTML(dm.getPrimaryKeys(CatalogName,SchemaName,TableName));
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}


public ResultSet GetParametersOfProcedure(Connection aConn,String ProcName, String SchemaName , String CatalogName) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed() && (ProcName!=null) && (ProcName.length()>0))
		{
			DatabaseMetaData dm = aConn.getMetaData();

			if (dm != null)
			{
				if ((CatalogName != null) && (CatalogName.length()==0))
				{
					CatalogName = null;
				}

				if ((SchemaName != null) && (SchemaName.length()==0))
				{
					SchemaName = null;
				}

				int dotIndex = ProcName.lastIndexOf(".");

				if (dotIndex != -1)
				{
					ProcName = ProcName.substring(dotIndex+1,ProcName.length());
				}

				return dm.getProcedureColumns(CatalogName,SchemaName,ProcName,null);
			}
		}
	}
	catch (Exception e)
	{
		HandleException(e);
	}

	return null;
}

public String ExecuteSQL(Connection aConn,String SQL,String MaxRows) 
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed())
		{
			Statement aStatement = aConn.createStatement();
			aStatement.setMaxRows(Integer.parseInt(MaxRows));
			ResultSet aRecordset = aStatement.executeQuery(SQL);
			if (aRecordset != null)
			{
				return MarshallRecordsetIntoHTML(aRecordset);
			}
		}
	}
	catch (Exception e)
	{
		return HandleException(e);
	}

	return null;
}

public String ExecuteSP(JspWriter aOut,Connection aConn,String spName,int timeOut,String spParams,String SchemaName,String CatalogName)
{
	try 
	{
		if ((aConn!=null) && !aConn.isClosed())
		{
			java.util.Hashtable aParamArray=null;

			if(spParams != null && spParams.length() > 0)
			{
				aParamArray = new java.util.Hashtable();

				for (;;)
				{
					int Index = spParams.indexOf(",");

					if(Index == -1)
					{
						Index = spParams.length();
					}

					String name = spParams.substring(0,Index);
					spParams	= spParams.substring(Index+1,spParams.length());

					Index = spParams.indexOf(",");
					if (Index == -1)
					{
						Index = spParams.length();
					}

					String value = spParams.substring(0,Index);

					aParamArray.put(name,value);

					if (Index >= spParams.length())
					{
						break;
					}

					spParams = spParams.substring(Index+1,spParams.length());
				}
			}

			ResultSet paramRS = GetParametersOfProcedure(aConn,spName,SchemaName,CatalogName);

			String aCallStatement="";
			boolean bHasRetVal = false;
			int RefCurIndex = -1;

			if (paramRS != null)
			{
				int i = 0;
				aCallStatement = aCallStatement + "(";
				while (paramRS.next())
				{
					int pType	 = paramRS.getInt("COLUMN_TYPE");

					if(i > 0)
					{
						aCallStatement = aCallStatement + ",";
					}

					if (pType == DatabaseMetaData.procedureColumnReturn)
					{
						bHasRetVal = true;
					}
					else
					{
						aCallStatement = aCallStatement + "?";
						i++;
					}
				}
				paramRS.close();
			}

			aCallStatement = aCallStatement + ")}";


			if(bHasRetVal)
			{
				aCallStatement = "{?=call " + spName + aCallStatement;
			}
			else
			{
				aCallStatement = "{call " + spName + aCallStatement;
			}

			CallableStatement aStatement = aConn.prepareCall(aCallStatement);

			if (aStatement != null)
			{
				paramRS = GetParametersOfProcedure(aConn,spName,SchemaName,CatalogName);
				if (paramRS != null)
				{
					int index = 0;
					while (paramRS.next())
					{
						String pName = paramRS.getString("COLUMN_NAME");
						int pType	 = paramRS.getInt("COLUMN_TYPE");
						int aType	 = Types.LONGVARCHAR;

						String TypeName = paramRS.getString("TYPE_NAME");

						if (TypeName.indexOf("REF CURSOR") != -1)
						{
							RefCurIndex = index + 1;
							aType = -10;//ORACLE.REF CURSOR.
						}

						if (pType == DatabaseMetaData.procedureColumnIn)
						{
							String pValue = aParamArray.get(pName).toString();
							aStatement.setString(index+1,pValue);
						}
						else if (pType == DatabaseMetaData.procedureColumnInOut)
						{
							if (RefCurIndex != index + 1)
							{
								String pValue = aParamArray.get(pName).toString();
								aStatement.setString(index+1,pValue);
							}
							aStatement.registerOutParameter(index+1,aType);
						}
						else if ((pType == DatabaseMetaData.procedureColumnOut) ||
								 (pType == DatabaseMetaData.procedureColumnReturn)) 	
						{
							aStatement.registerOutParameter(index+1,aType);
						}
						index++;
					}
					paramRS.close();
				}

				aStatement.execute();
				ResultSet aResultSet = null;

				if (RefCurIndex != -1)
				{
					aResultSet = (ResultSet)aStatement.getObject(RefCurIndex);
				}
				else
				{
					aResultSet = aStatement.getResultSet();