Skip Menu |

This queue is for tickets about the DBD-CSV CPAN distribution.

Report information
The Basics
Id: 61168
Status: resolved
Priority: 0/
Queue: DBD-CSV

People
Owner: Nobody in particular
Requestors: volker [...] superhein.de
Cc: rehsack [...] googlemail.com
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.30
Fixed in: 0.30



Subject: Specifying seperation character per table does not work
Hi there, the following code: -------------------------------------------------------------- my $dbh = DBI->connect("dbi:CSV:") || $mainLog->logdie("Connection failed with error: $DBI::errstr"); $dbh->{'csv_tables'}->{'csv_data'}->{'file'} = "/tmp/test.csv"; $dbh->{'csv_tables'}->{'csv_data'}->{'eol'} = "\n"; $dbh->{'csv_tables'}->{'csv_data'}->{'sep_char'} = ";"; $dbh->{'csv_tables'}->{'csv_data'}->{'quote_char'} = undef; $dbh->{'csv_tables'}->{'csv_data'}->{'escape_char'} = undef; print Dumper $dbh->{'csv_tables'}->{'csv_data'}; my $stmt = qq| select * from csv_data |; my $sth = $dbh->prepare($stmt); $sth->execute(); -------------------------------------------------------------- produces this output: $VAR1 = { 'f_fqbn' => '/tmp/csv_data', 'eol' => ' ', 'quote_char' => undef, 'f_file' => '/tmp/volki.csv', 'csv_class' => 'Text::CSV_XS', 'f_encoding' => undef, 'initialized' => 1, 'escape_char' => undef, 'f_lockfile' => undef, 'csv_skip_first_row' => undef, 'f_fqfn' => '/tmp/volki.csv', 'f_schema' => undef, 'csv_sep_char' => ';', 'csv_in' => bless( { '_BOUND_COLUMNS' => undef, 'verbatim' => 0, 'types' => undef, '_AHEAD' => undef, '_COLUMN_NAMES' => undef, 'eol' => '', 'blank_is_undef' => 0, '_STRING' => undef, 'quote_char' => '"', 'sep_char' => ',', 'allow_whitespace' => 0, 'auto_diag' => 1, '_ERROR_INPUT' => undef, 'escape_char' => '"', '_FFLAGS' => undef, 'always_quote' => 0, 'allow_loose_escapes' => 0, '_STATUS' => undef, 'allow_loose_quotes' => 0, 'keep_meta_info' => 0, 'binary' => 1, 'quote_null' => 1, '_EOF' => 0, 'quote_space' => 1, '_FIELDS' => undef, 'empty_is_undef' => 0 }, 'Text::CSV_XS' ), 'f_lock' => undef, 'csv_eol' => ' ', 'f_dir' => '/home/vhein51/cscan', 'f_ext' => undef, 'sql_identifier_case' => 2, 'table_name' => 'csv_data', 'csv_out' => bless( { '_BOUND_COLUMNS' => undef, 'verbatim' => 0, 'types' => undef, '_AHEAD' => undef, '_COLUMN_NAMES' => undef, 'eol' => ' ', 'blank_is_undef' => 0, '_STRING' => undef, 'quote_char' => '"', 'sep_char' => ',', 'allow_whitespace' => 0, 'auto_diag' => 1, '_ERROR_INPUT' => undef, 'escape_char' => '"', '_FFLAGS' => undef, 'always_quote' => 0, 'allow_loose_escapes' => 0, '_STATUS' => undef, 'allow_loose_quotes' => 0, 'keep_meta_info' => 0, 'binary' => 1, 'quote_null' => 1, '_EOF' => 0, 'quote_space' => 1, '_FIELDS' => undef, 'empty_is_undef' => 0 }, 'Text::CSV_XS' ) }; DBD::CSV::st execute failed: Execution ERROR: Missing first row at /sysmgmt/opt/site_perl/x86_64-linux-thread-multi/DBD/File.pm line 684 . ---------- The csv contained the following lines: "HEADER1";"HEADER2" Volki;Bolki Zolki;Solki Seems like setting the "sep_char" or "csv_sep_char" ( I tried both ) seperately for a table does not work. P.S.: greetings from Mr. Jens Rehsack who witnessed this phenomenon personally ;-)
The "problem" is two-fold. Using the most recent versions of DBI, SQL::Statement, Text::CSV_XS and DBD::CSV, the problem was that the csv_in and csv_out objects for Text::CSV_XS were created before they saw the sep_char attribute change, so it never got to the parser. I've patched the most recent version (get it from git to test). Note that you cannot pass it as "sep_char", but have to add the DBD prefix and use "csv_sep_char". $ cat sandbox/rt61168.pl #!/pro/bin/perl use strict; use warnings; use autodie; use Data::Peek; use DBI; my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { PrintError => 1, RaiseError => 1, f_ext => ".csv/r", sep_char => ";", }) or die "Connection failed with error: $DBI::errstr"; DDumper { "DBI" => $DBI::VERSION, "DBD::CSV" => $DBD::CSV::VERSION, "Text::CSV_XS" => $Text::CSV_XS::VERSION, "SQL::Statement" => $SQL::Statement::VERSION, }; $dbh->{csv_tables}{csv_data}{file} = "/tmp/test.csv"; $dbh->{csv_tables}{csv_data}{csv_sep_char} = ";"; DDumper $dbh->{csv_tables}{csv_data}; my $sth = $dbh->prepare ("select * from csv_data"); $sth->execute; while (my $r = $sth->fetchrow_hashref) { DDumper $r; } $ cat /tmp/test.csv "HEADER1";"HEADER2" Volki;Bolki Zolki;Solki $ perl -Ilib sandbox/rt61168.pl { 'DBD::CSV' => '0.31', DBI => '1.614', 'SQL::Statement' => '1.32', 'Text::CSV_XS' => '0.74' } { csv_class => 'Text::CSV_XS', csv_eol => ' ', csv_in => bless ({ _AHEAD => undef, _BOUND_COLUMNS => undef, _COLUMN_NAMES => undef, _EOF => 0, _ERROR_INPUT => undef, _FFLAGS => undef, _FIELDS => undef, _STATUS => undef, _STRING => undef, allow_loose_escapes => 0, allow_loose_quotes => 0, allow_whitespace => 0, always_quote => 0, auto_diag => 1, binary => 1, blank_is_undef => 0, empty_is_undef => 0, eol => '', escape_char => '"', keep_meta_info => 0, quote_char => '"', quote_null => 1, quote_space => 1, sep_char => ';', types => undef, verbatim => 0 }, 'Text::CSV_XS'), csv_out => bless ({ _AHEAD => undef, _BOUND_COLUMNS => undef, _COLUMN_NAMES => undef, _EOF => 0, _ERROR_INPUT => undef, _FFLAGS => undef, _FIELDS => undef, _STATUS => undef, _STRING => undef, allow_loose_escapes => 0, allow_loose_quotes => 0, allow_whitespace => 0, always_quote => 0, auto_diag => 1, binary => 1, blank_is_undef => 0, empty_is_undef => 0, eol => ' ', escape_char => '"', keep_meta_info => 0, quote_char => '"', quote_null => 1, quote_space => 1, sep_char => ';', types => undef, verbatim => 0 }, 'Text::CSV_XS'), csv_sep_char => ';', csv_skip_first_row => undef, f_dir => '/pro/3gl/CPAN/DBD-CSV', f_encoding => undef, f_ext => '.csv/r', f_file => '/tmp/test.csv', f_fqbn => '/tmp/csv_data', f_fqfn => '/tmp/test.csv', f_lock => undef, f_lockfile => undef, f_schema => undef, initialized => 1, sql_identifier_case => 2, table_name => 'csv_data' } { header1 => 'Volki', header2 => 'Bolki' } { header1 => 'Zolki', header2 => 'Solki' } $ Jens, note the wrong setting for f_fqbn
CC: volker [...] superhein.de
Subject: Re: [rt.cpan.org #61168] Specifying seperation character per table does not work
Date: Tue, 14 Sep 2010 15:48:37 +0200
To: bug-DBD-CSV [...] rt.cpan.org
From: Jens Rehsack <rehsack [...] googlemail.com>
2010/9/14 H.Merijn Brand via RT <bug-DBD-CSV@rt.cpan.org>: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=61168 > > > The "problem" is two-fold. Using the most recent versions of DBI, > SQL::Statement, Text::CSV_XS and DBD::CSV, the problem was that the > csv_in and csv_out objects for Text::CSV_XS were created before they > saw the sep_char attribute change, so it never got to the parser.
That's what I tried to explain at the begin of July - but I know, Belgium Perl Workshop and holiday preparation... Show quoted text
> I've patched the most recent version (get it from git to test).
The last paragraph of http://search.cpan.org/~timb/DBI-1.613_94/lib/DBD/File/HowTo.pod#User_comfort should you guide how it's intended. Show quoted text
> Note that you cannot pass it as "sep_char", but have to add the DBD > prefix and use "csv_sep_char".
The documentation should be updated in this case. Show quoted text
> $ cat sandbox/rt61168.pl
[...] Show quoted text
> Jens, note the wrong setting for f_fqbn
I'd say this is intended by the file2table implementation, but I can't figure out why. We should probably take a close look to this function together :( Stop timbo releasing 1.614 before we figured out this. Probably we should add a nice test for it to t/51dbm_file.t ... /Jens
This has been fixed in the related modules. I cannot add a "fixed-in" version here that actually tells where it was fixed, so I set it to the same.