--- sql2xls.cgi	2008/03/24 23:02:15	4
+++ sql2xls.cgi	2008/11/03 19:19:39	10
@@ -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,15 +35,17 @@
 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
+our $dsn      = 'DBI:Pg:dbname=';
+our $database = 'template1';
+our $user     = 'dpavlin';
+our $passwd   = '';
+our $path     = 'sql_reports.xls';
 
-my $db_encoding = 'iso-8859-2';
-my $xls_date_format = 'dd.mm.yyyy';
+our $db_encoding     = 'iso-8859-2';
+our $xls_date_format = 'dd.mm.yyyy';
 
-my $debug = 1;
+our $debug = 1;
 
 my $sql_dir = path_translated || '.';
 $sql_dir =~ s,/[^/]+$,,;
@@ -30,19 +54,24 @@
 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
 closedir DIR;
 
+my $config_path = "$sql_dir/config.pl";
+warn "# using $config_path\n";
+require $config_path if -e $config_path;
+
 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 );
@@ -58,16 +87,17 @@
 	# Add a worksheet
 	my $worksheet = $workbook->addworksheet($sheet_name);
 
-	print STDERR "working on $sql_file...\n" if ($debug);
+	print STDERR "working on $sql_file\n" if ($debug);
 
 	open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
-	my $comment;
+	my $comment = '';
 	my $sql = "";
 	while(<SQL>) {
 		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->disconnect if $dbh;
+			print STDERR "## connect to $1\n" if $debug;
+			$dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
@@ -88,12 +118,15 @@
 		my $fmt_comment = $workbook->addformat();    # Add a format
 		$fmt_comment->set_bold();
 
+		$comment =~ s/^\s+//;
+		$comment =~ s/\s+$//;
+
 		$worksheet->write($row, 0, _c($comment), $fmt_comment);
 		$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();
@@ -103,7 +136,7 @@
 	}
 	$row++;
 
-	my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
+	my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
 
 	while (my @row = $sth->fetchrow_array() ) {
 		for(my $col=0; $col<=$#row; $col++) {
@@ -128,24 +161,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
-