Home > Archive > PERL CGI Beginners > May 2004 > Data dump using perl with mysql won't update
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Data dump using perl with mysql won't update
|
|
| smrtalec@algxmail.com 2004-05-22, 11:32 am |
| before I begin sorry for the cross post but i'm desperate
The routine in question is below. It's pretty straight forward its a series of loops which basically matches the row then dumps data stored in hash ref into the DB. for some reason the script runs with no errors (ran with strict) I can verify that the da
ta is being iterated, but when I check the Db no data is updated, the int fields have been updated to 0 as opposed to NULL. is anyone seeing somethign I'm not.
sub write_db_2 {
my ($ar_info,$table) = @_;
my ($dbh,$sth,$k,$rh_row);
$dbh = connect_try("rowan","5340brig");
foreach $k (keys (%{$ar_info->[1]})){
if ($table eq "prop_info"){
$sth = $dbh->prepare ("UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?;") or
err_trap("failed to prepare statement\n");
}elsif ($table eq "own_info"){
$sth = $dbh->prepare ("UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?;") or
err_trap("failed to prepare statement\n");
}
foreach $rh_row (@$ar_info) {
print "::$k=>$rh_row->{$k}";
$sth->bind_param (1,$rh_row->{$k});
if ($table eq "prop_str_addr") {
$sth->bind_param (2,$rh_row->{prop_str_addr});
}elsif ($table eq "own_str_addr") {
$sth->bind_param (2,$rh_row->{own_str_addr});
}
$sth->execute() or
err_trap("failed to execute statement\n");
}
print "\n===========================\n";
}
$sth->finish();
$dbh->disconnect or
err_trap("failed to disconnect statement\n");
}
| |
| smrtalec@algxmail.com 2004-05-22, 11:32 am |
|
> If you want them set to NULL using DBI, you have to bind undef to the
> proper parameter. Is that what you're doing, or are you binding
> something else, such as 0 or the empty string?
>
No the initial values in the DB are set to null, with the exception of one field. I'm trying to assign the correct values to each field as I iterate through the rows. However after runnign the script the values either remain at null or 0 in the caase of a
n intiger. I'm reposting a clearer version of the script which actually has remarks
sub write_db_2 { #updates specified table in a dump fasion using a hash table
my ($ar_info,$table) = @_;
my ($dbh,$sth,$k,$rh_row);
$dbh = connect_try("*******","******");
foreach $k (keys (%{$ar_info->[1]})){ # retrieves a generic set of fields and uses them to assign values for each row.
if ($table eq "prop_info"){ # checks which table is being used and assigns the correct SQL statement
$sth = $dbh->prepare ("UPDATE prop_info
SET $k = ?
WHERE prop_str_addr = ?;") or
err_trap("failed to prepare statement\n");
}elsif ($table eq "own_info"){
$sth = $dbh->prepare ("UPDATE own_info
SET $k = ?
WHERE own_str_addr = ?;") or
err_trap("failed to prepare statement\n");
}
foreach $rh_row (@$ar_info) { # iterates through the list of rows and assigns the correct value to the field
print "::$k=>$rh_row->{$k}"; # this is an internal check to verify what values are being inserted
$sth->bind_param (1,$rh_row->{$k});
if ($table eq "prop_str_addr") {
$sth->bind_param (2,$rh_row->{prop_str_addr});
}elsif ($table eq "own_str_addr") {
$sth->bind_param (2,$rh_row->{own_str_addr});
}
$sth->execute() or
err_trap("failed to execute statement\n");
}
print "\n===========================\n";
}
$sth->finish();
$dbh->disconnect or
err_trap("failed to disconnect statement\n");
}
| |
| Paul Dubois 2004-05-22, 11:32 am |
| At 15:02 -0700 5/5/04, smrtalec@algxmail.com wrote:
>before I begin sorry for the cross post but i'm desperate
>The routine in question is below. It's pretty straight forward its a
>series of loops which basically matches the row then dumps data
>stored in hash ref into the DB. for some reason the script runs with
>no errors (ran with strict) I can verify that the data is being
>iterated, but when I check the Db no data is updated, the int fields
>have been updated to 0 as opposed to NULL. is anyone seeing
>somethign I'm not.
If you want them set to NULL using DBI, you have to bind undef to the
proper parameter. Is that what you're doing, or are you binding
something else, such as 0 or the empty string?
Have you tried turning on DBI's trace facility to see what's happening?
P.S., you don't need the semicolons at the ends of the query strings.
That's a mysql client thing, but you're not using mysql.
>sub write_db_2 {
> my ($ar_info,$table) = @_;
> my ($dbh,$sth,$k,$rh_row);
> $dbh = connect_try("rowan","5340brig");
> foreach $k (keys (%{$ar_info->[1]})){
> if ($table eq "prop_info"){
> $sth = $dbh->prepare ("UPDATE prop_info
> SET $k = ?
> WHERE prop_str_addr = ?;") or
> err_trap("failed to prepare
>statement\n");
> }elsif ($table eq "own_info"){
> $sth = $dbh->prepare ("UPDATE own_info
> SET $k = ?
> WHERE own_str_addr = ?;") or
> err_trap("failed to prepare
>statement\n");
>
>
> }
> foreach $rh_row (@$ar_info) {
> print "::$k=>$rh_row->{$k}";
> $sth->bind_param (1,$rh_row->{$k});
> if ($table eq "prop_str_addr") {
> $sth->bind_param (2,$rh_row->{prop_str_addr});
> }elsif ($table eq "own_str_addr") {
> $sth->bind_param (2,$rh_row->{own_str_addr});
> }
> $sth->execute() or
> err_trap("failed to execute statement\n");
>
> }
> print "\n===========================\n";
>
> }
> $sth->finish();
> $dbh->disconnect or
> err_trap("failed to disconnect statement\n");
>}
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
|
|
|
|
|