--- sql2xls.cgi	2008/11/03 18:31:58	5
+++ sql2xls.cgi	2008/11/03 19:19:39	10
@@ -36,16 +36,16 @@
 use Data::Dump qw/dump/;
 
 # edit following to set defaults
-my $dsn      = 'DBI:Pg:dbname=';
-my $database = 'template1';
-my $user     = 'dpavlin';
-my $passwd   = '';
-my $path     = 'sql_reports.xls';
+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,/[^/]+$,,;
@@ -54,6 +54,10 @@
 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 ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
 	# use as cgi script
@@ -83,15 +87,16 @@
 	# 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->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;
@@ -113,6 +118,9 @@
 		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;
 	}
@@ -128,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++) {