Hi all,
unfortunately I must continue this thread. I managed to update DBI on the
Web-Server, where my test-script corrupts data while updating - and still it
does not work. I checked it on another computer where it works fine:
OK 1:
DBI 1.607-ithread
DBD::CSV version 0.22
perl 5.10.0
Linux 2.6.27.15-170.2.24.fc10.x86_64 #1 SMP Wed Feb 11 23:14:31 EST 2009 x86_64
x86_64 x86_64 GNU/Linux
OK 2:
DBI 1.52-ithread
DBD::CSV version 0.22
perl 5.8.8
Linux 2.6.18.8-0.7-default #1 SMP Tue Oct 2 17:21:08 UTC 2007 i686 i686 i386
GNU/Linux
NOT OK:
DBI 1.607-ithread
DBD::CSV version 0.22
perl 5.8.8
SunOS 5.10 Generic_118822-30 sun4u sparc SUNW,Ultra-250
Now it seems to me that the difference is the OS or a speciallity in a strange
setup of Perl which I can not see. Even trace(15) shows no differences in the
execute-part (trace(9), as I set in the script, is for the execute-part the same
as 15).
What's going on? Where should I look for the cause of the problem?
Greetings
Robert
PS: Here again my test-script. The 1st execution creates the table 'Projects' in
/tmp. The 2nd execution should update the data (infact if everything went fine
nothing changes, because the UPDATE woks with the same data as the INSERT).
###########
use strict;
use warnings;
use DBI;
my %projects = (
'ID001' => {
'begin' => '20040101',
'end' => '20080630',
},
'ID002' => {
'begin' => '20050301',
'end' => '20091231',
},
'ID003' => {
'begin' => '20050701',
'end' => '20100430',
},
);
DBI->trace(9);
my $dbh = DBI->connect("dbi:CSV:f_dir=/tmp;csv_eol=\n",'','',
{ AutoCommit => 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";
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};
warn "will try UPDATE Projects\n";
my $result = $sth_up->execute($begin, $end, $id);
if ($result == 0) {
warn "will INSERT INTO Projects\n";
$result = $sth_in->execute($id, $begin, $end);
if ($result == 0) {
warn "Could not update table 'Projects' project $id\n";
}
}
}
$sth_up->finish();
$sth_in->finish();
$dbh->disconnect();
warn "finished\n";
###########
----
Robert Roggenbuck schrieb:
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
[snip]
