Re: Yet Another Stored Procedure Question

This is covered in DBI::FAQ ... but the answer isn't all that helpfull. the basic idea is that:

DBI doesn't provide anyway to call any DB specific Stored procs, but you can run any SQL, which might include SQL that calls a stored proc.
any SQL which sets a value can be used to set a value that you have bound using bind_param_inout

In some databases, you could do this like so...

my $rv  = 0;
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare('{?=call name_of_sproc()}');
$sth->bind_param_inout(1, \$rv, 4);
$sth->execute();
$sth->finish();
print "return value: $rv\n";
[download]

Comment on Re: Yet Another Stored Procedure Question Download Code

Replies are listed 'Best First'.
The Results Are In! (Returning data from stored procedures on MS SQL and Win32 using DBI::ODBC) by Reverend Phil (Pilgrim) on Jun 24, 2003 at 15:15 UTC
Thanks to everyone who responded. A half a fresh night's sleep, 200 cathartic pages more of Harry Potter, and of course the replies I received here got me hitting things from different angles, and I've gotten it to work. I'm posting what I did here because hopefully this will help the next person who searches for stored procedures and find lots of mostly helpful tidbits =) This is in reference (as the title suggests) to calling stored procedures via DBI&DBD::ODBC on Win32 systems to an m$ SQL server. The test table contained two fields: InField is a varchar field, and OutField is an int, autonumbering. `CREATE PROCEDURE TestProc @InField varchar(50) AS INSERT INTO TestTable (InField) VALUES (@InField) SELECT @@identity GO` [download] For those as unfamiliar with stored procs as I am, @@identity on MS SQL returns the last selected identity-type field (ie: autonumbering) in the previous action. Here is the perl code which drops off data and picks up the auto-number field. `..connect to db.. my $sth = $dbh->prepare('{call TestProc(?)}'); my $a = "TEST"; $sth->bind_param(1, $a) or die $sth->errstr; $sth->execute(); my $results = $sth->fetch(); $sth->finish(); $dbh->disconnect; print "results are $$results[0]\n";` [download] Not the prettiest piece of code, but hopefully it's basic enough to be clear to somebody looking to accomplish the same thing. Thanks again for the help and the fresh angles. The myriad docs and posts on various sites all had different ways to go about it, and whether or not they work seems quite dependant on so many things. -=rev=-	[reply] [d/l] [select]

Replies are listed 'Best First'.

The Results Are In! (Returning data from stored procedures on MS SQL and Win32 using DBI::ODBC)
by Reverend Phil (Pilgrim) on Jun 24, 2003 at 15:15 UTC

Harry Potter

DBI

DBD::ODBC

CREATE PROCEDURE TestProc
@InField varchar(50)
AS
INSERT INTO TestTable
(InField)
VALUES
(@InField)
SELECT @@identity
GO
[download]

..connect to db..
my $sth = $dbh->prepare('{call TestProc(?)}');
my $a = "TEST"; 
$sth->bind_param(1, $a) or die $sth->errstr;
$sth->execute();
my $results = $sth->fetch();
$sth->finish();
$dbh->disconnect;

print "results are $$results[0]\n";
[download]

[reply]
[d/l]
[select]