#!/usr/bin/perl -w
#!c:/perl/bin/perl.exe -w
use strict;

# sql2csv.pl - Bare bones SQL Table to CSV export
# David H. Bennett - 11/6/2000 - dbennett@bensoft.com
# Version 1.0

# A couple of notes about the output format:
#
# - Column data is only surrounded by quotes if necessary (if it has
#   commas)
# - Embedded double quotes in values (") are converted to double
#   single quotes ('')
# - Trailing white space is stripped from all values (but not leading
#   white space)
# - Embedded newlines or returns are converted into spaces.
#
# This should insure the utmost compatibility with existing CSV
# applications.
# ------------------------------------------------------------------------
# Tested under Linux and Windows NT

# Ignore unitialized values in query results
#
local $SIG{__WARN__} = sub {
    if ($_[0] =~ m/^Use of uninitialized value/) { return };
    print STDERR $_[0],"\n";
};

use DBI;

# Check parameter count
#
if ($#ARGV != 3) {
  print STDERR "Usage: $0 {dsn} {user} {password} {table}\n\n";
  print STDERR "Outputs table as Comma Seperated Value file to\n";
  print STDERR "standard output.\n";
  exit;
}

# Usage: dbi2csv.pl {dsn} {user} {password} {table}
#
my($dsn)=$ARGV[0];
my($user)=$ARGV[1];
my($password)=$ARGV[2];
my($table)=$ARGV[3];

# Connect to the database
#
my($dbh) = DBI->connect(
    $dsn,
    $user,
    $password,
    { RaiseError => 1, AutoCommit => 1 }
);
$dbh->{LongReadLen} = 8192;

# Select all the fields from the table
#
my($cmd)="select * from $table";
my($sth) = $dbh->prepare($cmd);
$sth->execute;

# Output the names of the columns
#
my($nof)=$sth->{NUM_OF_FIELDS};
my($i)=0;
my(@row);
for ($i=0; $i < $nof; $i++) {
    my($cname)=$sth->{NAME}->[$i];
    # Trim trailing space
    $cname =~ s/\s+$//g;
    # Convert embedded new lines into spaces
    $cname =~ s/[\n\r]/ /g;
    # Convert embedded double quotes to double single quotes
    $cname =~ s/"/''/g;
    # Quote for commas
    if ($cname =~ m/,/) {
        $cname = join('"','',$cname,'');
    }
    push @row,$cname;
}
print join(',',@row),"\n";

# Output the rows
#
my($datarow);
while ($datarow = $sth->fetch) {
  my(@drow);
  for ($i=0; $i < $nof; $i++) {
    my($data)=@$datarow[$i];
    # Trim trailing space
    $data =~ s/\s+$//g;
    # Convert embedded new lines into spaces
    $data =~ s/[\n\r]/ /g;
    # Convert embedded double quotes to double single quotes
    $data =~ s/"/''/g;
    # Quote for commas
    if ($data && ($data =~ m/,/)) {
        $data = join('"','',$data,'');
    }
    # push data on to array
    push @drow,$data;
  }
  print join(',',@drow),"\n";
}

# Close statement handle and disconnect
#
$sth->finish();
$dbh->disconnect();