Update statement with Perl DBI for MySQL 5.0

Discussion in 'Perl Misc' started by sam, Jan 17, 2005.

  1. sam

    sam Guest

    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
    sam, Jan 17, 2005
    #1
    1. Advertising

  2. 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
    Sherm Pendley, Jan 17, 2005
    #2
    1. Advertising

  3. sam

    J. Gleixner Guest

    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.
    J. Gleixner, Jan 18, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    103
    Paul Vudmaska
    Apr 21, 2004
  2. Jerome Hauss
    Replies:
    0
    Views:
    160
    Jerome Hauss
    Oct 13, 2004
  3. Steven Price

    mysql update with ruby + dbi

    Steven Price, Oct 21, 2009, in forum: Ruby
    Replies:
    1
    Views:
    101
    Charles Johnson
    Oct 21, 2009
  4. wana
    Replies:
    0
    Views:
    172
  5. John
    Replies:
    6
    Views:
    111
    Peter J. Holzer
    Apr 14, 2006
Loading...

Share This Page