Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Perl, MySQL and the work done

by mogul (Novice)
on Mar 20, 2013 at 21:06 UTC ( #1024621=perlquestion: print w/ replies, xml ) Need Help??
mogul has asked for the wisdom of the Perl Monks concerning the following question:

I'm using DBI to access a MySQL database. I do updates.

Is it possible to get both the number of rows matched and the number of rows changed? The MySQL CLI easily reports both like:

Rows matched: 1 Changed: 0 Warnings: 0
I have tried to change mysql_client_found_rows, but that only let me choose which one I get. I need both!

Comment on Perl, MySQL and the work done
Download Code
Re: Perl, MySQL and the work done
by kschwab (Priest) on Mar 20, 2013 at 22:23 UTC
Re: Perl, MySQL and the work done
by McA (Curate) on Mar 20, 2013 at 22:38 UTC
    #!/usr/bin/perl use warnings; use strict; use Data::Dumper; use DBI; my %attr = ( 'RaiseError' => 1, 'FetchHashKeyName' => 'NAME_lc', 'AutoCommit' => 0, 'mysql_enable_utf8' => 1, ); my $dbh = DBI->connect("dbi:mysql:database=xxx_db;host=127.0.0.1;port= +3306", 'xxxx_db', 'xxxxx', \%attr); $dbh->do('drop table if exists mca_test'); $dbh->do('create table mca_test (field varchar(20))'); $dbh->do("insert into mca_test(field) values ('sepp')"); $dbh->do("insert into mca_test(field) values ('kuno')"); $dbh->do("insert into mca_test(field) values ('seppl')"); $dbh->commit; my $sql = "update mca_test set field = 'seppl' where field like 'sepp% +'"; my $num = $dbh->do($sql); print Dumper($dbh->{'mysql_info'}), "\n"; print "Rows: $num\n"; $dbh->commit; $dbh->do('drop table if exists mca_test'); $dbh->disconnect;

    McA

Re: Perl, MySQL and the work done
by rnewsham (Hermit) on Mar 20, 2013 at 22:55 UTC

    $dbh->{'mysql_info'}; seems to work for me, test code below, credit to kschwab for pointing this out

    use strict; use warnings; use DBI; my $database = 'test'; my $dsn = "DBI:mysql:$database;mysql_read_default_file=$ENV{HOME}/.my. +cnf"; my $dbh = DBI->connect($dsn,undef,undef,{ RaiseError => 1 }); $dbh->do("update foo set bar=1"); my $info = $dbh->{'mysql_info'}; my ( $matched, $changed, $warnings ) = $info =~ /Rows matched: (\d+) +Changed: (\d+) Warnings: (\d+)/; print "Matched: $matched\nChanged: $changed\nWarnings: $warnings\n";

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1024621]
Approved by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2014-09-02 05:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (20 votes), past polls