benn has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed Brethren,
We've come across a strange problem with DBD::CSV when inserting / updating empty fields, demonstrated by this test case. 
use DBI;use strict;use warnings;
my $dbh = DBI->connect("DBI:CSV:f_dir=./;");
$dbh->do("create table mytable (field1 text,field2 text)");
$dbh->do("insert into mytable values ('','there')");
print "field1=",$dbh->selectrow_array("select field1 from mytable");

[download]
which prints field1='?0?there' only on our Redhat 7.3.2 (Perl 5.8,DBD::CSV 0.2002) box - not on the Windows machines (also Perl 5.8, DBD::CSV 2.002) or a Debian Perl 5.6 (DBD::CSV 1.030) machine.

I therefore assumed it was something to do with Text::CSV_XS, but that seems to be fine as far as I can make out - this is OK... 

use Text::CSV_XS;
use IO::Wrap;
my $csv = Text::CSV_XS->new(); 
my $fh = wraphandle(STDOUT);
$csv->print($fh, ['','test']);

[download]
...and it looks like could be something to do with placeholders - values('','something','','something'...) gives '?0?something','?1?something' etc.

Stangely also, even if csv_quote_char is set to \", the values are always single-quoted.

Has anybody else come across this? I'm not seriously worried, as we're only playing with CSV for initial prototyping fun, but it seemed a curious bug. I don't want to go mailing authors unless it's reproducible elsewhere, and not just us being dumb :)

Cheers,Ben.

Replies are listed 'Best First'.
Re: DBD::CSV empty fields problem on RedHat / Perl 5.8.
by Itatsumaki (Friar) on Jun 09, 2003 at 17:56 UTC

    I can replicate this error on windows (Win XP 5.1; Perl 5.6.1). However, the syntax below (using place-holders) works correctly for me. I often find that inserting a NULL into the leading field of a database table causes problems when not using place-holders. Does this work for you?

    use DBI;
use strict;
my $dbh = DBI->connect("DBI:CSV:f_dir=./;");

my $param1 = undef;
my $param2 = 'test';

my $sql_create = '
create table mytable(
      field1 text,
      field2 text
      )';

my $sql_insert = '
INSERT INTO mytable(
      field1,
      field2
      )
VALUES (?,?)';

my $sth_insert = $dbh->prepare($sql_insert);
$dbh->do($sql_create);
$sth_insert->execute($param1, $param2);
print "field1=",$dbh->selectrow_array("select field1 from mytable");

    [download]
    Hope this helps,
    -Tats

    Update:
    DBD::DSV 0.2002
    DBI 1.30

[reply]
[d/l]
      Indeed - that works fine. We're using placeholders everywhere else, and only found this due to an 'optimisation' of the "update mytable set ".join(",",map {"$_=".$dbh->quote($cgi->param($_))} @fields) variety. Interesting that it's Windows/5.6 - as I said, Debian/5.6 was fine, as was Windows/5.8. Good to have it confirmed though :)

      Thanks, Ben.

[reply]
[d/l]

Back to Seekers of Perl Wisdom