Re: Sybase ASE through DBD::ADO: Problem calling proc with param

[Steffen Goeldner]

Philip Zembrod wrote:

Hello,

I am having trouble calling any stored procedure with a parameter through DBI
and DBD::ADO on a Sybase Adaptive Server Enterprise.

What I would finally like to do is call a stored proc with an output parameter
through OLE DB. But as yet I can't even call a procedure with an input 
parameter.

Currently, DBD::ADO doesn't support bind_param_inout().

I basically get the following error message:

OLE exception from "ASE OLE DB Provider":

No value given for one or more required parameters

Win32::OLE(0.1701) error 0x80040e10
  in METHOD/PROPERTYGET "Execute" at q:/run/site/lib/DBD/ADO.pm line 1073
DBD::ADO::st execute failed: Can't Execute Command 'exec sp_help @objname = ?'

I'll paste the script causing this error at the end of this mail.
If you change $ado from 1 to 0 then the script will use DBD::Sybase
instead and all is fine.

I have user DBI_TRACE with high levels. I get this message from the
ASE OLE DB Provider:
"The provider cannot derive parameter info and SetParameterInfo has not been 
called"
Later I see: Parameter: Name => 0 Type => 200 Direction => 1 Size => 1
Type and Direction seem ok, Name and Size maybe not so.
(Trying $sth->{ado_comm}->Parameters->Item(0)->{Name} = '@objname';
just resulted in "Der Vorgang ist in diesem Zusammenhang nicht  zugelassen.",
meaning this action is not permitted in this context.)

I am quite lost now and would appreciate any help. Am I missing something, or 
did
I encounter a bug in the OLE DB provider?

My environment is:
WinXP SP1 (german)
ActivePerl 5.8.2 build 808
DBI 1.49
DBD::ADO 2.94
DBD::Sybase 1.04.12
Sybase ASE OLE DB Provider 2.70.0.24
Sybase ASE 12.5.3

Thanks in advance
Philip

And now comes the script:
--------------------------------
use strict;
use DBI;

my $server = '<server>';
my $user   = '<user>';
my $passwd = '<passwd>';

my $ado = 1;

my $stConnect = $ado ? 'dbi:ADO:Provider=Sybase ASE OLE DB Provider;Data 
source='
    : 'dbi:Sybase:server=';
my $stVal = 'sysobjects';

my $dbh = DBI->connect($stConnect . $server, $user, $passwd ,{RaiseError => 1, 
PrintError => 0, Warn=>1});
my $sth = $dbh->prepare('exec sp_help @objname = ?', 
{CommandType=>'adCmdStoredProc'}) or confess $dbh->errstr();

my $rc = $sth->bind_param(1, $stVal, {TYPE=>DBI::SQL_VARCHAR()}) or confess $sth->errstr(); $sth->execute();

DBI::dump_results($sth);
$sth->finish;

I have no Sybase available, only it's derivative:

  set DBI_DSN=dbi:ADO:Provider=SQLOLEDB;Trusted_Connection=Yes

Running

  my $sth = $dbh->prepare('sp_help @objname = ?');
  $sth->bind_param( 1,'sysobjects');
  $sth->execute;
  $sth->dump_results;

shows

  'sysobjects', 'dbo', 'system table', '2002-12-17 14:36:10'
  1 rows


Steffen