--- sql2xls.cgi	2008/03/24 23:02:15	4
+++ sql2xls.cgi	2008/11/03 18:31:58	5
@@ -1,11 +1,33 @@
-#!/usr/bin/perl -w
-#
-# convert sql queries on file system to excel file (one query per
-# sheet)
-#
-# Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10
-
+#!/usr/bin/perl
+use warnings;
 use strict;
+
+=head1 NAME
+
+sql2xls.pl - convert sql queries on file system to Excel file
+
+=head1 USAGE
+
+Each file in current directory which ends in C<< *.sql >> will
+be converted to Excel sheet. If you want to have specific order, you can
+prefix filenames with numbers which will be striped when creating sheet
+names.
+
+Comments in sql files (lines beginning with --) will be placed
+in first line in bold.
+
+To specify database on which SQL query is executed
+C<< \c database >> syntax is supported.
+
+You can also run script from command line, and it will produce
+C<< sql_reports.xls >> file.
+
+=head1 AUTHOR
+
+Dobrica Pavlinusic, dpavlin@rot13.org
+
+=cut
+
 use Spreadsheet::WriteExcel;
 use DBI;
 use CGI::Carp qw(fatalsToBrowser);
@@ -13,12 +35,14 @@
 use Encode qw/decode/;
 use Data::Dump qw/dump/;
 
-# Connect to DB
-my $connect = "DBI:Pg:dbname=new";
-my $user = "web";
-my $passwd = "";
+# edit following to set defaults
+my $dsn      = 'DBI:Pg:dbname=';
+my $database = 'template1';
+my $user     = 'dpavlin';
+my $passwd   = '';
+my $path     = 'sql_reports.xls';
 
-my $db_encoding = 'iso-8859-2';
+my $db_encoding     = 'iso-8859-2';
 my $xls_date_format = 'dd.mm.yyyy';
 
 my $debug = 1;
@@ -31,18 +55,19 @@
 closedir DIR;
 
 my $workbook;
-if ($0 =~ m/\.cgi$/i) {
+if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
 	# use as cgi script
 	print "Content-type: application/vnd.ms-excel\n\n";
 	$workbook = Spreadsheet::WriteExcel->new("-");
 } else {
 	# Create a new Excel workbook
-	$workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
+	$workbook = Spreadsheet::WriteExcel->new( $path );
+	warn "Creating XLS file $path\n";
 }
 
 my $date_format = $workbook->add_format(num_format => $xls_date_format);
 
-my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
+my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 
 sub _c {
 	return decode( $db_encoding, shift );
@@ -67,7 +92,7 @@
 		chomp;
 		if (/^\\c\s+(\S+)/) {
 			warn "## connect to $1\n" if $debug;
-			$dbh = DBI->connect('DBI:Pg:dbname=' . $1,$user,$passwd) || die $DBI::errstr;
+			$dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
@@ -92,8 +117,8 @@
 		$row+=2;
 	}
 
-	my $sth = $dbh->prepare($sql) || die $dbh->errstr();
-	$sth->execute() || die $sth->errstr();
+	my $sth = $dbh->prepare($sql);
+	$sth->execute();
 
 	my $fmt_header = $workbook->addformat();    # Add a format
 	$fmt_header->set_italic();
@@ -128,24 +153,3 @@
 
 __END__
 
-=head1 NAME
-
-sql2xls.pl - convert sql queries on file system to excel file
-
-=head1 USAGE
-
-Edit top of script and edit @sql_files array which describes
-files which are going to be loaded and executed producing
-Excel sheets (one sheet per file)
-
-Comments in sql files (lines beginning with --) will be placed
-in outout sheet on top in bold.
-
-Sheet will have name same as sql file.
-
-Run script and examine b<sql_result.xls> file.
-
-=head1 AUTHOR
-
-Dobrica Pavlinusic, dpavlin@rot13.org
-