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]

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

• SELECT statements
• OUTPUT parameters
• Procedure return status

OUTPUT parameters are handled in DBD::ODBC via bind_param_inout(). However, this will only work if the parameter has been declared OUTPUT in the stored procedure source - otherwise the server has no idea that it needs to return data for that parameter.

I'm not sure how to retrieve the return status with DBD::ODBC - it might be via the "call ..." syntax.

In your case the error message appears to indicate that your stored procedure doesn't define any OUTPUT parameters, so bind_param_inout) fails.

Michael

my $sth->prepare("exec DBname..stored_proc_name ?,?");
$sth->execute($var1,'var2');

while ($sth->fetchrow_hashref){.....
...
}
[download]

..spcode..
declare @var int
select @var = 42

select @var as var_name_for_hash
end
..endofsp..
[download]

CREATE TABLE dbo.TestIds
(
    TestID int not null,
    TestName varchar(50) not null
)
[download]

create proc dbo.GetTest(
    @pName varchar(50) = '',
    @pID   int = 0 out
)
as
set nocount on
begin
    select @pID = TestID from TestIds where TestName = @pName
    if ( @@rowcount = 0 )
    begin
        select @pID = isnull(max(TestID),0) + 1 from TestIDs
        insert TestIDs
        select TestID = @pID, TestName = @pName
    end
end
[download]

my $sql = '
declare @id int
exec GetTest ?, @id out
select GotID = @id
';
my $sth = $dbh->prepare($sql);
if ( $sth->execute('foo') )
{
    while (my $hash = $sth->fetchrow_hashref )
    { 
        foreach my $key (keys %$hash) { print $key . " => " . $$hash{$
+key} . "\n" if $$hash{$key}; };
    }
}
[download]

GotID => 2
[download]