Re: DBD::CSV - UPDATE corrupts data!
- From: alexander@xxxxxxxx (Alexander Foken)
- Date: Tue, 30 Jun 2009 18:08:13 +0200
Hello,
On 30.06.2009 14:41, Robert Roggenbuck wrote:
Hi all,Running the code below copied and pasted on Linux 2.6.26.5, Perl 5.8.8, DBI 1.607, DBD::CSV 0.20, both runs deliver the same result from your first run. Even several further runs don't change the result.
I stumbled over somthing very strange: When I try to update data in a table, the input parameters are going into the right fields - exept the first data row in the table / file. Below is a script which demonstrate the thing. Running the first time, it creates the table 'Projects' and fills it with data - nothing to complain at this stage. Running it the second time, it tries to update the data (and if a project is missing it will insert a new data set). While updating, it enters the project ID into the field for the begin date and the begin date into the field for the end date and where end date goes to I do not know.
Table project after the first run:
project_id,begin,end
ID001,20040101,20080630
ID003,20050701,20100430
ID002,20050301,20091231
Table Project after the second run:
project_id,begin,end
ID001,20040101,20080630
ID003,ID003,20050701
ID002,ID002,20050301
It has some parts that look very strange to me.
While moving the line of ID001 to the end (as an example) will show the equivalent result: ID003 will be updated correct, ID002 keeps wrong and ID001 gets wrong.
Here is the script:
Enabling RaiseError and PrintError is redundant, RaiseError should be sufficient.
#########################
use strict;
use warnings;
use DBI;
my %projects = (
'ID001' => {
'begin' => '20040101',
'end' => '20080630',
},
'ID002' => {
'begin' => '20050301',
'end' => '20091231',
},
'ID003' => {
'begin' => '20050701',
'end' => '20100430',
},
);
my $dbh = DBI->connect("dbi:CSV:f_dir=/tmp;csv_eol=\n",'','',
{ AutoCommit => 1, PrintError => 1, RaiseError => 1 });
You store a date in a CHAR? OK, with CSV, this makes no difference, but still it is strange.
my $sql = "CREATE TABLE Projects (
project_id VARCHAR(32) PRIMARY KEY,
begin CHAR(8) NOT NULL,
end CHAR(8) NOT NULL
)";
$dbh->do($sql) unless -e '/tmp/Projects';Two parallel prepares. DBD::CSV seems to be ok with that, MS SQL via ODBC does not like that.
warn "will fill/actualise table 'Projects'\n";
DBI->trace(2); # DEBUG
my $sql_up = "UPDATE Projects SET begin=?, end=? WHERE project_id LIKE ?";
my $sth_up = $dbh->prepare($sql_up);
my $sql_in = "INSERT INTO Projects (project_id, begin, end) VALUES (?, ?, ?)";
my $sth_in = $dbh->prepare($sql_in);
$sth_up is always true (prepare dies on error due to RaiseError =>1), why do you test it here?
foreach my $id (keys %projects) {
my $begin = $projects{$id}->{begin};
my $end = $projects{$id}->{end};
my $result;
if ($sth_up) {
warn "will try UPDATE Projects\n"; # DEBUG$sth_in is always true for the same reason. Why do you test it here?
$result = $sth_up->execute($begin, $end, $id);
}
if (not $result or $result eq '0E0' and $sth_in) {
$result will always be true, again due to RaiseError => 1.
$result may be -1 if DBI does not know the number of rows affected.
"0E0" is a special representation of 0, like "0 but true".
So, the real condition would better be written as ($result==0).
warn "will INSERT INTO Projects\n"; # DEBUGAgain, $result is always true, for the same reasons. Again, you better wrote ($result==0).
my $result = $sth_in->execute($id, $begin, $end);
if (not $result or $result eq '0E0') {
warn "Could not update table 'Projects' project $id\n";
}
}
You never call finish for your statement handles. This short script has AutoCommit enabled and the script terminates very fast, so the DESTROY methods of the statement handles should call finish. I would not bet on that behaviour.
}I don't get that finish line with your code.
warn "finished\n";
#########################
As You see in the script I turned tracing on to see what happens with the parameters. But I can not see anything wrong (my scripts name is debugSetup.pl):
[snip]
will try UPDATE Projects
-> execute in DBD::File::st for DBD::CSV::st (DBI::st=HASH(0x4cee84)~0xd27e8 '20040101' '20080630' 'ID001') thr#22ea0
1 -> finish in DBD::File::st for DBD::CSV::st (DBI::st=HASH(0xd27e8)~INNER) thr#22ea0
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 48Neither this one.
will try UPDATE Projects
-> execute for DBD::CSV::st (DBI::st=HASH(0x4cee84)~0xd27e8 '20050701' '20100430' 'ID003') thr#22ea0
1 -> finish for DBD::CSV::st (DBI::st=HASH(0xd27e8)~INNER) thr#22ea0
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 48Not this one.
will try UPDATE Projects
-> execute for DBD::CSV::st (DBI::st=HASH(0x4cee84)~0xd27e8 '20050301' '20091231' 'ID002') thr#22ea0
1 -> finish for DBD::CSV::st (DBI::st=HASH(0xd27e8)~INNER) thr#22ea0
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 48
[snip]
It seems the code you showed us is not the code you are running.
This is old, released 2006-Feb-01.
At last my stats:
Perl 5.8.8 built for sun4-solaris-thread-multi
DBI 1.48This is ancient, released 2005-Mar-14.
DBD::CSV 0.22This is the current version.
Double-check that you showed us the right code. Try updating at least DBI, better Perl and DBI.
Can someone help?
Regards,
Alexander
--
Alexander Foken
mailto:alexander@xxxxxxxx http://www.foken.de/alexander/
.
- References:
- DBD::CSV - UPDATE corrupts data!
- From: Robert Roggenbuck
- DBD::CSV - UPDATE corrupts data!
- Prev by Date: Re: DBD::ODBC make test fails - unixODBC/Easysoft/SQL Server 2008
- Next by Date: Re: DBD::ODBC make test fails - unixODBC/Easysoft/SQL Server 2008
- Previous by thread: DBD::CSV - UPDATE corrupts data!
- Next by thread: Out of memory! error
- Index(es):
Relevant Pages
|
