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

Is it possible to call MySQL stored procedures from Perl using the DBI and DBD::mysql modules?

Stored procedures would mean less coding on the Perl side.

Thanks.

  • Comment on Calling MySQL Stored Procedures using DBI and DBD::mysql

Replies are listed 'Best First'.
Re: Calling MySQL Stored Procedures using DBI and DBD::mysql
by Mandrake (Chaplain) on Jan 25, 2006 at 03:17 UTC
    Try 
    my $sql = "call PROC()" ;
$sth = $dbh->prepare($sql);
$sth->execute();

# PROC is the name of the procedure.

    [download]
    Could not test this code since I don't have access to MySQL at my workplace.

    Thanks..
[reply]
[d/l]
Re: Calling MySQL Stored Procedures using DBI and DBD::mysql
by jbrugger (Parson) on Jan 25, 2006 at 06:21 UTC
    From MySQL 5, Stored Procedures are now part of the MySQL database engine.

    > Stored procedures would mean less coding on the Perl side.

    Keep in mind though, that on this moment this is the only advantage you have.
    The implementation of sp will lead to worse performance than the embedded SQL because there is no query execution plan caching implemented yet, meaning the stored procedure must be re-evaluated each time it is executed (perhaps this is changed in a later version).
    On this moment, the main advantage you get are code encapsolation and putting the business logic into the database tier.

    When you want 'raw speed' use the emmeded way, and read Speeding up the DBI for optimal performance

    "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.
[reply]

Back to Seekers of Perl Wisdom