Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Update statement with Perl DBI for MySQL 5.0

Reply
Thread Tools

Update statement with Perl DBI for MySQL 5.0

 
 
sam
Guest
Posts: n/a
 
      01-17-2005
Hi,
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;

Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.

If I copy the update statement exactly from the output of the print
statement ( print $sql), and paste it to the mysql login prompt to
execute it, the update statement update the record instantly. I don't
know what is happening here, why perl dbi does not do what the update
statement supposed to do? Have I missed a commit statement? but I don't
have idea how to place a commit statement to perl dbi.

Thanks
Sam
 
Reply With Quote
 
 
 
 
Sherm Pendley
Guest
Posts: n/a
 
      01-17-2005
sam wrote:

> $sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
> transtype=\"$transtype\",
> returnreason=\"$returnreason\"
> where prodcode=\"$prodcode\" and
> custcode=\"$custcode\" and date=$transdate";
> $udpate_sql = qq {$sql;};
> $sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
> $DBI::errstr";
> $sth->execute || die "Unable to execute query: $dbh->errstr\n";
> $sth->finish;
> print $sql;
>
> Currently I found somethingn strange with mysql(perhaps with perl dbi as
> well), after setup 1 or 2 test data, I tried to make a change to one of
> the fiield in a table, but after update is completed, I don't see the
> corresponding field in a record is updated by the new value.


It sounds like a quoting problem - your example is just *begging* for
placeholders anyway. Here's how you'd use placeholders instead:

my $sql = 'update transaction
set salesvolume=?, netsales=?,
transtype=?, returnreason=?,
where prodcode=? and custcode=? and date=?';

my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

$sth->execute($salesvol, $netsales, $transtype, $returnreason,
$prodcode, $custcode, $transdate) ||
die "Unable to execute query: $dbh->errstr";
$sth->finish;

Using placeholders does two things for you: First, it handles all the
quoting automagically. That's handy. And second, if you're inserting more
than one row, it's far more efficient: Instead of repeatedly building up a
series of SQL strings that you prepare() and execute() one at a time, you
need only call prepare() once, and then make as many calls to execute() as
you need to.

sherm--

--
Cocoa programming in Perl: http://camelbones.sourceforge.net
Hire me! My resume: http://www.dot-app.org
 
Reply With Quote
 
 
 
 
J. Gleixner
Guest
Posts: n/a
 
      01-18-2005
sam wrote:
> Hi,
> Here is my sample code:

[...]
> $udpate_sql = qq {$sql;};
> $sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
> $DBI::errstr";


If this truly is your code.. update_sql is undefined.

use strict;

Would likely make the typo more obvious. Also, you could also use single
quotes, making your SQL statement much simpler, placeholders would be
the best solution.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql update with ruby + dbi Steven Price Ruby 1 10-21-2009 01:02 PM
perl MySQL using DBI - security issue John Perl Misc 6 04-14-2006 03:32 PM
DBI Mysql storing DBI:binary, trouble with character 26 Jerome Hauss Ruby 0 10-13-2004 03:04 PM
apache mod perl dbi mysql : Premature end of script headers: wana Perl Misc 0 09-17-2004 09:52 PM
dbi:mysql mysql has gone away Paul Vudmaska Ruby 0 04-21-2004 06:19 PM



Advertisments