Below is a Java Server Page (JSP) that can be used as a Java SQL Responder for Tcl Interpreters. The JSP can run in any Java web server such as Apache Tomcat. The JSP is expecting several input parameters: sqlquery, maxrows, querytype, etc. via http post protocol. The servlet then does a SQL lookup based off of the input parameter passed to it. After the SQL database returns the data to the JSP, it then returns a tcl list via a http response to the calling process, which is usually a Tcl Interpreter, but could be a web browser. 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.
The JSP is expecting the following input parameters via HTTP POST:
1. sqlquery This is the SQL query to send to the DBS. This parameter is required. If it is not included NULL is returned to the calling process. 2. connection This is the JDBC datasource connection string to use. This paramater can be an ODBC DSN connection that exists on the same computer as where the JSP is running. The default value is: "jdbc:odbc:Northwind" 3. username This is the username to login into the DBS with. The default value is "sa" 4. password This is the password to use with the login into the DBS. The default value is NULL or "" 5. querytype This is the type of SQL query that is being run. This can be either select, insert, update, or delete. The default value is select. NOTE: An error may be returned if the SQL query does not match the query type parameter. And even though an error is returned the SQL query may have been successful. For example, if the querytype is set to select, but the sqlquery contents are an UPDATE, INSERT, or DELETE then an error will be returned saying, "no result set", but the query may have been successful. 6. maxrows This is the maximum rows to be returned as a Tcl list. The default value is 1000 rows.
The JSP will return one of the following values as a Tcl list.
Tcl Index 0 is reserved for SUCCEEDED or FAILED. If FAILED is returned an error has occured. Tcl Index 1 will give a description of the error. If Tcl Index 0 has the value SUCCEEDED then Tcl index 1 will contain the meta-data of the SQL query (a.k.a Column Headers,a.k.a Field Names,a.k.a Attributes). The remaing rows, if any, in the Tcl list will contain the result values all converted to text.
Here is the source code for the JSP page. To install the script simply copy the contents of it into your clipboard and then past it into a JSP file and save the file into a working Java Web Server's context. An example of a Java Web Server is Apache Tomcat (which can be downloaded for free at Apache.org[L1 ]). This JSP also requires the Jacl library package to be installed and in the Java's library path.
<%@ 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.equals("select"))
{
// 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 SQL JSP Tcl Responder using http protocol. This example requires a ODBC DSN connection created on the server where the JSP is running.
package require http
puts "--------------------------------SQL JSP Tcl Responder--------------------------------------------------------------"
# 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 JSP Tcl Responder ----------------------------------------------------------------"I have been asked to provide what's the purpose of the above JSP. What I had in mind was the JSP allows multiple Tcl Interpreters running on remote computers to do SQL queries via http without having the ODBC drivers installed on their computers. Also, the SQL being returned is in the form of Tcl list and has meta-data (column headers) included. The meta-data and returned values could be used to create dynamic Tk forms. For example, you could program a Tk form with dynamic labels and have the text fields populated with the result from the SQL. And, you could then add a Tk button to allow a user to update the records in the database from the same dynamic form.
[ Category Java | Jacl ]