/usr/share/doc/libdbd-odbc-perl/examples/dml_counts.pl is in libdbd-odbc-perl 1.45-1.
This file is owned by root:root, with mode 0o755.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | #!/usr/bin/perl
# $Id$
#
# Multiple ways of getting DML counts
# Provided for:
# http://stackoverflow.com/questions/4202178/perl-dbi-getting-records-affected-of-each-statement-in-a-transaction
use DBI;
use strict;
use warnings;
use Data::Dumper;
my $h = DBI->connect();
$h->{RaiseError} = 1;
eval {$h->do(q/drop table mje/)};
$h->do(q/create table mje (a int)/);
sub example1 {
my $s = $h->prepare(<<'EOT');
declare @insert_count int
declare @update_count int
declare @delete_count int
begin tran
insert into mje values(1);
select @insert_count = @@rowcount
update mje set a = 2 where a = 1;
select @update_count = @@rowcount
delete from mje where a = 2;
select @delete_count = @@rowcount
commit tran
select @insert_count, @update_count, @delete_count
EOT
print "execute: ", $s->execute, "\n";
return $s;
}
sub example2 {
my $s = $h->prepare(<<'EOT');
begin tran
insert into mje values(1);
select @@rowcount
update mje set a = 2 where a = 1;
select @@rowcount
delete from mje where a = 2;
select @@rowcount
commit tran
EOT
print "execute: ", $s->execute, "\n";
return $s;
}
sub example3 {
eval {$h->do(q/drop procedure pmje/)};
$h->do(<<'EOT');
create procedure pmje (@insert int OUTPUT, @update int OUTPUT, @delete int OUTPUT) AS
begin tran
insert into mje values(1);
select @insert = @@rowcount
update mje set a = 2 where a = 1;
select @update = @@rowcount
delete from mje where a = 2;
select @delete = @@rowcount
commit tran
EOT
my $s = $h->prepare(q/{call pmje(?,?,?)}/);
$s->bind_param_inout(1, \my $insert, 100);
$s->bind_param_inout(2, \my $update, 100);
$s->bind_param_inout(3, \my $delete, 100);
$s->execute;
print "example3 insert=$insert, update=$update, delete=$delete\n";
}
sub example4 {
my ($inserted, $updated, $deleted);
eval {
$h->begin_work;
$inserted = $h->do(q/insert into mje values(1)/);
$updated = $h->do(q/update mje set a = 2 where a = 1/);
$deleted = $h->do(q/delete from mje where a = 2/);
$h->commit;
};
if ($@) {
$h->rollback or warn "Failed to rollback";
}
print "example4 insert=$inserted, update=$updated, delete=$deleted\n";
}
sub show_result {
my $s = shift;
do {
while (my @row = $s->fetchrow_array) {
print Dumper(\@row), "\n";
}
} while ($s->{odbc_more_results});
}
my $s = example1();
show_result($s);
$s = example2();
show_result($s);
example3();
example4();
|