XML input

Do you specify a DTD or an XML Schema for the XML data input?

DTD or Schema is not necessary. If you have complex XML you may configure table model with XPath expressions. Look at the XML examples in /examples/grid/ directory.

Thanks. That helped. But we have data coming for a database and we have a JDBC layer on top of it. It would really help if there is a way you can accept data in the form of a ResultSet or any other Java Object (any interface that you define should be fine too).
Right now as I see it, we need to convert our data in an XML format and save it in a file (doesn't look like you accept XML data objects either). Do you have any suggestions on how to input a ResultSet to your grid control?

Hi.

I think you are using a servlet or jsp, true? (if not, skip this, please :P )

All you need is a servlet/jsp that get the data from de JDBC layer and outputs it to the response object as xml. then you just point the table to the servlet.

var table = new Active.XML.Table;
table.setProperty("URL","/context/pathtoservlet");

You may need to set the content type to text/xml in the servlet also.

This might be useful?

var table = new Active.XML.Table;
table.setProperty("URL","/SQLServlet?sql=select%20*%20from%20users");

/*
 * SQLServlet.java
 *
 * Created on April 8, 2005, 9:02 AM
 */


import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;


/**This servlet has an obvious security problem but...
 *To use, access the servlet like so
 * /SQLServlet?sql=Select%20*%20from%20users
 *
 * @author bhatt
 * @version
 */
public class SQLServlet extends HttpServlet {
    
    /** Initializes the servlet.
     */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        
    }
    
    /** Destroys the servlet.
     */
    public void destroy() {
        
    }

    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/xml");
        PrintWriter out = response.getWriter();
        Connection conn = null;
        
        try{
            conn = getConnection();
            String sql = request.getParameter("sql");
            Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = stmt.executeQuery(sql);
            // Get the ResultSetMetaData.  This will be used for the column headings
            java.sql.ResultSetMetaData rsmd = rs.getMetaData();

            //  Get the number of columns in the result set
            int numCols = rsmd.getColumnCount();
            //1 based index, the first element will not be used
            String[] columnLabels = new String[numCols+1]; 
            // load array with column headings
            out.print("<xml>\n");// uses the default encoding
            out.print("  <columns>\n");// uses the default encoding
            for(int i = 1;i <=numCols;i++) {
                //incase there are spaces, replace with underscore
                columnLabels[i] = rsmd.getColumnLabel(i).replaceAll("\\w","_"); 
                //You could add length, precision etc.
                out.print("<column type=\"" + getDataType(rsmd.getColumnType(i)) + "\" label=\"" + columnLabels[i] + "\"/>\n");
            }
            out.print("  </columns>\n");
            // Display data, fetching until end of the result set
            out.print("  <rows>\n");

            while(rs.next()) {
                StringBuffer sb = new StringBuffer();
                sb.append("\t<row>\n");

                // Loop through each column, getting the column data and displaying
                for(int i = 1;i <= numCols;i++) {
                    sb.append("\t\t<");
                    sb.append(columnLabels[i]);
                    sb.append(">");
                    //Could do better handling of different datatypes
                    String data = rs.getString(i);
                    if(!rs.wasNull())
                        sb.append(escapeXMLString(data));
                    sb.append("</");
                    sb.append(columnLabels[i]);
                    sb.append(">\n");
                }
                sb.append("\t</row>");
                out.print(sb.toString());
                // Fetch the next result set row
            }
            out.print("  </rows>\n");
            // end tag, although there may be no content, the doc will still be valid
            out.print("</xml>\n");
            out.close();            
        }catch(Exception e){
            out.print("<xml><error>" + e.getMessage() + "</error></xml>");
            out.close();
            return;
        }finally{
            if(conn!=null)
                try{conn.close();}catch(Exception e){}
        }
    }
    
    
    
   /**Package protected constructor 
     */
   public Connection getConnection()
        throws SQLException,javax.naming.NamingException {
       
       final String DBNAME = "DB"; //Has to match your datasource
      Connection conn = null;  
       DataSource ds = null;
       try{
        Context initContext = new InitialContext();
        try{
            Object ob = (Context)initContext.lookup("java:/comp/env/jdbc/" + DBNAME);
            if(ob!=null&&ob instanceof DataSource){
                ds = (DataSource)ob;
            }
        }catch(Exception e2){
            //log.error("DS Not found in root context java:/comp/env/jdbc/DB");
        }
        Context envContext  = (Context)initContext.lookup("java:/comp/env");
        ds = (DataSource)envContext.lookup("jdbc/" + DBNAME);
        conn = ds.getConnection();
        return conn;
       }catch(SQLException sqlEx) {
            throw (sqlEx);
        }
    }    
    
    
    /** Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Returns a short description of the servlet.
     */
    public String getServletInfo() {
        return "SQLServlet - Security issue, not for production or public use";
    }
    
    
   public static final String getDataType(final int type){
        switch (type){
            case Types.ARRAY:
                return "ARRAY";
            case Types.BIGINT:
                return "BIGINT";
            case Types.BINARY:
                return "BINARY";
            case Types.BIT:
                return "BIT";
            case Types.BLOB:
                return "BLOB";
            case Types.BOOLEAN:
                return "BOOLEAN";
            case Types.CHAR:
                return "CHAR";
            case Types.CLOB:
                return "CLOB";
            case Types.DATALINK:
                return "DATALINK";
            case Types.DATE:
                return "DATE";
            case Types.DECIMAL:
                return "DECIMAL";
            case Types.DISTINCT:
                return "DISTINCT";
            case Types.DOUBLE:
                return "DOUBLE";
            case Types.FLOAT:
                return "FLOAT";
            case Types.INTEGER:
                return "INTEGER";
            case Types.JAVA_OBJECT:
                return "JAVA_OBJECT";
            case Types.LONGVARBINARY:
                return "LONGVARBINARY";
            case Types.LONGVARCHAR:
                return "LONGVARCHAR";
            case Types.NULL:
                return "NULL";
            case Types.NUMERIC:
                return "NUMERIC";
            case Types.REAL:
                return "REAL";
            case Types.SMALLINT:
                return "SMALLINT";
            case Types.STRUCT:
                return "STRUCT";
            case Types.TIME:
                return "TIME";
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.TINYINT:
                return "TINYINT";
            case Types.VARBINARY:
                return "VARBINARY";
            case Types.VARCHAR:
                return "VARCHAR";
            default:
                return "VARCHAR";
        }
    }

    
   //Credit for escape code goes to http://64.233.161.104/search?q=cache:9mbBk5Gw-DIJ:www.galileocomputing.de/openbook/javainsel2/java_170027.htm+htmlchars%5B%27%5Cu0088%27%5D%3Dhtmlchars%5B%27%5Cu008D%27%5D%3Dhtmlchars%5B%27%5Cu008E%27%5D&hl=en
    private static String htmlchars[] = new String[256];

     static{
       String entry[] = {
         "nbsp", "iexcl", "cent", "pound", "curren", "yen", "brvbar",
         "sect", "uml", "copy", "ordf", "laquo", "not", "shy", "reg",
         "macr", "deg", "plusmn", "sup2", "sup3", "acute", "micro",
         "para", "middot", "cedil", "sup1", "ordm", "raquo", "frac14",
         "frac12", "frac34", "iquest",
         "Agrave", "Aacute", "Acirc", "Atilde", "Auml", "Aring", "AElig",
         "CCedil", "Egrave", "Eacute", "Ecirc", "Euml", "Igrave", "Iacute",
         "Icirc", "Iuml", "ETH", "Ntilde", "Ograve", "Oacute", "Ocirc",
         "Otilde", "Ouml","times", "Oslash", "Ugrave", "Uacute", "Ucirc",
         "Uuml", "Yacute", "THORN", "szlig",
         "agrave", "aacute", "acirc", "atilde", "auml", "aring", "aelig",
         "ccedil", "egrave", "eacute", "ecirc", "euml", "igrave", "iacute",
         "icirc", "iuml", "eth", "ntilde", "ograve", "oacute", "ocirc",
         "otilde", "ouml", "divid", "oslash", "ugrave", "uacute", "ucirc",
         "uuml", "yacute", "thorn", "yuml"
       };

       htmlchars['&'] = "&amp;";
       htmlchars['<'] = "&lt;";
       htmlchars['>'] = "&gt;";

       for ( int c = '\u00A0', i=0 ; c <= '\u00FF'; c++, i++ )
         htmlchars[c] = "&"+entry[i]+";";

       for ( int c = '\u0083', i=131 ; c <= '\u009f'; c++, i++ )
         htmlchars[c] = "&#"+i+";";

       htmlchars['\u0088']=htmlchars['\u008D']=htmlchars['\u008E'] = null;
       htmlchars['\u008F']=htmlchars['\u0090']=htmlchars['\u0098'] = null;
       htmlchars['\u009D'] = null;
     }

     public static String escapeXMLString( String s ){
       int len = s.length();
       StringBuffer sb = new StringBuffer(len*5/4);

       for ( int i = 0; i < len; i++ ){
         char c = s.charAt( i );
         String elem = null;
         if(c=='&'){
            if(i+1<len){
                if(s.charAt(i+1)=='#')
                    elem = "&";
                if(i+4<len){
                    if(s.charAt(i+4)==';')
                        elem = "&";
                    if(i+5<len){
                        if(s.charAt(i+5)==';')
                            elem = "&";
                        if(i+6<len){
                            if(s.charAt(i+6)==';')
                                elem = "&";
                            if(i+7<len){
                                if(s.charAt(i+7)==';')
                                    elem = "&";
                            }
                        }
                    }
                }
            }else
                elem = htmlchars[c&0xff];
         }else if((int)c>128)
            elem = "&#"+((int)c)+";";
         else
             elem = htmlchars[c&0xff];
         sb.append( elem == null ? ""+c : elem );
        }
        return sb.toString();
     }    
    
}

Obviously that example is very dangerous. It would be easy to maliciously insert SQL into the client side code.

I am after a way of inserting a string representation of an XML doc into the grid. The grid would be generated by a JSP. An external XML file is not convenient, though an external servlet might work OK. I would like to do something like (using JSTL tags)


table.setXML("<c:out value="${bean.document}" />");

The bean.document would call the getDocument method, which would retrieve an XML string (though a DOM object would be even better)

<script>alert('dss');</script>