Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
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 (Priest) 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 drinking their drinks and smoking their pipes about the Monastery: (8)
As of 2014-12-29 10:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (186 votes), past polls