Author: spadkins
Date: Thu Dec 7 12:57:24 2006
New Revision: 8371
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
Log:
add call_procedure()
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Thu Dec 7 12:57:24 2006
@@ -2472,8 +2472,10 @@
=cut
sub begin {
+ &App::sub_entry if ($App::trace);
my $self = shift;
$self->_do("begin");
+ &App::sub_exit() if ($App::trace);
}
#############################################################################
@@ -2495,8 +2497,10 @@
=cut
sub commit {
+ &App::sub_entry if ($App::trace);
my $self = shift;
$self->_do("commit");
+ &App::sub_exit() if ($App::trace);
}
#############################################################################
@@ -2518,13 +2522,119 @@
=cut
sub rollback {
+ &App::sub_entry if ($App::trace);
my $self = shift;
$self->_do("rollback");
+ &App::sub_exit() if ($App::trace);
+}
+
+#############################################################################
+# call_procedure()
+#############################################################################
+
+=head2 call_procedure()
+
+ * Signature: $rep->call_procedure($call_str);
+ * Signature: $rep->call_procedure($call_str, $return_type, $param_types,
@params);
+ * Signature: $result = $rep->call_procedure($call_str, $return_type);
+ * Signature: $result = $rep->call_procedure($call_str, $return_type,
$param_types, @params);
+ * Signature: @results = $rep->call_procedure($call_str, $return_type);
+ * Signature: @results = $rep->call_procedure($call_str, $return_type,
$param_types, @params);
+ * Signature: $rows = $rep->call_procedure($call_str, $return_type);
+ * Signature: $rows = $rep->call_procedure($call_str, $return_type,
$param_types, @params);
+ * Param: void
+ * Return: $result string (if $return_type is "SCALAR")
+ * Return: @results ARRAY (if $return_type is "LIST")
+ * Return: $row ARRAY (if $return_type is "ROW")
+ * Return: $rows ARRAY (if $return_type is "ROWS")
+ * Throws: App::Exception::Repository
+ * Since: 0.01
+
+There is no standard way to call stored procedures in the DBI.
+This is an attempt to provide access to them.
+
+ MySQL: Sample Usage
+ 1. As of DBD-mysql-3.0008 and MySQL 5.1.12, INOUT and OUT parameters are
not supported
+ 2. In order to receive values back from a stored procedure in MySQL,
+ you need to have applied the "dbd-mysql-multi-statements.patch" patch.
+ https://rt.cpan.org/Public/Bug/Display.html?id=12322
+
https://rt.cpan.org/Ticket/Attachment/167152/53763/dbd-mysql-multi-statements.patch
+ This supports the "SCALAR" return type (and maybe "LIST" and "ROW"), but
+ a stored procedure can still not return multiple rows ("ROWS"). (I
think.)
+ You DSN needs to have "mysql_multi_results=1" set to activate the
ability to
+ get rows back from a stored procedure.
+
+ $rep->call_procedure("call sp_doit('prod',5)");
+ $val = $rep->call_procedure("call sp_doit_return_val('prod',5)",
"SCALAR");
+ ($val1, $val2) = $rep->call_procedure("call
sp_doit_return_vals('prod',5)", "LIST");
+ $row = $rep->call_procedure("call
sp_doit_return_vals('prod',5)", "ROW");
+
+=cut
+
+sub call_procedure {
+ &App::sub_entry if ($App::trace);
+ my ($self, $call_str, $return_type, $param_options, @params) = @_;
+ my $dbh = $self->{dbh};
+ my $sth = $dbh->prepare($call_str);
+ my ($i, $param_option, $param_direction, $param_length, $param_type);
+ for ($i = 0; $i <= $#params; $i++) {
+ $param_option = $param_options->[$i];
+ if (!ref($param_option)) {
+ $param_direction = $param_option || "IN";
+ $param_length = 100;
+ $param_type = undef;
+ }
+ else {
+ $param_direction = $param_option->{direction} || "IN";
+ $param_length = $param_option->{length} || 100;
+ $param_type = $param_option;
+ }
+ if ($param_direction eq "OUT") {
+ $sth->bind_param_inout($i+1, \$_[$i+4], $param_length);
+ }
+ elsif ($param_direction eq "INOUT") {
+ $sth->bind_param_inout($i+1, \$_[$i+4], $param_length);
+ }
+ else {
+ $sth->bind_param($i+1, $params[$i], $param_type);
+ }
+ }
+ $sth->execute();
+ my (@values);
+ my $rows = [];
+ if (defined $return_type) {
+ while (@values = $sth->fetchrow_array()) {
+ push(@$rows, [EMAIL PROTECTED]);
+ }
+ if ($return_type eq "LIST") {
+ @values = @{$rows->[0]} if ($#$rows > -1);
+ }
+ elsif ($return_type eq "SCALAR") {
+ @values = ($rows->[0][0]) if ($#$rows > -1 && $#{$rows->[0]} > -1);
+ }
+ elsif ($return_type eq "ROW") {
+ @values = ( $rows->[0] ) if ($#$rows > -1);
+ }
+ elsif ($return_type eq "ROWS") {
+ @values = ( $rows );
+ }
+ }
+ $sth->finish();
+ if ($return_type eq "LIST") {
+ &App::sub_exit(@values) if ($App::trace);
+ return(@values);
+ }
+ else {
+ &App::sub_exit($values[0]) if ($App::trace);
+ return($values[0]);
+ }
}
sub explain_sql {
+ &App::sub_entry if ($App::trace);
my ($self, $sql) = @_;
# to be overridden in each Repository class
+ &App::sub_exit() if ($App::trace);
}
sub _get_timer {
