Below is a Java Server Page (JSP) that I wrote that can be used as a SQL Responder running in a Java Web Server such as Apache Tomcat. The JSP is expecting several input parameters: sqlquery, maxrows, querytype, etc. via a http post protocol. The servlet returns a tcl list to the calling process, usually a Tcl Interpreter. The technologies used in the servlet are JDBC and Jacl. I would not recommend using this serlvet over the internet because of security reasons. You could update the servlet to use SSL or https. If anyone has ideas on how to do update the servlet to make it more secure using https then feel free to update the code below. One nice thing about JSPs or Java Servlets are that they are multi-threaded by nature. This JSP engine should be able to handle many concurrent requests. Scott Nichols. scott.nichols@attbi.com

 // Start JSP Page
 <%@ page import="java.io.*" %>
 <%@ page import="java.util.*" %>
 <%@ page import="java.sql.*" %>
 <%@ page import="tcl.lang.*" %>

 <%
 String sSQLQuery = "";
 String sError = "";
 String sMessage = "";
 Connection con;
 Statement stmt;
 int i=0;
 ResultSet rs;
 String sbgcolor = "#D3D3D3";
 String sFontcolor = "White";

 String sTable = "";
 TclObject   row;
 TclObject	rows=null;
 Interp interp;
 interp = new Interp();

 if (request.getParameter("sqlquery") != null)
 {
	// Read the SQL Query
	sSQLQuery = request.getParameter("sqlquery");

	try {
		// Load the standard JDBC driver provided by SUN
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		try {
			// Read the connection string, username and password posted from the HTML form.
			if ((request.getParameter("connection") != null) &&
			    (request.getParameter("username") != null) &&
			    (request.getParameter("password") != null))

				// Connect to the ODBC datasource.
				con = DriverManager.getConnection(request.getParameter("connection"),request.getParameter("username"),request.getParameter("password"));
			else
				con = DriverManager.getConnection("jdbc:odbc:Northwind","sa","");

			// Create a connection statment.
			stmt = con.createStatement();

			String queryType;

			if (request.getParameter("querytype") != null)
				queryType = request.getParameter("querytype");
			else
				queryType = "select";

			// Do we do a Select SQL Query or  an Update, Delete or Insert SQL Query?
			if ( queryType.compareTo("select") == 0)
			{

				// Read how many SQL rows the user wants returned.
				if (request.getParameter("maxrows") != null)
					stmt.setMaxRows(Integer.parseInt(request.getParameter("maxrows")));
				else
					stmt.setMaxRows(1000);

				// Submit the SQL Select Query
				rs = stmt.executeQuery(sSQLQuery);
				ResultSetMetaData meta = rs.getMetaData();

				// Parse the column headers from the result set (meta-data) and build the header
				// of the HTML table
				int iColumns = meta.getColumnCount();
				rows = TclList.newInstance();
				row = TclList.newInstance();
				TclList.append(interp, row,TclString.newInstance("SUCCEEDED"));
				TclList.append(interp, rows,row);
				row = TclList.newInstance();
				for (int colNumber=1; colNumber<=iColumns; colNumber++)
				{
					// sTable = sTable + "<TD><B><font color=\"Yellow\">" + meta.getColumnLabel(colNumber) + "</font></B></TD>\n";
					TclList.append(interp, row,TclString.newInstance(meta.getColumnLabel(colNumber)));
				}
				TclList.append(interp, rows,row);

				// Build the body of the HTML Table
				while (rs.next())
				{
					row = TclList.newInstance();
					for (int colNumber=1; colNumber<=iColumns; colNumber++)
					{
						// sTable = sTable + "<TD><B><font color=\"" + sFontcolor + "\">" + rs.getString(colNumber) + "</font></B></TD>\n";
						TclList.append(interp, row,TclString.newInstance(rs.getString(colNumber)));
					}
					TclList.append(interp, rows,row);
				}

				// Close the result set
				rs.close();
			}
			else
			{
				// Execute an UPDATE, DELETE, or Insert Query.
				i = stmt.executeUpdate(sSQLQuery);
			}
			// Close the open database connections.
			stmt.close();
			con.close();
		}
		catch (SQLException se) {
			System.out.println("SqlException:" + se.getMessage());
			se.printStackTrace(System.out);
			sError =  "SqlException:" + se.getMessage();

			rows = TclList.newInstance();

			TclList.append(interp, rows,TclString.newInstance("FAILED"));
			TclList.append(interp, rows,TclString.newInstance(se.getMessage()));
		}
	}
	catch (ClassNotFoundException e) {
		System.out.println("ClassNotFound:" + e.getMessage());
		sError = "ClassNotFound:" + e.getMessage();

		rows = TclList.newInstance();

		TclList.append(interp, rows,TclString.newInstance("FAILED"));
		TclList.append(interp, rows,TclString.newInstance(e.getMessage()));

	}
 }
 %>
 <%=rows.toString()%>

And here's some example Tcl code on how to call the JSP SQLServlet using http protocol. This example requires a ODBC DSN connection created on the server where the JSP is running. Scott Nichols

 package require http

 puts "--------------------------------Start http SQL Request--------------------------------------------------------------"

 # Format the JSP URL
 set postData [::http::formatQuery sqlquery "Select * from Orders" connection "jdbc:odbc:Northwind" username sa  password "" querytype select maxrows 50]

 puts $postData

 set response [::http::geturl "http://Insert JSP URL" -query $postData]

 upvar #0 $response state

 puts $state(status)
 puts $state(body)

 if { [string match "ok" $state(status)] } {

 set status [lindex $state(body) 0]

 if { [string match "SUCCEEDED" $status] } {

    set rows [string trim $state(body)]

    if {[llength $rows] > 1} {
        set bFailed 0
        set P_status succeeded
        set P_result $rows
    } else {
        # This is a nomatch condition
        set P_status failed
        lappend P_status nomatch
    }

 } else {
    set P_status failed
    lappend P_status [lindex $state(body) 1]
 }

 } else {
    set P_status failed
 }


 puts "--------------------------------End http SQL Request----------------------------------------------------------------"

Please rename this page to SQLServlet. Thanks.

[Category Jacl]