Re: Multiple queries on DBI corresponding to multiple csv files?

[poj]

There is an example in Text::CSV_XS here

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Text::CSV_XS;

# Create multiples csv file corresponding to each sql query
my @query = (
 ['SELECT * FROM account','accounts.csv'],
 ['SELECT * FROM facts','facts.csv'],
 ['SELECT * FROM mytable','mytable.csv'],
);

# DBI CONNECTION
my $dbh = get_dbh();

for (@query){
  run_query(@$_);
}

sub run_query {
  my ($sql,$csvfile) = @_;
  print "Running $sql\n";
  open my $fh, '>', $csvfile 
       or die "Could not open $csvfile: $!";
  
  my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\n" });
  my $sth = $dbh->prepare ($sql); 
  $sth->execute;
  $csv->print($fh, $sth->{NAME_lc});
  my $count = 1;
  while (my $row = $sth->fetchrow_arrayref) {
    $csv->print($fh, $row);
    ++$count;
  }
  close $fh; 
  print "$count lines dumped to $csvfile\n";
}
    
sub get_dbh {
  my $dbname = '';
  my $user = '****'; 
  my $pw   = '';
  my $dsn  = "dbi:Ingress:$dbname"; 
  my $dbh  = DBI->connect($dsn, $user, $pw, 
     { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}

poj

[jtech]

Nice! But, sadly I don't have permission to install Perl modules on the server, I am afraid that cannot use Text::CSV_XS :(

[marto]

Yes, even you can use CPAN, local::lib, cpanm.

[poj]

Ok, then maybe use Text::CSV_PP or are you sure you will never have a table with fields containing commas ?

poj

[jtech]

Right, I will check this out :)