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

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

.

Relevant Pages

  • Sybase ASE through DBD::ADO: Problem calling proc with param
    ... I am having trouble calling any stored procedure with a parameter through DBI ... and DBD::ADO on a Sybase Adaptive Server Enterprise. ... "The provider cannot derive parameter info and SetParameterInfo has not been called" ... Sybase ASE OLE DB Provider 2.70.0.24 ...
    (perl.dbi.users)
  • Re: Stored Procedures & Parameters Insert Problem
    ... Why use the MSDataShape provider? ... This is so that I can easily change the stored procedure in the sample ... an AutoNumber primary key field, and lngTestCount. ... Dim adoDBConn As ADODB.Connection ...
    (microsoft.public.data.ado)
  • Re: DeriveParameters and Jet OLEDB Provider
    ... retrieves provider-side parameter information for the stored procedure or ... parameterized query specified in the Command object. ... In Jet a 'saved' query is not a stored procedure. ... > method with this provider to populate a Paramters collection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Foxpro vs SQL Server
    ... "OLE DB Provider for Visual FoxPro". ... These apply to any Stored Procedure your write! ... While there is a newer one that comes with the VFP 9.0 public beta, ...
    (microsoft.public.fox.vfp.queries-sql)
  • Having problem with OLE DB provider IBMDADB2
    ... linked server, the linked server uses OLE DB provider 'IBMDADB2'. ... stored procedure a couple of days back and i was able to insert 250 records ... OLE DB provider 'IBMDADB2' reported an error. ...
    (microsoft.public.sqlserver.connect)