# ----------------------------------------------------------------------
# odbc-sql-tool.pl
# Developed with NT Emacs 19.37 and Win32 Perl build 303 on WinNT 4.0sp3
#
# Written by Jamie Flournoy <jamie@westlake.com>
# Copyright (C) 1997 by WestLake Solutions <http://www.westlake.com>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License at <http://www.fsf.org/copyleft/gpl.html>
# for more details.
# 
# The standard version of this program lives at 
#  <http://demo.westlake.com/sql-tool/odbc-sql-tool.pl>
#
# ----------------------------------------------------------------------
#
# Note: this is a Win32 Perl script, hence the lack of a shebang
# (#!blah) line at the top which Unix perl scripts need
#
# Get Win32 Perl from <http://www.activestate.com/>
#
# This is a simple tool that lets you execute arbitrary SQL statements
# provided you have an ODBC data source already defined in the ODBC control
# panel.
#
# It's a bit more than a hello world; if you've used the simple command
# line ISQL/DBAccess/SQL*Plus tools with major databases, or the GUI tools
# with their simple raw SQL tools, this gives similar functionality.
# However, this tool lets you open multiple browser windows to preserve the
# output of several queries, and query results are displayed in HTML table
# format for easier reading.
#
# This file is the whole tool, but you still DO need a separate ODBC driver
# for your database so that this thing will have a way to connect to it.
# You also need to make sure that the ODBC driver works, possibly by
# troubleshooting it with this tool.
#
# ----------------------------------------------------------------------

use CGI;
use Win32::ODBC;

$q = new CGI;


print $q->header;
print $q->start_html(-title => 'SQL Tool',
		     -BGCOLOR => '#DFEFFF')."\n";
print $q->startform('POST', '/cgi-bin/sql-tool.pl')."\n";
print "<H1 ALIGN='CENTER'>SQL Tool</H1>\n";
print '<DIV ALIGN="CENTER">' . $q->submit(-name => 'Execute')
    . $q->reset . "</DIV>\n";

print "<H3>SQL Statement:</H3>\n<BLOCKQUOTE>\n";
print $q->textarea(-name => 'statement',
		   -rows => 5,
		   -columns => 70,
		   -wrap => 'soft');
print "</BLOCKQUOTE>\n<H3>Connection Info:</H3>\n<BLOCKQUOTE>\n";

print "<TABLE WIDTH=450 BORDER=0>\n";
print " <TR><TD ALIGN='RIGHT' WIDTH='30%'>Data Source Name</TD>\n";

%dsn_list = Win32::ODBC::DataSources;
@dsn_names = keys (%dsn_list);
print ' <TD>'. $q->popup_menu(-name => 'dsn',
			      -values => \@dsn_names,
			      -default => $q->param('dsn')
			      ) . "</TD></TR>\n";

print " <TR><TD ALIGN='RIGHT' WIDTH='30%'>User-ID</TD>\n";
print ' <TD>'. $q->textfield(-name => 'userid', -size => 40) . "</TD></TR>\n";
print " <TR><TD ALIGN='RIGHT' WIDTH='30%'>Password</TD>\n";
print ' <TD>'. $q->password_field(-name => 'password', -size => 40)
    . "</TD></TR>\n";
print "</TABLE></BLOCKQUOTE>\n";

print $q->endform . "\n";




if ($q->param('Execute'))
{
    print "<H3>Query Response:</H3>\n<BLOCKQUOTE>\n";
    print "<TABLE BORDER=1 WIDTH='80%'>\n";

    if ($q->param('userid'))
    {
	$dsn_string='dsn=' . $q->param('dsn') . ';UID=' . $q->param('userid')
	    . ';PWD=' . $q->param('password');
    }
    else
    {
	$dsn_string=$q->param('dsn');
    }
    $db = new Win32::ODBC($dsn_string);

    if ($db)
    {
	if (! $db->Sql($q->param('statement')))
	{
	    while ($db->FetchRow())
	    {
		# "my" makes a new array during each iteration
		# otherwise all references point to one array
		# (which contains the data from the last row
		# of the result set)
		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";
	    }
	    
	}
	else
	{
	    print "<TR><TH>Statement Resulted in an Error</TH></TR>\n";
	    print "<TR><TD BGCOLOR='#FF9F9F'><PRE>";
	    $db->DumpError();
	    print "<PRE></TD><TR>\n";
	}

    }
    else
    {
	print "<TR><TH>Connection Failed</TH></TR>\n";
	print "<TR><TD BGCOLOR='#FF5F5F'>\n";
        Win32::ODBC::DumpError();
	print "</TD></TR>\n";
    }

    print "</TABLE>\n</BLOCKQUOTE>\n";
}


print "\n".$q->end_html;

exit (0);