support multiple SQL queries in one file, for example

to use create temporary time to speedup reporting

git-svn-id: svn://svn.rot13.org/SQL2XLS@21 2e857b76-582b-47e5-ad5c-b3ba0f0ee29b

diff --git a/sql2xls.cgi b/sql2xls.cgi
index ef6fb2c..939a500 100755 (executable)
--- a/sql2xls.cgi
+++ b/sql2xls.cgi
@@ -70,7 +70,6 @@ use CGI::Carp qw(fatalsToBrowser);
 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 @@ sub require_config {
 
 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 @@ foreach my $sql_file (@sql_files) {
 
        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 @@ foreach my $sql_file (@sql_files) {
                } 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 @@ foreach my $sql_file (@sql_files) {
                $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++;
-
-       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++) {
-                       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 ) );
-                       }
+       foreach my $sql ( split(/;/, $full_sql ) ) {
+
+               warn "SQL: $sql\n" if $debug;
+
+               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;