Hello,
On 30.06.2009 14:41, Robert Roggenbuck wrote:
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
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.
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:
It has some parts that look very strange to me.
#########################
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 });
Enabling RaiseError and PrintError is redundant, RaiseError should be
sufficient.
my $sql = "CREATE TABLE Projects (
project_id VARCHAR(32) PRIMARY KEY,
begin CHAR(8) NOT NULL,
end CHAR(8) NOT NULL
)";
You store a date in a CHAR? OK, with CSV, this makes no difference, but
still it is strange.
$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);
Two parallel prepares. DBD::CSV seems to be ok with that, MS SQL via
ODBC does not like that.
foreach my $id (keys %projects) {
my $begin = $projects{$id}->{begin};
my $end = $projects{$id}->{end};
my $result;
if ($sth_up) {
$sth_up is always true (prepare dies on error due to RaiseError =>1),
why do you test it here?
warn "will try UPDATE Projects\n"; # DEBUG
$result = $sth_up->execute($begin, $end, $id);
}
if (not $result or $result eq '0E0' and $sth_in) {
$sth_in is always true for the same reason. Why do you test it here?
$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"; # DEBUG
my $result = $sth_in->execute($id, $begin, $end);
if (not $result or $result eq '0E0') {
Again, $result is always true, for the same reasons. Again, you better
wrote ($result==0).
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.
}
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
I don't get that finish line with your code.
<- 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
Neither this one.
<- 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
Not this one.
<- execute= 1 at debugSetup.pl line 48
[snip]
It seems the code you showed us is not the code you are running.
At last my stats:
Perl 5.8.8 built for sun4-solaris-thread-multi
This is old, released 2006-Feb-01.
DBI 1.48
This is ancient, released 2005-Mar-14.
DBD::CSV 0.22
This is the current version.
Can someone help?
Double-check that you showed us the right code. Try updating at least
DBI, better Perl and DBI.
Regards,
Alexander
--
Alexander Foken
mailto:[email protected] http://www.foken.de/alexander/
