Here it is, it is adapted for ODBC altougth working with other type of
connection would be easy to adapt, hope it helps, please let me now if
you're using it or anyone else on this list.
roberto
------BEGIN---------------
#!/usr/bin/perl -w
# -----------------------------
# websql.pl
# Mar 1999
# [EMAIL PROTECTED] & luis aura
# -----------------------------
#
use strict;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use lib 'lib';
use DBI;
# constante para identificar el nombre y ubicacion del script
my $CGI_BIN = 'websql.pl';
# cgi stuff
my $q = new CGI;
# obtener parametros
my($x, $usr, $pwd, $dsn, $sql);
$x = $q->param('x');
$usr = $q->param('usr');
$pwd = $q->param('pwd');
$dsn = $q->param('dsn');
$sql = $q->param('sql');
# valores por default
$x = '' unless $x;
$usr = 'typeuser' unless defined($usr); #your user
$pwd = 'typepassword' unless defined($pwd); #you pwd
$dsn = 'dbi:ODBC:dsname' unless $dsn; #your dsname
# print begin of document
&print_begin;
if ($x eq '') {
# nothing to do
&print_end;
exit;
}
# connect to database
my $dbh = DBI->connect($dsn, $usr, $pwd, {LongReadLen => 32 * 1024} )
or error_handler("Failed to connect to $dsn");
# handle statement
my $sth;
# batch
my @sql = split(/;(\n|\r\n)+/, $sql);
if (scalar(@sql) > 1) {
my $i = 0;
my $tsql;
foreach $tsql (@sql) {
# elimina espacios iniciales y finales
$tsql =~ s/^\s+|\s+$//;
if ($tsql) {
++$i;
$sth = $dbh->prepare($tsql) || error_handler("Unable to
prepare $tsql");
if ($sth) {
if ($sth->execute) {
&print_results;
} else {
error_handler("Unable to execute $tsql");
}
}
}
}
} else {
# prepare statement
$sth = $dbh->prepare($sql) or error_handler("Unable to prepare $sql");
# execute statement
$sth->execute or error_handler("Unable to execute $sql");
}
# print results
&print_results;
# disconnect from database
$dbh->disconnect;
# print end of document
&print_end;
exit;
sub error_handler {
my $msg = shift;
print<<EOH;
<tr><td bgcolor="#990033"><font face="verdana, arial, helvetica, sans-serif"
size="1" color="#ffffff"><b>E R R O R S</b><br></font></td></tr>
<tr><td bgcolor="#eeeecc">
<font face="verdana, arial, helvetica, sans-serif"
size="2"><b>$msg</b><br>$DBI::errstr<br></font>
</td></tr>
EOH
&print_end;
exit 1;
}
sub print_begin {
print $q->header;
print<<EOH;
<!doctype html public "-//W3C//DTD HTML 3.2 Final//EN">
<html>
<head>
<title>WebSQL</title>
<meta http-equiv="Keywords" content="web, sql, dbi, dbd, odbc">
<meta http-equiv="Description" content="WebSQL Client 1.0 written by Luis
Aura">
</head>
<body bgcolor="#ffffff" text="#000000" link="#006699" vlink="#006699"
alink="#0099cc">
<form action="$CGI_BIN" method="post">
<input type="hidden" name="x" value="execsql">
<table width="600" cellspacing="0" cellpadding="5" border="0">
<tr><td bgcolor="#006699"><font face="verdana, arial, helvetica, sans-serif"
size="1" color="#ffffff">CONNECTION<br></font></td></tr>
<tr><td bgcolor="#eeeeee">
<font face="verdana, arial, helvetica, sans-serif" size="2">
User: <input type="text" name="usr" value="$usr" size="10"
maxlength="30"> Password: <input type="password" name="pwd"
value="$pwd" size="10" maxlength="30"> DSN: <input type="text"
name="dsn" value="$dsn" size="20" maxlength="128">
</font>
</td></tr>
<tr><td bgcolor="#006699"><font face="verdana, arial, helvetica, sans-serif"
size="1" color="#ffffff">SQL STATEMENT TO EXECUTE<br></font></td></tr>
<tr><td bgcolor="#eeeeee"><textarea name="sql" cols="70"
rows="15">$sql</textarea><br><input type="submit" name="execute"
value="Execute"> <input type="reset"
value="Reset"></td></tr>
EOH
}
sub print_end {
print qq{</table></form></body></html>};
}
sub print_results {
print qq{<tr><td bgcolor="#006699"><font face="verdana, arial, helvetica,
sans-serif" size="1" color="#ffffff">R E S U L T
S<br></font></td></tr><tr><td bgcolor="#eeeecc"><table cellspacing="1"
cellpadding="3" border="0"><tr bgcolor="#0099cc">};
for (my $i = 0; $i < $sth->{NUM_OF_FIELDS}; $i++) {
print qq{<td><font face="verdana, arial, helvetica, sans-serif"
size="1"
color="#ffffff">$sth->{NAME}->[$i]</font></td>};
}
print qq{</tr>};
while (my @row = $sth->fetchrow_array) {
print qq{<tr bgcolor="#ffffff">};
foreach (@row) {
$_ = '<code>(null)</code>' unless defined($_);
print qq{<td><font face="verdana, arial, helvetica, sans-serif"
size="1">$_</font></td>};
}
print qq{</tr>};
}
# get rows
my $rows = $sth->rows;
my $rows_affected = ($rows > 0 ? "<b>$rows</b> row(s) affected" : "(no rows
affected)");
print qq{</tr></table><font face="verdana, arial, helvetica, sans-serif"
size="2"><br>$rows_affected<br></font></td></tr>};
}
------------END-----------
Dear roberto,
I am interested in it. I'm getting pretty tired typing these SQL statements
over and over. Can you send me what you have ?
Thanks,
Arjan
----- Original Message -----
From: "roberto lopez" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 19, 2001 10:37 AM
Subject: RE: Web interface?
> I got one if you're interested it is called websql.pl, we developed it our
> selves
>
> -----Original Message-----
> From: Bernhard Schmalhofer [mailto:[EMAIL PROTECTED]]
> Sent: Mi�rcoles, 19 de Septiembre de 2001 12:14
> To: [EMAIL PROTECTED]
> Subject: Re: Web interface?
>
>
> Jeff Boes wrote:
> >
> > On Sun, 9 Sep 2001 11:50:18 +0600
> > "Alex Gerasev" <[EMAIL PROTECTED]> wrote:
> >
> > > Let me briefly explain what I need: I'm working on application that
uses
> > > perl DBI internally and provides web user interface. I need to be
able
> > to
> > > provide nice database table access from the web, i.e. view tables (or
> > even
> > > sets of data generated by database as a response to ANY sql statement)
> >
> Hi,
>
> actually I rolling my own here, which usually isn't a good idea.
> However one Framework that looked good to me was CogniX at www.thinx.de.
>
> CU, Bernhard
>
> --
> *************************************************
> Bernhard Schmalhofer
> Software Engineer
> Biomax Informatics AG
> Lochhamer Str. 11
> 82152 Martinsried, Germany
> Tel: +49 89 89 55 74 - 39
> Fax: +49 89 89 55 74 - 25
> mailto:[EMAIL PROTECTED]
> http://www.biomax.de
> *************************************************
>
>
