problem calling MySQL stored proc

ethrbunny has asked for the wisdom of the Perl Monks concerning the following question:

Situation: two machines. Same versions of DBI and DBD::mysql.
Running the following sub:

use DBI;


my $dbh;

open_db_conn();

my @active_homes = get_active_homes();

$dbh->disconnect();

sub open_db_conn {
  my $dbd      = "mysql";
  my $host     = "host_name";
  my $db_name  = "db_name";
  my $user     = "login";
  my $password = "pass";
  my $dsn      = "DBI:$dbd:$db_name:$host";

  eval { $dbh =  DBI->connect( $dsn, $user, $password,
                       { RaiseError => 1, AutoCommit => 0 } ); };
print "Could not open database connection: $@"  if $@;
} # &open_db_conn


# Get active homes.
#
sub get_active_homes {

  my $sth = $dbh->prepare( qq{ call myProc() } );
        $sth->execute();

  print "Trouble with database connection: $@"  if $@;

} # &get_active_homes
[download]

Code runs fine on machine #1.
Running on machine #2 returns the following:
DBD::mysql::st execute failed: PROCEDURE db_name.myProc can't return a result set in the given context at ./test0.pl line 32.
DBD::mysql::st execute failed: PROCEDURE db_name.myProc can't return a result set in the given context at ./test0.pl line 32.

Both systems report the following from CPAN:

cpan[1]> i DBD::mysql
CPAN: Storable loaded ok
Going to read /u/user/.cpan/Metadata
  Database was generated on Wed, 13 Jun 2007 03:07:59 GMT
Strange distribution name [DBD::mysql]
Bundle    Bundle::DBD::mysql     (CAPTTOFU/DBD-mysql-4.005.tar.gz)
Module    DBD::mysql             (CAPTTOFU/DBD-mysql-4.005.tar.gz)
2 items found

cpan[2]> i DBI
Strange distribution name [DBI]
Bundle    Bundle::DBI            (TIMB/DBI-1.56.tar.gz)
Module    DBI                    (TIMB/DBI-1.56.tar.gz)
2 items found
[download]

What other modules do I need to synch?
What other step(s) did I miss?

Comment on problem calling MySQL stored proc Select or Download Code

Replies are listed 'Best First'.
Re: problem calling MySQL stored proc by PreferredUserName (Pilgrim) on Jun 14, 2007 at 14:35 UTC
Here is some discussion of that problem. Are both programs connecting to the same database? With identical connection params? Are you sure they're executing the same procedure on that database?	[reply]
Re: problem calling MySQL stored proc by andreas1234567 (Vicar) on Jun 14, 2007 at 20:20 UTC
I don't know if it's related to your specific problem, but it says here that there is a bug in DBD::MySQL that won't allow you to set 'mysql_multi_results=1', therefore producing the "can't return a result set in the given context" error. Read more... (3 kB)	[reply] [d/l] [select]
Re: problem calling MySQL stored proc by naikonta (Curate) on Jun 14, 2007 at 14:37 UTC
And what are the version of MySQL on both macines? Did you populate it with the same tables and data? Did you configure them exactly the same setting? Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!	[reply]
Re: problem calling MySQL stored proc by andreas1234567 (Vicar) on Jun 14, 2007 at 15:41 UTC
Please show us the source code for db_name.myProc. Does it have any input or output arguments? What does it return? What does your sample data look like? -- print map{chr}unpack(q{A3}x24,q{074117115116032097110111116104101114032080101114108032104097099107101114})	[reply]
Re^2: problem calling MySQL stored proc by ethrbunny (Monk) on Jun 14, 2007 at 16:23 UTC
Both machines are hitting the same DB on the same server. Its MySql 5.0.12. The proc in question is only returning one result set which is another reason why this issue baffles me. The proc does a series of joins into a temporary table. It then does a 'select distinct' which creates the result set that Im looking for.	[reply]
Re: problem calling MySQL stored proc by kwaping (Priest) on Jun 14, 2007 at 17:06 UTC
Read the user comments on this page for some tips and pointers. --- It's all fine and dandy until someone has to look at the code.	[reply]


more useful options
	PerlMonks