[opencms-dev] MsSQL meta data dump - jsp

Fri Apr 25 18:24:39 CEST 2008

I have compiled this little jsp snippet and I'm posting it here
for what its worth.

Reason was to find out what datatype the user_lastlogin field in the 
cms_users table is. Its a bigint. But what kind of date is that ?

I logged into my workplace locally on my notebook OpenCms system a
couple of minutes ago and see a user_lastlogin=1209138714187

Here is the jsp that dumps the whole schema of the MySql database:

<%@page import="org.opencms.jsp.*,org.opencms.file.*,java.util.*,java.sql.*,java.lang.Math" %>
<%@ taglib prefix="cms" uri="http://www.opencms.org/taglib/cms" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<%
out.println("<head>");
out.println("<meta http-equiv=\"content-type\"
content=\"application/xhtml+xml;charset=iso-8859-1\" />");
      out.println("<title>Simple MySql OpenCMS Example</title>");
out.println("<style type=\"text/css\" media=\"screen\">");
out.println("body {
font-family:Courier;font-size:12px;margin:0;padding:0;border:0}");
out.println("table.my { margin:50px 0 0 50px;border-collapse : collapse;
border: 1px solid #ccc; }");
out.println("td.colnam {border-collapse:collapse;border: 1px solid
#ccc;font-size:90%;text-align:center;width:38em;}");
out.println("td.nam {  border-collapse:collapse;border: 1px solid
#ccc;font-size:70%;text-align:left; width:28em;}");
out.println("td.type { border-collapse:collapse;border: 1px solid
#ccc;font-size:70%;text-align:left; }");
out.println("</style>");
out.println("</head>");
out.println("<body>");

      String url = "jdbc:mysql://localhost:3306/";
      String db = "opencms";
      String driver = "com.mysql.jdbc.Driver";

      Connection conn = null;
      try
      {
         Class.forName(driver);

         conn = DriverManager.getConnection(url+db,"root","mysqlb3");
         out.println("<h3>Connected!</h3>");
         Statement stmt = conn.createStatement();
         Statement stmt1 = conn.createStatement();

         ResultSet rs = stmt.executeQuery("SELECT table_name FROM
INFORMATION_SCHEMA.TABLES");

         //Print start of table and column headers
         out.println("<p>Table_name</p>\n");

         //Loop through results of query.
         while(rs.next())
         {
            out.println("<table class=\"my\">");
            String temp=rs.getString("table_name");
            out.println("<tr>");
            out.println("<td class=\"colnam\" colspan=\"2\" ><b>" + temp
+ "</b></td></tr>");
            ResultSet cs = stmt1.executeQuery("SELECT
data_type,column_name FROM INFORMATION_SCHEMA.columns where table_name =
"  + "'" + temp + "'");
                    while(cs.next()) {
                         out.println("<tr><td class=\"nam\">" +
cs.getString(2)+ "  </td><td class=\"type\">" + cs.getString(1) +
"</td></tr>");
                    }
            out.println("</table>\n");

         }
      }
      catch(SQLException e)
      {
         out.println("SQLException: " + e.getMessage() + "<br/>");

         while((e = e.getNextException()) != null)
            out.println(e.getMessage() + "<br/>");

      }
      finally
      {
         //Clean up resources, close the connection.
         if(conn != null)
         {
            try
            {
               conn.close();
            }
            catch (Exception ignored) {}
         }
      }

      out.println("<p>Grüße Christoph Kukulies</p>");
      out.println("</body>");
      out.println("</html>");

%>

</body>
</html>