[svn:p5ee] r8371 - p5ee/trunk/App-Repository/lib/App/Repository

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, [@values]);
+        }
+        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 {