/* * $Id: JdbcGateway.java,v 1.8 1997/08/12 12:20:45 ak Exp $ * * Copyright 1997 Hewlett-Packard Company * * This file may be copied, modified and distributed only in * accordance with the terms of the limited licence contained * in the accompanying file LICENSE.TXT. */ package hplb.servlets; import java.net.URL; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; import java.util.Hashtable; import java.util.Properties; import java.io.IOException; /** * This servlet lets a Web browser connect to a * JDBC database accessible * from the server. The user can display tables, get database metadata, * and submit arbitrary SQL statements to the database. * *

Databases are identified by their JDBC URL. To connect to an * ODBC datasource using Suns JDBC-to-ODBC gateway you'd use something * like "jdbc:odbc:mydb". * * @author Anders Kristensen * *

* Fixed bug in passing user and password. * Added support for database property, typically used with MS SQL Server. * @author Per Lundholm, ArtComputer, Sweden */ public class JdbcGateway extends HttpServlet { // This info is cached between invocations for the common case // when the same database is connected to by the same person. private Connection con; private DatabaseMetaData dbmd; private String datasource; private String user; private String password; private String base; // eg "/servlets/jdbc-gw" private ServletOutputStream out; /* String[] types = { "BIGINT", "BINARY", "BIT", "CHAR", "DATE", "DECIMAL", "DOUBLE", "FLOAT", "INTEGER", "LONGVARBINARY", "LONGVARCHAR", "NULL", "NUMERIC", "OTHER", "REAL", "SMALLINT", "TIME", "TIMESTAMP", "TINYINT", "VARBINARY", "VARCHAR" }; */ /* * Note that all requests go through and are serialized by service(). * Some methods share state and thus depend on this. */ public synchronized void service(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { String src; String usr; String pass; String lDatabase; String op; ResultSet rs = null; base = req.getServletPath(); out = res.getOutputStream(); op = (String) req.getParameter("op"); src = (String) req.getParameter("datasource"); usr = (String) req.getParameter("user"); pass = (String) req.getParameter("password"); lDatabase = (String) req.getParameter("database"); if (usr == null) usr = ""; if (pass == null) pass = ""; /* System.out.println("base: " + base); System.out.println("op: " + op); System.out.println("datasource: " + src); System.out.println("user: " + usr); System.out.println("password: " + pass); */ if (op == null) { /* * Top page - ask for JDBC URL. */ out.print( "JDBC gateway\n"+ "\n

JDBC gateway

\n"+ "\n"+ "This servlet lets you view and manipulate using SQL any ODBC\n"+ "datasource configured on the Web server (modulo the database being\n"+ "password protected, of course). This gateway uses the generic\n"+ "Sun-Intersolv JDBC-to-ODBC bridge.

\n"+ "\n"+ "

\n" + "\n" + "\n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + " \n" + "
Data source:
User:
Password:
Database:
\n" + "\n" + "\n" + "
\n"); htmlEnd(out); return; } if (con == null || !datasource.equals(src) || !user.equals(usr) || !password.equals(pass)) { try { if (con != null) { try { con.close(); } catch (Exception e) {} } datasource = src; Properties lProp = new java.util.Properties(); lProp.put("user", usr); lProp.put("password", pass); if(lDatabase != null) lProp.put("database", lDatabase); con = DriverManager.getConnection(datasource, lProp); // System.out.println("Opened connection: " + con); user = usr; password = pass; } catch (SQLException e) { con = null; htmlStart(out, "JDBC gateway error"); out.print("Couldn't connect to datasource " + datasource + "

\n\n"); printSQLException(e, out); out.print("

User: '" + usr + "'password: '" + pass + "'"); htmlEnd(out); return; } catch (Exception e) { con = null; htmlStart(out, "JDBC gateway error"); out.print("Couldn't connect to datasource " + datasource + "

\n\n"); out.print(e.toString()); htmlEnd(out); } try { dbmd = con.getMetaData(); } catch (SQLException e) { throw new ServletException(e.getMessage()); } } // else { // System.out.println("Reusing \"old\" connection: " + con); // } if ("capabilities".equalsIgnoreCase(op)) { htmlStart(out, datasource + " capabilities"); try { printCapabilities(dbmd); } catch (SQLException e) { out.print("\nInternal error... giving up!\n\n"); } printTopLink(); htmlEnd(out); } else if ("dispTable".equalsIgnoreCase(op)) { /* * Write database table */ boolean isQueryRes = false; String table = (String) req.getParameter("table"); String query = (String) req.getParameter("query"); if (query != null) { isQueryRes = true; htmlStart(out, "Result of SQL expression"); } else { query = "SELECT * FROM " + table; htmlStart(out, table); } System.out.println("Query: " + query); Statement stmt = null; try { stmt = con.createStatement(); String sqlop = query.substring(0, 6); if ("SELECT".equalsIgnoreCase(sqlop)) { rs = stmt.executeQuery(query); if (isQueryRes) { out.print("Result of query \"" + query + "\":

\n"); } else { out.print("Table " + table + ":

\n"); } resultSetToHtmlTable(rs, out); rs.close(); } else if ("INSERT".equalsIgnoreCase(sqlop) || "UPDATE".equalsIgnoreCase(sqlop) || "DELETE".equalsIgnoreCase(sqlop)) { stmt.executeUpdate(query); //con.commit(); //checkForWarning(con.getWarnings()); out.print("

Insert/update OK

"); } } catch (SQLException e) { printSQLException(e, out); return; } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) {} try { stmt.close(); } catch (Exception ex) {} rs = null; } } String dfltQuery; if (isQueryRes) dfltQuery = query; else dfltQuery = "SELECT *\r\nFROM " + table + "\r\n"; printSQLForm(datasource, user, password, dfltQuery); printTopLink(); htmlEnd(out); } else if ("overview".equalsIgnoreCase(op)) { /* * Write list of tables (and other stuff) */ String table; htmlStart(out, datasource); out.print("

JDBC gateway: " + datasource + "

"); try { out.print("Connected to: " + dbmd.getURL() + "
\n"); out.print("Database product:" + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion() + "
\n"); out.print("Username:" + dbmd.getUserName() + "
\n"); out.print("More metadata info.\n

\n\n"); out.print("Tables in database:
\n"); out.print("

\n"); rs = dbmd.getTables(null, null, null, null); while (rs.next()) { table = rs.getString("TABLE_NAME"); out.print("
  • " + table + "
    \n"); } out.print("
    • \n"); printSQLForm(datasource, user, password, "SELECT *\r\nFROM table\r\n"); } catch (SQLException e) { out.println("Internal error: " + e.getMessage()); } finally { try { rs.close(); } catch (Exception ex) {} } htmlEnd(out); } } /** Generates HTML link to top page. */ public void printTopLink() throws IOException { out.print("

    \n\nTo the top\n\n"); } /** Generates HTML form for entering SQL expression. */ public void printSQLForm(String datasource, String user, String password, String dfltQuery) throws IOException { out.print("

    \n 

    \nSQL query:
    \n"); out.print("

    \n"); out.print("\n"); out.print("\n"); if (user != null) { out.print("\n"); } if (password != null) { out.print("\n"); } out.print("\n\n
    \n\n"); } public void init(ServletConfig config) throws ServletException { super.init(config); try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch (ClassNotFoundException e) { log("Couldn't load class sun.jdbc.odbc.JdbcOdbcDriver"); throw new ServletException(e.getMessage()); } } static void printSQLException(SQLException e, ServletOutputStream out) throws IOException { out.print("SQLException\n

    \n\n"); while (e != null) { out.print("SQLState: " + e.getSQLState() + "
    \n"); out.print("Message: " + e.getMessage() + "
    \n"); out.print("Vendor: " + e.getErrorCode() + "
    \n"); e = e.getNextException(); } } private static boolean checkForWarning (SQLWarning warn) throws SQLException { boolean rc = false; // If a SQLWarning object was given, display the // warning messages. Note that there could be // multiple warnings chained together if (warn != null) { System.out.println ("\n *** Warning ***\n"); rc = true; while (warn != null) { System.out.println ("SQLState: " + warn.getSQLState ()); System.out.println ("Message: " + warn.getMessage ()); System.out.println ("Vendor: " + warn.getErrorCode ()); System.out.println (""); warn = warn.getNextWarning (); } } return rc; } void o(String name, String val) throws IOException { out.print("" + name + ": " + val + "
    \n"); } void o(String name, Object val) throws IOException { o(name, val.toString()); } void o(String name, boolean val) throws IOException { o(name, val ? "true" : "false"); } void o(String name, int val) throws IOException { o(name, Integer.toString(val)); } public void printCapabilities(DatabaseMetaData dbmd) throws SQLException { ResultSet rs; try { o("allProceduresAreCallable", dbmd.allProceduresAreCallable()); o("allTablesAreSelectable", dbmd.allTablesAreSelectable()); o("getURL", dbmd.getURL()); o("getUserName", dbmd.getUserName()); o("isReadOnly", dbmd.isReadOnly()); o("nullsAreSortedHigh", dbmd.nullsAreSortedHigh()); o("nullsAreSortedLow", dbmd.nullsAreSortedLow()); o("nullsAreSortedAtStart", dbmd.nullsAreSortedAtStart()); o("nullsAreSortedAtEnd", dbmd.nullsAreSortedAtEnd()); o("getDatabaseProductName", dbmd.getDatabaseProductName()); o("getDatabaseProductVersion", dbmd.getDatabaseProductVersion()); o("getDriverName", dbmd.getDriverName()); o("getDriverVersion", dbmd.getDriverVersion()); o("getDriverMajorVersion", dbmd.getDriverMajorVersion()); o("getDriverMinorVersion", dbmd.getDriverMinorVersion()); o("usesLocalFiles", dbmd.usesLocalFiles()); o("usesLocalFilePerTable", dbmd.usesLocalFilePerTable()); o("supportsMixedCaseIdentifiers", dbmd.supportsMixedCaseIdentifiers()); o("storesUpperCaseIdentifiers", dbmd.storesUpperCaseIdentifiers()); o("storesLowerCaseIdentifiers", dbmd.storesLowerCaseIdentifiers()); o("storesMixedCaseIdentifiers", dbmd.storesMixedCaseIdentifiers()); o("supportsMixedCaseQuotedIdentifiers", dbmd.supportsMixedCaseQuotedIdentifiers()); o("storesUpperCaseQuotedIdentifiers", dbmd.storesUpperCaseQuotedIdentifiers()); o("storesLowerCaseQuotedIdentifiers", dbmd.storesLowerCaseQuotedIdentifiers()); o("storesMixedCaseQuotedIdentifiers", dbmd.storesMixedCaseQuotedIdentifiers()); o("getIdentifierQuoteString", dbmd.getIdentifierQuoteString()); o("getSQLKeywords", dbmd.getSQLKeywords()); o("getNumericFunctions", dbmd.getNumericFunctions()); o("getStringFunctions", dbmd.getStringFunctions()); o("getSystemFunctions", dbmd.getSystemFunctions()); o("getTimeDateFunctions", dbmd.getTimeDateFunctions()); o("getSearchStringEscape", dbmd.getSearchStringEscape()); o("getExtraNameCharacters", dbmd.getExtraNameCharacters()); o("supportsAlterTableWithAddColumn", dbmd.supportsAlterTableWithAddColumn()); o("supportsAlterTableWithDropColumn", dbmd.supportsAlterTableWithDropColumn()); o("supportsColumnAliasing", dbmd.supportsColumnAliasing()); o("nullPlusNonNullIsNull", dbmd.nullPlusNonNullIsNull()); o("supportsConvert", dbmd.supportsConvert()); o("supportsTableCorrelationNames", dbmd.supportsTableCorrelationNames()); o("supportsDifferentTableCorrelationNames", dbmd.supportsDifferentTableCorrelationNames()); o("supportsExpressionsInOrderBy", dbmd.supportsExpressionsInOrderBy()); o("supportsOrderByUnrelated", dbmd.supportsOrderByUnrelated()); o("supportsGroupBy", dbmd.supportsGroupBy()); o("supportsGroupByUnrelated", dbmd.supportsGroupByUnrelated()); o("supportsGroupByBeyondSelect", dbmd.supportsGroupByBeyondSelect()); o("supportsLikeEscapeClause", dbmd.supportsLikeEscapeClause()); o("supportsMultipleResultSets", dbmd.supportsMultipleResultSets()); o("supportsMultipleTransactions", dbmd.supportsMultipleTransactions()); o("supportsNonNullableColumns", dbmd.supportsNonNullableColumns()); o("supportsMinimumSQLGrammar", dbmd.supportsMinimumSQLGrammar()); o("supportsCoreSQLGrammar", dbmd.supportsCoreSQLGrammar()); o("supportsExtendedSQLGrammar", dbmd.supportsExtendedSQLGrammar()); o("supportsANSI92EntryLevelSQL", dbmd.supportsANSI92EntryLevelSQL()); o("supportsANSI92IntermediateSQL", dbmd.supportsANSI92IntermediateSQL()); o("supportsANSI92FullSQL", dbmd.supportsANSI92FullSQL()); o("supportsIntegrityEnhancementFacility", dbmd.supportsIntegrityEnhancementFacility()); o("supportsOuterJoins", dbmd.supportsOuterJoins()); o("supportsFullOuterJoins", dbmd.supportsFullOuterJoins()); o("supportsLimitedOuterJoins", dbmd.supportsLimitedOuterJoins()); o("getSchemaTerm", dbmd.getSchemaTerm()); o("getProcedureTerm", dbmd.getProcedureTerm()); o("getCatalogTerm", dbmd.getCatalogTerm()); o("isCatalogAtStart", dbmd.isCatalogAtStart()); o("getCatalogSeparator", dbmd.getCatalogSeparator()); o("supportsSchemasInDataManipulation", dbmd.supportsSchemasInDataManipulation()); o("supportsSchemasInProcedureCalls", dbmd.supportsSchemasInProcedureCalls()); o("supportsSchemasInTableDefinitions", dbmd.supportsSchemasInTableDefinitions()); o("supportsSchemasInIndexDefinitions", dbmd.supportsSchemasInIndexDefinitions()); o("supportsSchemasInPrivilegeDefinitions", dbmd.supportsSchemasInPrivilegeDefinitions()); o("supportsCatalogsInDataManipulation", dbmd.supportsCatalogsInDataManipulation()); o("supportsCatalogsInProcedureCalls", dbmd.supportsCatalogsInProcedureCalls()); o("supportsCatalogsInTableDefinitions", dbmd.supportsCatalogsInTableDefinitions()); o("supportsCatalogsInIndexDefinitions", dbmd.supportsCatalogsInIndexDefinitions()); o("supportsCatalogsInPrivilegeDefinitions", dbmd.supportsCatalogsInPrivilegeDefinitions()); o("supportsPositionedDelete", dbmd.supportsPositionedDelete()); o("supportsPositionedUpdate", dbmd.supportsPositionedUpdate()); o("supportsSelectForUpdate", dbmd.supportsSelectForUpdate()); o("supportsStoredProcedures", dbmd.supportsStoredProcedures()); o("supportsSubqueriesInComparisons", dbmd.supportsSubqueriesInComparisons()); o("supportsSubqueriesInExists", dbmd.supportsSubqueriesInExists()); o("supportsSubqueriesInIns", dbmd.supportsSubqueriesInIns()); o("supportsSubqueriesInQuantifieds", dbmd.supportsSubqueriesInQuantifieds()); o("supportsCorrelatedSubqueries", dbmd.supportsCorrelatedSubqueries()); o("supportsUnion", dbmd.supportsUnion()); o("supportsUnionAll", dbmd.supportsUnionAll()); o("supportsOpenCursorsAcrossCommit", dbmd.supportsOpenCursorsAcrossCommit()); o("supportsOpenCursorsAcrossRollback", dbmd.supportsOpenCursorsAcrossRollback()); o("supportsOpenStatementsAcrossCommit", dbmd.supportsOpenStatementsAcrossCommit()); o("supportsOpenStatementsAcrossRollback", dbmd.supportsOpenStatementsAcrossRollback()); o("getMaxBinaryLiteralLength", dbmd.getMaxBinaryLiteralLength()); o("getMaxCharLiteralLength", dbmd.getMaxCharLiteralLength()); o("getMaxColumnNameLength", dbmd.getMaxColumnNameLength()); o("getMaxColumnsInGroupBy", dbmd.getMaxColumnsInGroupBy()); o("getMaxColumnsInIndex", dbmd.getMaxColumnsInIndex()); o("getMaxColumnsInOrderBy", dbmd.getMaxColumnsInOrderBy()); o("getMaxColumnsInSelect", dbmd.getMaxColumnsInSelect()); o("getMaxColumnsInTable", dbmd.getMaxColumnsInTable()); o("getMaxConnections", dbmd.getMaxConnections()); o("getMaxCursorNameLength", dbmd.getMaxCursorNameLength()); o("getMaxIndexLength", dbmd.getMaxIndexLength()); o("getMaxSchemaNameLength", dbmd.getMaxSchemaNameLength()); o("getMaxProcedureNameLength", dbmd.getMaxProcedureNameLength()); o("getMaxCatalogNameLength", dbmd.getMaxCatalogNameLength()); o("getMaxRowSize", dbmd.getMaxRowSize()); o("doesMaxRowSizeIncludeBlobs", dbmd.doesMaxRowSizeIncludeBlobs()); o("getMaxStatementLength", dbmd.getMaxStatementLength()); o("getMaxStatements", dbmd.getMaxStatements()); o("getMaxTableNameLength", dbmd.getMaxTableNameLength()); o("getMaxTablesInSelect", dbmd.getMaxTablesInSelect()); o("getMaxUserNameLength", dbmd.getMaxUserNameLength()); o("getDefaultTransactionIsolation", dbmd.getDefaultTransactionIsolation()); o("supportsTransactions", dbmd.supportsTransactions()); o("supportsDataDefinitionAndDataManipulationTransactions", dbmd.supportsDataDefinitionAndDataManipulationTransactions()); o("supportsDataManipulationTransactionsOnly", dbmd.supportsDataManipulationTransactionsOnly()); o("dataDefinitionCausesTransactionCommit", dbmd.dataDefinitionCausesTransactionCommit()); o("dataDefinitionIgnoredInTransactions", dbmd.dataDefinitionIgnoredInTransactions()); // getProcedures() // getProcedureColumns() // getTables() try { o("getSchemas", resultSetToHtmlTable(dbmd.getSchemas())); } catch (Exception e) {} try { o("

    getCatalogs", resultSetToHtmlTable(dbmd.getCatalogs())); } catch (Exception e) {} try { o("getTableTypes", resultSetToHtmlTable(dbmd.getTableTypes())); } catch (Exception e) {} // getColumns() // getColumnPrivileges() // getTablePrivileges() // getBestRowIdentifier() // getVersionColumns() // getPrimaryKeys() // getImportedKeys() // getExportedKeys() // getCrossReference() //try { o("getTypeInfo", resultSetToHtmlTable(dbmd.getTypeInfo())); // } catch (SQLException e) { printSQLException(e); } ///* try { rs = dbmd.getTables(null, null, null, null); o("getTables: \n", resultSetToHtmlTable(rs).toString()); } catch (SQLException e) { printSQLException(e, out); } //*/ // getIndexInfo() } catch (Exception e) { e.printStackTrace(System.out); // } } /** * Converts ResultSet to HTML format - returned in a StringBuffer. */ private StringBuffer resultSetToHtmlTable(ResultSet rs) throws SQLException { StringBuffer sb = new StringBuffer(); int i; ResultSetMetaData rsmd = rs.getMetaData (); int numCols = rsmd.getColumnCount (); // column headings sb.append("\n\n" + " \n"); for (i = 1; i <= numCols; i++) { sb.append(" \n"); } sb.append(" \n"); while (rs.next()) { sb.append(" \n"); for (i = 1; i <= numCols; i++) { sb.append(" \n"); } sb.append(" \n"); } sb.append("
    " + rsmd.getColumnLabel(i) + "
    [" + rsmd.getColumnTypeName(i) + "]
    " + (rs.getString(i)) + "
    \n\n"); return sb; } /** * Writes ResultSet as HTML to the provided PrintStream. */ private void resultSetToHtmlTable(ResultSet rs, ServletOutputStream out) throws SQLException, IOException { StringBuffer sb = new StringBuffer(); int i; ResultSetMetaData rsmd = rs.getMetaData (); int numCols = rsmd.getColumnCount (); // column headings out.print("\n\n" + " \n"); for (i = 1; i <= numCols; i++) { out.print(" \n"); } out.print(" \n"); while (rs.next()) { out.print(" \n"); for (i = 1; i <= numCols; i++) { out.print(" \n"); } out.print(" \n"); } out.print("
    " + rsmd.getColumnLabel(i) + "
    [" + rsmd.getColumnTypeName(i) + "]
    " + (rs.getString(i)) + "
    \n\n"); } void htmlStart(ServletOutputStream out, String title) throws IOException { out.println("" + title + ""); out.println("\n

    " + title + "

    "); } void htmlEnd(ServletOutputStream out) throws IOException { out.print("\n"); } String link(String url, String text) { return "" + text + ""; } public String getServletInfo() { return "Display and SQL manipulate JDBC databases on the server."; } }