Start	Results	Error

What is it?

Actually "it" is a "they". The "SQL Tool" program is written in both Java as a Servlet and in Perl as a Win32 Perl CGI script. It's free (under the terms of the GNU Public License) for the taking. And, it's useful as-is, both as a technology demonstration and as another gizmo in your developer's toolbox.

Who Needs This Thing?

If you've worked with SQL databases before, you know that the standard low-level SQL tools are pretty primitive. Worse, you usually have to install the native client libraries for the target database on your desktop machine, configure them to find your database, and then install the management tools that rely on them for connectivity. Only then can you actually start working with the database.

For major DBA tasks this effort is worthwhile -- the GUI tools use the expressive power of color graphics to help the DBA make sense of inherently complex tasks. Managing storage and relational objects such as tables, constraints, users, indexes, etc. is much easier when you can see a graph or tree diagram than when all you get is an ASCII table of numbers with column titles which scroll off the screen before you can see them.

But for daily development this is overkill, especially if there is a team of developers involved. Do five people really need to install the DBA tools on their desktop machines just to test questionable SQL query once in a while?

More to the point, not everybody uses Windows 95 or NT, no matter what Microsoft would like you to think. And, in a pinch you may need to use somebody else's machine - for example, if you're giving a demo and there is a data-driven problem that you'd like to fix on the spot to avoid losing face, you can't just stop the demo and install the database client software on the demo machine, especially if it's the client's hardware. What if it's a Windows 3.1 machine with 4MB RAM and Netscape 2.0?

The Web Browser as a Universal Client

The problem is not a matter of platforms or installers, it's a bad architecture. The two-tier "client-server" architecture is inherently flawed for deployment of finished applications too, for all the reasons detailed above. That's why multi-tiered architectures have arisen.

The web browser provides a universal client application, and the web server is a universal server front-end. Behind that we create a web-to-database application, and behind that is the database itself. What do we gain by adding the complexity of two more tiers?

1. Speed. The only data that is returned is query data, plus a small HTML form. You don't have to wait for a dedicated application to launch.
2. Portability. If you have a web browser you can execute arbitrary queries against your database. Security is something to be concerned about here - put this utility in a password-protected directory on your web server...
3. "Hackability". If you find that you're in need of some new functionality you can hack up this little utility to your heart's content. I personally have (the Win32::ODBC version presents known ODBC data sources as a popup for user convenience, and I plan to use cookies to remember JDBC connect strings in the Java Servlet version in the near future). You can't rewrite the database vendor's SQL tool, no matter how clever your idea is.

You also gain a certain degree of security using this model vs. carrying around a laptop with the vendors tools on it, or an applet that does direct 2-tier JDBC to the database. Either of those solutions means that when you roam, your database has to be wide open to direct connections, so the only line of defense against intrusion is your database userid and password.

The 4-tier solution lets you protect the database with a firewall or other means of network security, becase the only client that has to get in is the web-to-db tool running on the server, regardless of where you and your web browser are. If you want to be really secure you can put this tool on a secure (SSL encrypted) web server so no-one can see your userid/password or query data. I doubt you will be able to set up such a secure environment using the database vendor's native network protocol - to my admittedly limited knowledge in this area, none of them support encrypted client->server connections.

So in short, these are simple examples, but in some ways they are far superior to the full-blown vendor-supplied DBA tools if all you need to do is shoot some SQL statements at your database and examine the results. I actually use these things on an almost-daily basis!

Code Walkthrough

The programs are very similar; the Java version was adapted line by line from the Perl version. The main differences are in the database API's (Win32::ODBC vs. JDBC), the request/response API's (CGI vs. Java Servlet), in error handling (if/then vs. exceptions), and in the way I collect data rows and print them (in Perl I collect all the rows, then print them all; in Java I print them one by one as I get them - and there's no good reason why I didn't do it the same way in Java as I did in Perl, I just didn't bother).

The database API's are very similar - first you connect, then you make an SQL statement, then you hand the statement to the connection and hope it works.

$db = new Win32::ODBC($dsn_string);

if ($db)
 {
   if (! $db->Sql($q->param('statement')))

 Connection jdbc_conn = DriverManager.getConnection( param("jdbc_connect_string") );      

 try
   {
     Statement stmt=jdbc_conn.createStatement();
              
     String sql = param("statement");
     
     // hack to decide if this is a select or insert/delete/update
     if ( sql.toLowerCase().startsWith("select") )
       {
         ResultSet rs = stmt.executeQuery(sql);

At this point, the API's differ. In JDBC, if it works and there are query results, then you get a ResultSet object back. It's not a complete set of results in and of itself; it's more an iterator which lets you scroll through your results one row at a time, and which provides a means of finding out "information about the information", also known as meta data, about your results. This meta data can be the row count, column names, number of columns, etc. In JDBC this is isolated into the aptly named ResultSetMetaData class. An instance of ResultSetMetaData is available on a JDBC ResultSet object, via the getMetaData() method.

A side benefit of this separation of the results from the connection is that some (not all!) JDBC drivers let you work with multiple statements and result sets on the same connection at the same time, which can save connections (and thus resources on both sides of the connection).

In Win32::ODBC, query results are accessible via the FetchRow() method of the Win32::ODBC object iself, and the metadata is accessible directly on the Win32::ODBC object also, via FieldNames() and RowCount() etc.

This isn't so great architecture-wise, but then again for most applications you'll want to bury the database connectivity and just get an array of references to hashes (column name -> data) so it's not a big deal in practice.

Anyway, once you have successfully executed your statement and have some query results to deal with, you just have to use next() (JDBC) or FetchRow() to loop over the result set, and then inside the loop you deal with each column:

while ($db->FetchRow())
{
    # "my" makes a new array during each iteration
    # otherwise all references point to one array
    my(@fetched_row) = $db->Data;
    push (@rows, \@fetched_row);
}

print '<TR><TH>'.join ('</TH><TH>',$db->FieldNames)
    .'</TH></TR>';

foreach $row_ref (@rows)
{
    $html_row = "<TR>";
    foreach $value (@{$row_ref})
    {
        $html_row .=  "<TD BGCOLOR='#FFFFFF'>$value</TD>";
    }
    $html_row .= "</TR>\n";
    print $html_row."\n";

for(i = 1; i <= cols; i++)
  {
    print( rsmd.getColumnName(i) + "</TH><TH><FONT SIZE='2'>\n" );
  }
print("</TH></TR>\n");

int rownum = 0;
while ( rs.next() )
  {
    rownum++;
    print("<TR><TD><FONT SIZE='1'>"
          + String.valueOf(rownum)
          + "</FONT></TD>\n");
          
    for(i = 1; i <= cols; i++)
      {
        print("<TD BGCOLOR='#FFFFFF'><FONT SIZE='1'>"
              + rs.getString(i)
              + "</FONT></TD>\n");
      }
     print("</TR>\n");

Alternatively, if the statement is not the sort which has results, the statement is simply executed and no results are displayed.

If there is an error, the appropriate "get me the error message as a string" method is used, and a reddish table containing the error message is printed.

Going Further

In my own projects I have found that the query complexity is generally very simple for most tasks, so the database server seldom has to work very hard at all. More attention must be given to your own middleware code than to ultra-efficient SQL query optimization or database tuning, when developing basic interactive web->db applications.

The key optimization in my experience is database connection pooling. This requires some cleverness on your part because you have to find a way to keep your execution environment running across requests, *and* to prevent your connection object from going out of scope and getting garbage collected when your request handler exits. In other words if you're using CGI, forget it, because the whole Perl process gets created and destroyed for every request. Apache's mod_perl and Java Servlets seem to me to be the best solutions. LiveWire is a lesser substitute because of the less robust language and execution environment but it can be made to work; I highly recommend taking the time to learn Java and use Java Servlets though. In any case the trick is to find a way to store your connection pool on something global that belongs to the environment, rather than a local variable or property of your request handler.

A second optimization is simply reducing the number of statements you execute. This is something you have to do yourself as the architect - try to use joins and normalization sensibly; if you're executing 50 statements in a loop in order to accomplish a known task, something is wrong. (An exception is if the loop is controlled by user input in which case you have less control over your SQL "where" clause.)

Source Code

odbc-sql-tool.pl - the Win32 Perl CGI / Win32::ODBC version
SimpleJDBCTool.java - the Java Servlet / JDBC version

Links

I hope these programs and this tutorial help you develop neat stuff of your own, and I hope they advance your knowledge of database intergration technolgies. I hope you have fun with Perl and Java; they're both great languages to work with.