Thank You for Your reply.
The most important thing first: I tried the script on a slightly newer
environment (but still a very old one) and it works:
Perl, v5.8.8 built for i586-linux-thread-multi (SuSe-Linux, kernel 2.6.18)
DBI 1.52
DBD::CSV 0.22
Interesting to see that the DBD::CSV is the same.
Other things inlined ...
Alexander Foken schrieb:
Hello,
On 30.06.2009 14:41, Robert Roggenbuck wrote:
Hi all,
[snip]
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 conclude from my successful run, that there were something wrong in the
interaction between DBD::CSV and DBI, because a newer DBI banish the phantom.
[snip]
Here is the script:
It has some parts that look very strange to me.
You have keen eyes ;-)
[snip]
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.
Yes. This (and other things You detected) are remnants from the shortening of
the original program to generate a minimal test script. Usually I set RaiseError
and PrintError to false and make my own error handling.
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.
The dates I use are always eight-character-strings. I don't do any fancy things
with them besides comparing them (see %projects). But of course usually dates
should be dates and not CHARs or INTEGERs.
[snip]
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.
Really? I did it very often to move several preparations of SQL-statements as
far as possible outside loops. As I understood this is the main intention for
preparing a statement: speed up the execution by avoiding repeatedly prepares.
[snip]
if ($sth_up) {
$sth_up is always true (prepare dies on error due to RaiseError =>1),
why do you test it here?
You are right. In the original script was RaiseError => 0.
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).
Yes and No. You are right for the same reason. But because in the context of my
program even a '0E0' should not happen, I treat it as an error.
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.
I did not called finish() because there is no more code after the loop and the
allocated space will be freed by the termination of the program - as You
mentioned. Does finish() more things? Is it recommended to call finish() in any
case?
}
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.
There is none. This must be an "implicit finish()" by DBD::File or so.
<- 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.
It is the code. And even in the trace in my successful environment are there
finish-calls (see at the end of this mail).
Greetings
Robert
PS:
Here is just for completeness the slightly modified code. (Btw.: I modified it
after the successful run in the alternative environment.)
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 => 0, 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;
warn "will try UPDATE Projects\n"; # DEBUG
$result = $sth_up->execute($begin, $end, $id);
if ($result == 0) {
warn "will INSERT INTO Projects\n"; # DEBUG
my $result = $sth_in->execute($id, $begin, $end);
if ($result == 0) {
warn "Could not update table 'Projects' project '$id':
$DBI::errstr\n";
}
}
}
$sth_up->finish();
$sth_in->finish();
warn "finished\n";
================================
TRACE:
================================
[snip]
will try UPDATE Projects
-> execute in DBD::File::st for DBD::CSV::st
(DBI::st=HASH(0x860c6d0)~0x860c904 '20040101' '20080630' 'ID001') thr#8167008
1 -> finish in DBD::File::st for DBD::CSV::st (DBI::st=HASH(0x860c904)~INNER)
thr#8167008
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 47
will try UPDATE Projects
-> execute in DBD::File::st for DBD::CSV::st
(DBI::st=HASH(0x860c6d0)~0x860c904 '20050701' '20100430' 'ID003') thr#8167008
1 -> finish in DBD::File::st for DBD::CSV::st (DBI::st=HASH(0x860c904)~INNER)
thr#8167008
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 47
will try UPDATE Projects
-> execute for DBD::CSV::st (DBI::st=HASH(0x860c6d0)~0x860c904 '20050301'
'20091231' 'ID002') thr#8167008
1 -> finish for DBD::CSV::st (DBI::st=HASH(0x860c904)~INNER) thr#8167008
1 <- finish= 1 at File.pm line 439
<- execute= 1 at debugSetup.pl line 47
-> finish for DBD::CSV::st (DBI::st=HASH(0x860c6d0)~0x860c904) thr#8167008
<- finish= 1 at debugSetup.pl line 60
-> finish for DBD::CSV::st (DBI::st=HASH(0x860c9d0)~0x8611000) thr#8167008
<- finish= 1 at debugSetup.pl line 61
finished
-> DESTROY in DBD::File::st for DBD::CSV::st
(DBI::st=HASH(0x8611000)~INNER) thr#8167008
<- DESTROY= ''
-> DESTROY for DBD::CSV::st (DBI::st=HASH(0x860c904)~INNER) thr#8167008
<- DESTROY= ''
-> DESTROY in DBD::File::db for DBD::CSV::db
(DBI::db=HASH(0x860c5a4)~INNER) thr#8167008
-> disconnect in DBD::File::db for DBD::CSV::db
(DBI::db=HASH(0x860c5a4)~INNER) thr#8167008
1 -> STORE in DBD::File::db for DBD::CSV::db (DBI::db=HASH(0x860c5a4)~INNER
'Active' 0) thr#8167008
1 <- STORE= 1 at File.pm line 222
<- disconnect= 1 at File.pm line 280
<- DESTROY= 1
-- DBI::END
-> disconnect_all in DBD::File::dr for DBD::CSV::dr
(DBI::dr=HASH(0x825e29c)~0x860c1e4) thr#8167008
<- disconnect_all= undef at DBI.pm line 700
! -> DESTROY in DBD::File::dr for DBD::CSV::dr (DBI::dr=HASH(0x860c1e4)~INNER)
thr#8167008
! <- DESTROY= undef during global destruction
================================
