Ten minutes hacking (check it :-)

This is a ten minute piece of code, it's over powerfull, you ought to
check it :-)

It's an extension to SSI, that will allow you to create templates that
access a database. A sample example will explain it better:

<html>
<head>
<title>Database SSI</title>
</head>
<body>
<h1>Database SSI</h1>
<p>This Server Side Include extension allows you to query a database
   (which I am doing right now) 
<!-- First step, make a query to the database -->
<!--#jdbc select="SELECT * FROM users" name="result" driver="imaginary.sql.iMsqlDriver" url="jdbc:msql://www43.inria.fr:4333/demo" -->

<!-- Second Step, skip to first row -->
<!--#jdbc name="result" next="true" -->

<!-- Third step: have fun with the results -->
	<p>The query has run, here is the first result:
	<p>First  row: <!--#jdbc name="result" column="1" -->
	<p>Second row: <!--#jdbc name="result" column="2" -->
</body>
</html>

I am all over excited, you have to understand that as SSIResources can
be nested, you can get the template from a database, and have it
filled through the database, not to mention things like per-client
page customization...

Anselm.
-----
For those of you who want to share fun, here is *all* the code needed !
BTW It needs improvement, of course; don't forget to register the new
command in some command registry, ie add in
DefaultCommandRegistry.java :

    private static Command[] cmds =
    {
	new ConfigCommand(),
	new IncludeCommand(),
	new EchoCommand(),
	new FSizeCommand(),
	new FLastModCommand(),
	new ExecCommand(),
	new SampleCommand(),
	new CountCommand(),
	new CookieCommand(),
	new w3c.jigsaw.ssi.jdbc.jdbcCommand()
    };

    

---- w3c.jigsaw.ssi.jdbc.jdbcCommand
package w3c.jigsaw.ssi.jdbc;

import java.util.*;
import java.sql.*;

import w3c.www.http.*;
import w3c.jigsaw.http.*;
import w3c.jigsaw.resources.*;
import w3c.util.*;
import w3c.jigsaw.ssi.*;

public class jdbcCommand implements Command {
    private final static String  NAME  = "jdbc";
    private final static boolean debug = true;
    
    private static final String keys[] = {
	"select",
	"url",
	"driver",
	"name",
	"column",
	"next"
    };


    protected Connection getConnection(String driver, String url) {
	try {
	    Class.forName(driver);
	    return DriverManager.getConnection(url, "abaird", "");
	} catch (Exception ex) {
	    ex.printStackTrace();
	}
	return null;
    }

    protected ResultSet performSelect(Connection conn, String cmd) {
	try {
	    Statement smt = conn.createStatement();
	    ResultSet set = smt.executeQuery(cmd);
	    return set;
	} catch (Exception ex) {
	    ex.printStackTrace();
	}
	return null;
    }

    protected void addSet(Dictionary d, String name, ResultSet set) {
	d.put(getClass().getName()+"."+name, set);
    }

    protected ResultSet getSet(Dictionary d, String name) {
	return (ResultSet) d.get(getClass().getName()+"."+name);
    }

    public String getName() {
	return NAME;
    }


    public Reply execute(SSIResource resource
			 , Request request
			 , ArrayDictionary parameters
			 , Dictionary variables) {
	Object values[] = parameters.getMany(keys);
	String select   = (String) values[0];
	String url      = (String) values[1];
	String driver   = (String) values[2];
	String name     = (String) values[3];
	String column   = (String) values[4];
	String next     = (String) values[5];
	String text     = null;

	if ( select != null ) {
	    Connection conn = getConnection(driver, url);
	    if ( conn != null )
		addSet(variables, name, performSelect(conn, select));
	} else if (column != null) {
	    ResultSet set = getSet(variables, name);
	    try {
		if ( set != null ) 
		    text = set.getObject(Integer.parseInt(column)).toString();
	    } catch (Exception ex) {
		ex.printStackTrace();
	    }
	} else if ( next != null ) {
	    ResultSet set = getSet(variables, name);
	    if ( set != null ) {
		try {
		    set.next();
		} catch (Exception ex) {
		    ex.printStackTrace();
		}
	    }
	}

	// We are NOT doing notMod hack here (tricky and useless ?)
	Reply reply = resource.createCommandReply(request, HTTP.OK);
	if ( text != null )
	    reply.setContent(text);
	return reply;
	
    }
}

Received on Thursday, 13 March 1997 07:35:29 UTC