Running a stored procedure

[Scott Phelps]

Here's what I was trying, I'm running this from IIS Win2k AS. I would use
ASP and would have been done quite some time ago, but there are some things
on this same page I would much rather do with perl. I've used just about
every iteration of the prepare statement I could think of, but just can't
seem to get the syntax right.

use DBI;

$keywords = "green";

my $dbh = DBI->connect( 'dbi:ODBC:myDSN', 'user', 'pass',
                        {RaiseError => 1,
                        AutoCommit => 1});

$sth = $dbh -> prepare ('sp_SEARCH_FAQ $keywords');
$sth -> execute;

while (@thedata = $sth -> fetchrow_array) {
        print "@thedata";
}

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 10:59 AM
To: [EMAIL PROTECTED]
Subject: Re: Running a stored procedure


Hi,

Scott Phelps wrote:
> I am new to DBI and have been trying to find an example of running a
stored
> procedure in MS-SQL. I have a simple search stored procedure that I need
to
> pass keywords to and receive a recordset back from. Would anyone mind
> terribly giving any examples?

I think it is done as usual, at least how it works for me (I use Sybase
though):

    $sth = $dbh -> prepare (sprintf 'my_proc %s, %s', $dbh -> quote ($user),
                                                      $dbh -> quote
($pass));
    $sth -> execute;
    while ($aref = $sth -> fetchrow_arrayref)
    {
        # do something with results (if there are any)
    }


> I'd appreciate the help, the DBI docs just say "Calling stored procedures
is
> currently not defined by the DBI", but I know it can be done!

Have you looked into DBD::Sybase and FreeTDS if you're accessing MS SQL from
UNIX?