Importing and Exporting Data - MySQL - page 391

The database name comes from the command line. Connection parameters can come
from the command line or an option file. ( Recipe 2.8 covers these option-processing
techniques.)
After establishing a connection to MySQL, the script is ready to execute the query and
produce output. This is where the Text::CSV_XS module comes into play. First, create
a CSV object by calling new() , which takes an optional hash of options that control how
the object handles data lines. The script prepares and executes the query, prints a row
of column labels (if the --labels option was specified), and writes the rows of the result
set:
my $csv = Text:: CSV_XS -> new ({
sep_char => $delim ,
quote_char => $quote ,
escape_char => $quote ,
eol => $eol ,
binary => 1
});
# If table name was given, use it to create query that selects entire table.
# Split on dots in case it's a qualified name, to quote parts separately.
$stmt = "SELECT * FROM " . $dbh -> quote_identifier ( split ( /\./ , $tbl_name ))
if defined ( $tbl_name );
warn "$stmt\n" ;
my $sth = $dbh -> prepare ( $stmt );
$sth -> execute ();
if ( $labels ) # write row of column labels
{
$csv -> combine ( @ { $sth -> { NAME }}) or die "cannot process column labels\n" ;
print $csv -> string ();
}
my $count = 0 ;
while ( my @val = $sth -> fetchrow_array ())
{
++ $count ;
$csv -> combine ( @val ) or die "cannot process column values, row $count\n" ;
print $csv -> string ();
}
The sep_char and quote_char options in the new() call set the column delimiter and
quoting character. The escape_char option is set to the same value as quote_char so
that instances of the quote character occurring within data values are doubled in the
output. The eol option indicates the line-termination sequence. Normally,
Text::CSV_XS leaves it to you to print the terminator for output lines. By passing a non-
undef eol value to new() , the module adds that value to every output line automatically.
The binary option is useful for processing data values that contain binary characters.