escape data which looks like formula

[SQL2XLS] / sql2xls.cgi

diff --git a/sql2xls.cgi b/sql2xls.cgi
index 00e484f..041e198 100755 (executable)
--- a/sql2xls.cgi
+++ b/sql2xls.cgi
@@ -99,7 +99,7 @@ $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
 
 require_config;
 
-warn "# reading SQL queries from $sql_dir\n" if $debug;
+warn "SQL queries from $sql_dir\n";
 
 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
@@ -124,7 +124,15 @@ sub use_database {
        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;
-       $dbh->do( qq{ set names '$db_encoding'; } ) if $db_encoding && $dsn =~ m{mysql};
+       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 );
@@ -187,12 +195,12 @@ foreach my $sql_file (@sql_files) {
 
        foreach my $sql ( split(/;/, $full_sql ) ) {
 
-               warn "SQL: $sql\n" if $debug;
+               warn "SQL: $sql\n";
 
                my $sth = $dbh->prepare($sql);
                $sth->execute();
 
-               next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
+               next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL
 
                my @types = eval {
                        map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
@@ -216,6 +224,7 @@ foreach my $sql_file (@sql_files) {
                                        warn "## heuristic date time: $1T$2\n" if $debug;
                                        $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
                                } else {
+                                       $data = "'" . $data if $data =~ m/^=/; # escape data which looks like formula
                                        $worksheet->write($row, $col, _c( $data ) );
                                }
                        }