[newbie]How to call stored procedure using DBIx?

[Tony Winslow]

Hi, all!

I've stored procedures defined in my database schema, and I need to call
them in my code.
The arbitrary-sql approach won't help since it writes sql statements in
source code to act as stored procedures. So could I do that in DBIx?

Thank you!

 

[Peter Scott]

I've stored procedures defined in my database schema, and I need to call
them in my code.
The arbitrary-sql approach won't help since it writes sql statements in
source code to act as stored procedures. So could I do that in DBIx?

DBIx::What? In DBI, with a DBD::Oracle driver, I call stored procedures
with a database handle thus:

$dbh->do( "Begin somepackage.someprocedure; End;" );

It's picky about the semicolons.

 

[Tony Winslow]

DBIx::What? In DBI, with a DBD::Oracle driver, I call stored procedures
with a database handle thus:

$dbh->do( "Begin somepackage.someprocedure; End;" );

It's picky about the semicolons.

The env: Catalyst, DBIx::Class, MySQL
If I can get a $dbh from what I already have from DBIx::Class, the
problem can be solved. Yet I can figure out a way to get it.

 

[smallpond]

The env: Catalyst, DBIx::Class, MySQL
If I can get a $dbh from what I already have from DBIx::Class, the
problem can be solved. Yet I can figure out a way to get it.

DBI is the generic database interface. To connect to MySQL you
need the DBD::mysql module.
http://search.cpan.org/~capttofu/DBD-mysql-4.006/lib/DBD/mysql.pm

--S

 

[Tony Winslow]

DBI is the generic database interface. To connect to MySQL you
need the DBD::mysql module.
http://search.cpan.org/~capttofu/DBD-mysql-4.006/lib/DBD/mysql.pm

--S

Of course, I've installed it.
I can connect to MySQL using DBIx::Class, and it runs smoothly.
Yet I don't know how to call my stored procedures directly using
the the API provided by DBIx::Class rather than that of DBI.

 

[smallpond]

Of course, I've installed it.
I can connect to MySQL using DBIx::Class, and it runs smoothly.
Yet I don't know how to call my stored procedures directly using
the the API provided by DBIx::Class rather than that of DBI.

Sorry. My mistake. You might try emailing the author directly.
There is some kind of hook for adding your own data accessors;
maybe that can be used.
--S

 

[smallpond]

Of course, I've installed it.
I can connect to MySQL using DBIx::Class, and it runs smoothly.
Yet I don't know how to call my stored procedures directly using
the the API provided by DBIx::Class rather than that of DBI.

Oh. I found this:
http://www.perlmonks.org/?node_id=625709

 

[Tony Winslow]

Oh. I found this:
http://www.perlmonks.org/?node_id=625709

Yes, I've read the Cookbook carefully. Yet the situation mentioned
is different from mine:
1. It is not stored procedure. It is only some plain SQL, or arbitrary
SQL as it says.
2. In my stored procedure, the parameter passed in is an output
parameter, while in the example input ones.

Anyway, thank you for your help!

 

[Peter Scott]

The env: Catalyst, DBIx::Class, MySQL
If I can get a $dbh from what I already have from DBIx::Class, the
problem can be solved. Yet I can figure out a way to get it.

See DBIx::Class::Storage:BI:

$schema->storage->dbh

 

[Tony Winslow]

See DBIx::Class::Storage:BI:

$schema->storage->dbh

I tried it and got the following error:

DBI Exception: DBD::mysql::st execute failed: OUT or INOUT argument 1
for routine MyWikiDB.countRevs is not a variable or NEW pseudo-variable
in BEFORE trigger [for Statement "CALL countRevs(?)" with ParamValues: ]
at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 940
DBIx::Class::Schema::throw_exception('MyWikiDB=HASH(0xab1ab84)', 'DBI
Exception: DBD::mysql::st execute failed: OUT or INOUT ar...') called at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage.pm line 121
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage:BI::mysql=HASH(0xab493f4)',
'DBI Exception: DBD::mysql::st execute failed: OUT or INOUT ar...')
called at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 833

Part of my codes:
my $dbh = $c->model('MyWikiDB')->schema->storage->dbh;
my $sth = $dbh->prepare("CALL countRevs(?)");
my $cnt;
$sth->bind_param(1, \$cnt);
# my @cnt;
# $sth->bind_param(1, \@cnt);
$sth->execute;

; the procedure
DROP PROCEDURE IF EXISTS countRevs;
DELIMITER //
CREATE PROCEDURE countRevs (OUT rev_cnt INT)
BEGIN
SELECT COUNT(*) INTO rev_cnt FROM revisions;
END;
//
DELIMITER ;

The procedure looks stupid since I only wrote it to
find out how to call procedures.

 

[Ben Morrow]

I tried it and got the following error:

DBI Exception: DBD::mysql::st execute failed: OUT or INOUT argument 1
for routine MyWikiDB.countRevs is not a variable or NEW pseudo-variable

Part of my codes:
my $dbh = $c->model('MyWikiDB')->schema->storage->dbh;
my $sth = $dbh->prepare("CALL countRevs(?)");
my $cnt;
$sth->bind_param(1, \$cnt);

Use ->bind_param_inout instead.

Ben

 

[Tony Winslow]

Use ->bind_param_inout instead.

Ben

right before I see your post, i did it and got:
DBI Exception: DBD::mysql::st bind_param_inout failed: Output parameters
not implemented

 

[Tony Winslow]

Use ->bind_param_inout instead.

Ben

I finally got a way out
see http://www.mysql.gr.jp/mysqlml/mysql/msg/11282
although written in Japanese, codes are readable

here comes my code:
my $dbh = $c->model('MyDB')->schema->storage->dbh;
$dbh->do('CALL countRevs(@rtnVal)');
my $cnt = $dbh->selectrow_array('SELECT @rtnVal');

Thank you all!