[newbie]How to call stored procedure using DBIx?

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!

Re: [newbie]How to call stored procedure using DBIx?

On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
> 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.

--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/

Re: [newbie]How to call stored procedure using DBIx?

Peter Scott wrote:
> On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
>> 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.
>
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.

Re: How to call stored procedure using DBIx?

On Dec 28, 9:58 am, Tony Winslow wrote:
> Peter Scott wrote:
> > On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
> >> 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.
>
> 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/mys ql.pm

--S

Re: How to call stored procedure using DBIx?

smallpond wrote:
> On Dec 28, 9:58 am, Tony Winslow wrote:
>> Peter Scott wrote:
>>> On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
>>>> 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.
>> 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/mys ql.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.

Re: How to call stored procedure using DBIx?

On Dec 28, 10:51 am, Tony Winslow wrote:
> smallpond wrote:
> > On Dec 28, 9:58 am, Tony Winslow wrote:
> >> Peter Scott wrote:
> >>> On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
> >>>> 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.
> >> 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/my sql.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.

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

Re: How to call stored procedure using DBIx?

On Dec 28, 10:51 am, Tony Winslow wrote:
> smallpond wrote:
> > On Dec 28, 9:58 am, Tony Winslow wrote:
> >> Peter Scott wrote:
> >>> On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
> >>>> 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.
> >> 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/my sql.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.

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

Re: How to call stored procedure using DBIx?

smallpond wrote:
> On Dec 28, 10:51 am, Tony Winslow wrote:
>> smallpond wrote:
>>> On Dec 28, 9:58 am, Tony Winslow wrote:
>>>> Peter Scott wrote:
>>>>> On Fri, 28 Dec 2007 21:19:06 +0800, Tony Winslow wrote:
>>>>>> 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.
>>>> 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/mys ql.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.
>
> 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!

Re: [newbie]How to call stored procedure using DBIx?

On Fri, 28 Dec 2007 22:58:25 +0800, Tony Winslow wrote:
> 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::DBI:

$schema->storage->dbh

--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/

Re: [newbie]How to call stored procedure using DBIx?

Peter Scott wrote:
> On Fri, 28 Dec 2007 22:58:25 +0800, Tony Winslow wrote:
>> 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::DBI:
>
> $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(0xab1ab8 4)', '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: :DBI::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.

Re: [newbie]How to call stored procedure using DBIx?

Quoth Tony Winslow :
> Peter Scott wrote:
> > On Fri, 28 Dec 2007 22:58:25 +0800, Tony Winslow wrote:
> >> 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::DBI:
> >
> > $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

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

Re: [newbie]How to call stored procedure using DBIx?

Ben Morrow wrote:
> Quoth Tony Winslow :
>> Peter Scott wrote:
>>> On Fri, 28 Dec 2007 22:58:25 +0800, Tony Winslow wrote:
>>>> 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::DBI:
>>>
>>> $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
>
>> 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
>
right before I see your post, i did it and got:
DBI Exception: DBD::mysql::st bind_param_inout failed: Output parameters
not implemented

Re: [newbie]How to call stored procedure using DBIx?

Ben Morrow wrote:
> Quoth Tony Winslow :
>> Peter Scott wrote:
>>> On Fri, 28 Dec 2007 22:58:25 +0800, Tony Winslow wrote:
>>>> 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::DBI:
>>>
>>> $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
>
>> 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
>
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!