Thomas A. Lowery wrote:
>Roger,
> My work with DBD-ADO and stored procedures is very limited. If you could
> provide me with a few (at least one) complete example that generates this
> error, fixing it would be much easier. Also, I haven't a lot of time to
> work on DBD-ADO.
>
My main focus is on DBD-ODBC but I found a bug that haven't been fixed
yet. I therefore thought I might give DBD-ADO a try.
However nothing(!) seem to work for me with DBD-ADO (Using MS SQL 7.0
MDAC 2.7 DBI 1.30 Activeperl 631):
my $dbh = newDbh();
my $sth = $dbh->prepare("select * from sysobjects");
$sth->execute();
Results in:
E:\Projekt\Helpdesk\Perl\UpgradeDB>dbitest4ado.pl
DSN: DRIVER={SQL
Server};SERVER=(local);DATABASE=Helpdesk2;NETWORK=dbmssocn;UID=sa;PWD=
OLE exception from "ADODB.Connection":
Object or provider is not capable of performing requested operation.
Win32::OLE(0.1502) error 0x800a0cb3
in METHOD/PROPERTYGET "OpenSchema" at E:/Perl/site/lib/DBD/ADO.pm
line 1402
OLE exception from "ADODB.Connection":
Error is repeated for each column in sysobjects
>
> I do recall the bind interface (also with parameter support) is a little
> tricky. A quick review of the 09bind.t tests, they look like the original
> tests I grabbed from DBD::ODBC and DBD::Oracle.
>
> I know calling a sp is database dependent ... Oracle uses begin; sp_call;
> end; Where as PostgreSQL uses select sp_call;
>
The code is executed by the server. It's when the result is interpreted
something goes wrong.
>
>
>
<snip>
>>>Finally my questions:
>>>
>>>1. Why do I get those errors?
>>>
>>>
>
> Not sure at the moment.
>
I've reinstalled using the new ppm package, didn't help. If I change
from ADO to ODBC in
$dbh = DBI->connect("DBI:ADO:$dsn")
my program works.
>
>
>
>>>2. Is DBD-ADO mature? Experiences?
>>>
>>>
>
> Limited ...
>
I've tested it because DBD:ODBC is not mature enough for me...
>
>
>
>>>3. Does DBD-ADO support input/output parameters for stored procedures?
>>>
>>>
> No or at least I doubt it.
>
>
>>>Multiple result sets?
>>>
>>>
> No.
>
>
>
>>>4. I've noticed that DBD-ADO call sp_sproc_columns for my sp's, can
>>>that be avoided?
>>>
>>>
>
>This looks like something the ADO driver is using. There isn't any
>sp_sproc_columns in the DBD::ADO code that I know of.
>
>
>
It's not an important feature. You have to be very specific to avoid the
call to sp_sproc_columns. If I use ADO command objects directly and
specify the parameters like this (Javascript):
cmdTemp = Server.CreateObject('ADODB.Command');
cmdTemp.CommandText = "dbo.my_stored_procedure";
cmdTemp.CommandType = adCmdStoredProc;
cmdTemp.Parameters.Append(cmdTemp.CreateParameter("RETURN_VALUE",
adInteger, adParamReturnValue, 4));
cmdTemp.Parameters.Append(cmdTemp.CreateParameter("@Cmd", adInteger,
adParamInput, 4, cmd));
cmdTemp.Execute();
Then ADO will know the parameters (trust my declaration) before calling
Execute and avoid the round trip to the server (sp_sproc_columns).
Since there is no way (I assume) to specify the type of command in
DBD-ADO it's not possible to do this in DBI.
/Roger P
