--- sql2xls.cgi	2008/11/03 22:31:47	20
+++ sql2xls.cgi	2008/11/03 23:19:40	21
@@ -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';
@@ -93,7 +92,7 @@
 
 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";
@@ -141,7 +140,7 @@
 
 	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+)/) {
@@ -151,14 +150,15 @@
 		} 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;
 
@@ -175,37 +175,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:\d\d:\d\d)$/$1T$2/;
-				warn "## by type datetime $data\n";
-				$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";
-				$worksheet->write_date_time( $row, $col, "$1T$2", $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;