Perl-SQL-ADO-Stored proedures

softeng2000 has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,
Does anyone have experience with using Perl, DBI, ADO to execute SQL Stored procedure that can update input parameters for use as output.
Here is my example: sp_test_1 is stored proc that chancges value of $y. Perl script:

use DBI;
use DBD::ADO;

$dbh = DBI->connect('dbi:ADO:DSN', 'name', 'password')
            || die $DBI::errstr;

$y = 5;
  $sth = $dbh->prepare("execute sp_test_1 ?");
  $rc = $sth->bind_param_inout(1, \$y, 1) || die $dbh->errstr;
  $sth->execute;
$dbh->disconnect;
print "y = $y\n";
[download]

It must be 10 after call.
sp_test_1 works from Query Analyzer, but when I run this script I get a stange error:
"Can't locate DBI object method "bind_param_inout" via package "DBD::ADO::st"."
As far as I know there is no st package. Can you explain why it doesn't work?
Thank you.

Edited by myocom: Cleaned up formatting, added <code> tags.

Comment on Perl-SQL-ADO-Stored proedures Download Code

Replies are listed 'Best First'.
Re: Perl-SQL-ADO-Stored proedures by Fastolfe (Vicar) on Nov 11, 2000 at 00:34 UTC
PLEASE WRAP YOUR CODE IN `<CODE>` TAGS. I've never heard of the `bind_param_inout` method. Perhaps just try passing `$y` to your execute? `$sth = $dbh->prepare("execute sp_test_1 ?"); $sth->execute($y) or die "execute: $!";` [download] If 'execute' has problems with that, or it complains about your bind values, just shove it all into the same SQL statement: `$dbh->do("execute sp_test_1 '$y'"); # or $sth = $dbh->prepare("execute sp_test_1 '$y'"); $sth->execute;` [download] Of course, this is assuming that your SQL syntax is valid (which you seem to indicate it the case). I've never seen an 'execute' keyword used in SQL like that before, but my experience is not with ADO.	[reply] [d/l] [select]
RE: Re: Perl-SQL-ADO-Stored proedures by extremely (Priest) on Nov 11, 2000 at 03:37 UTC
From my MS-SQL manual: Executes a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch. In general, you can ditch the "EXECUTE" when calling stored procedures. (sp_...) so you don't often see it. It's good form to use it tho, especially when you combine it with other SQL. -- $you = new YOU; honk() if $you->love(perl)	[reply]