--- sql2xls.cgi	2008/11/03 23:19:40	21
+++ sql2xls.cgi	2012/12/27 12:57:01	28
@@ -99,7 +99,7 @@
 
 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);
@@ -118,7 +118,24 @@
 
 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;
@@ -144,9 +161,7 @@
 	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 {
@@ -180,12 +195,12 @@
 
 	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} };
@@ -209,6 +224,7 @@
 					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 ) );
 				}
 			}