Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Don't ask to ask, just ask
 
PerlMonks  

problem calling MySQL stored proc

by ethrbunny (Monk)
on Jun 14, 2007 at 12:24 UTC ( #621223=perlquestion: print w/ replies, xml ) Need Help??
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

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

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
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?
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!

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})
      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.
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.
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://621223]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (10)
As of 2014-04-18 21:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (472 votes), past polls