Hi, Try using the DBI->trace(2) method. This will print a lot of stuff on your screen however it shows the UPDATE after it has bound all the variables etc. You will see the statement as it will be passed to the database. This has saved me a lot of work recently in showing up bugs very quickly. Also note that under certain circumstances, the UPDATE may not return 0 but return 0E0. This is also in the docs shown below.
It is documented on the CPAN site. http://www.cpan.org and go to search, look for DBI and read the documentation there. It is comprehensively covered there. I would also probably tend to use placeholders for your prepare, also if you use the RaiseError rather than having to check (at least while you are debugging) you will get an instant response to any errorcode eg. my %err_handle = ( PrintError => 1, RaiseError => 1 ); $dbh = $dsn etc.etc.etc. $sth = $dbh->prepare("UPDATE transaction SET salesvolume=?, netsales=?, transtype=?, returnreason=? WHERE prodcode=? AND custcode=? AND date=?"); $sth->execute($salesvol, $netsales, $transtype, $returnreason, $prodcode, $custcode, $transdate); $sth->finish; print $sql; Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -----Original Message----- From: sam wun [mailto:[EMAIL PROTECTED] Sent: Monday, 17 January 2005 4:23 PM To: [EMAIL PROTECTED] Cc: Mattias J; mysql@lists.mysql.com Subject: Re: Insert if Update failed without Select [EMAIL PROTECTED] wrote: >>Please also note hat UPDATE returns the number of records updated. If your >>UPDATE returns 0, you know that the record does not exist, and you might >>want to INSERT instead. >> >> > >There is one situation where the number of records updated will return >0, yet the row exists. If you update the record with the exact same >information, mySQL will return a count of 0 rows updated. Yet the row >exists. > > Hi, how can I find out the return value from update if I execute update in perl dbi? 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 > Brad Eacker ([EMAIL PROTECTED]) > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
