Hi all,
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
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:
#########################
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 });
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';
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);
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
$result = $sth_up->execute($begin, $end, $id);
}
if (not $result or $result eq '0E0' and $sth_in) {
warn "will INSERT INTO Projects\n"; # DEBUG
my $result = $sth_in->execute($id, $begin, $end);
if (not $result or $result eq '0E0') {
warn "Could not update table 'Projects' project $id\n";
}
}
}
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 48
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 48
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]
At last my stats:
Perl 5.8.8 built for sun4-solaris-thread-multi
DBI 1.48
DBD::CSV 0.22
Can someone help?
Greetings
Robert
