--- sql2xls.cgi	2008/11/03 20:42:55	14
+++ sql2xls.cgi	2008/11/06 10:49:02	23
@@ -70,7 +70,6 @@
 use Encode qw/decode/;
 use Data::Dump qw/dump/;
 
-# edit following to set defaults
 our $dsn      = 'DBI:Pg:dbname=';
 our $database = 'template1';
 our $user     = 'dpavlin';
@@ -85,15 +84,21 @@
 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
 $sql_dir =~ s,/[^/]+$,,;
 
-my $config_path = "$sql_dir/config.pl";
-warn "# using $config_path\n";
-require $config_path if -e $config_path;
+sub require_config {
+	my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
+	warn "# using $config_path\n";
+	require $config_path if -e $config_path;
+}
+
+require_config;
 
-my $reports_path = $ENV{PATH_INFO};
+my $reports_path = $ENV{PATH_INFO} || '';
 $reports_path =~ s/\.\.//g; # some protection against path exploits
 $reports_path ||= shift @ARGV; # for CLI invocation
 $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
 
+require_config;
+
 warn "# reading SQL queries from $sql_dir\n" if $debug;
 
 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
@@ -113,16 +118,34 @@
 
 my $date_format = $workbook->add_format(num_format => $xls_date_format);
 
-my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+our $dbh;
+sub use_database {
+	$dbh->disconnect if $dbh;
+	my $database = shift || return;
+	print STDERR "## connect to $database\n" if $debug;
+	$dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+	if ( $db_encoding ) {
+		if ( $dsn =~ m{Pg} ) {
+			$dbh->do( qq{ set client_encoding = '$db_encoding'; } );
+		} elsif ( $dsn =~ m{mysql} ) {
+			$dbh->do( qq{ set names '$db_encoding'; } );
+		} else {
+			warn "Don't know how to set encoding to $db_encoding for $dsn";
+		}
+	}
+}
+
+use_database( $database );
 
 sub _c {
+	return shift unless $db_encoding;
 	return decode( $db_encoding, shift );
 }
 
 foreach my $sql_file (@sql_files) {
 
 	my $sheet_name = $sql_file;
-	$sheet_name =~ s/\d+_//;
+	$sheet_name =~ s/\d+[_-]//;
 	$sheet_name =~ s/_/ /g;
 	$sheet_name =~ s/\.sql//;
 
@@ -134,24 +157,23 @@
 
 	open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
 	my $comment = '';
-	my $sql = "";
+	my $full_sql = "";
 	while(<SQL>) {
 		chomp;
 		if (/^\\c\s+(\S+)/) {
-			$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;
+			use_database( $1 );
 		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
-			$sql.= ' ' . $_;
+			$full_sql.= ' ' . $_;
 		}
 	}
 	close(SQL);
 
-	$sql =~ s/\s\s+/ /gs;
+	$full_sql =~ s/\s\s+/ /gs;
+	$full_sql .= ';' unless $full_sql =~ m/;\s*/s;
 
-	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
+	print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
 
 	my $row = 0;
 
@@ -168,34 +190,48 @@
 		$row+=2;
 	}
 
-	my $sth = $dbh->prepare($sql);
-	$sth->execute();
-
 	my $fmt_header = $workbook->addformat();    # Add a format
 	$fmt_header->set_italic();
 
-	for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
-		$worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
-	}
-	$row++;
+	foreach my $sql ( split(/;/, $full_sql ) ) {
 
-	my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+		warn "SQL: $sql\n" if $debug;
 
-	while (my @row = $sth->fetchrow_array() ) {
-		for(my $col=0; $col<=$#row; $col++) {
-			my $data = $row[$col];
-			if ( $types[$col] =~ m/^date/i ) {
-				$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
-				$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
-				warn "## $data\n";
-				$worksheet->write_date_time( $row, $col, $data, $date_format );
-			} else {
-				$worksheet->write($row, $col, _c( $data ) );
-			}
+		my $sth = $dbh->prepare($sql);
+		$sth->execute();
+
+		next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
+
+		my @types = eval {
+			map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+		};
+
+		for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
+			$worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
 		}
 		$row++;
-	}
 
+		while (my @row = $sth->fetchrow_array() ) {
+			for(my $col=0; $col<=$#row; $col++) {
+				my $data = $row[$col];
+				next unless defined $data;
+				if ( $types[$col] && $types[$col] =~ m/^date/i ) {
+					$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
+					$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
+					warn "## by type datetime $data\n" if $debug;
+					$worksheet->write_date_time( $row, $col, $data, $date_format );
+				} elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
+					warn "## heuristic date time: $1T$2\n" if $debug;
+					$worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
+				} else {
+					$worksheet->write($row, $col, _c( $data ) );
+				}
+			}
+			$row++;
+		}
+
+		$row++; # separete queries by one row
+	}
 }
 
 $dbh->disconnect;