Win32::OLE, ADODB.Stream and ADODB.Command problem.

[Luke Bakken]

Hello all,

I'm trying to use Win32::OLE (version 0.1703) and the ADODB objects to
retrieve XML being produced by a stored procedure on SQL server. I
used the following code as a guide:

http://msdn.microsoft.com/library/en-us/dnsqlmag2k/html/adoxmlListing_02.txt

If I create a VB project using the above code as a reference I can get
the data out of the server without any problems. I can also retrieve
the data with the "osql" program. However, the following perl code
gives me this error:

Error is
----------
OLE exception from "ADODB.Command":

Object or provider is not capable of performing requested operation.

Win32::OLE(0.1703) error 0x800a0cb3
    in METHOD/PROPERTYGET "Execute"
----------
in file "Z:\XXI\Core\Install\SQL\DBcreate\test_xml.plx", package "main", line 36

---------------------------------------------------------------------------------------------------------------
use strict;
use Win32::OLE qw/in with/;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.8 Library';

++$|;

my $connStr = q{Provider=SQLOLEDB;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=Globalfs;Data
Source=GFSDBmaster;Use Procedure for Prepare=0;Connect
Timeout=4;Trusted_Connection=Yes};
my $cnConn = Win32::OLE->new('ADODB.Connection');
$cnConn->Open($connStr);
chkW32LastErr();
my $cmCmd = Win32::OLE->new('ADODB.Command');

with($cmCmd,
        'ActiveConnection' => $cnConn,
        'CommandType' => adCmdStoredProc,
        'CommandText' => 'dbo.report_a_table_with_XML'
);
$cmCmd->Parameters->Append($cmCmd->CreateParameter('@table_name',
adVarChar, adParamInput, 64, 'Branch'));
$cmCmd->Parameters->Append($cmCmd->CreateParameter('@show_check_constraints',
adTinyInt, adParamInput, 1, 1));
$cmCmd->Parameters->Append($cmCmd->CreateParameter('@style_sheet',
adVarChar, adParamInput, 64, 'TableDump.xsl'));

for my $param (in $cmCmd->Parameters) {
        print "Param: ", $param->Name, " Value: ", $param->Value, "\n";
}

# Create a stream to handle the response
my $sResponseStream = Win32::OLE->new('ADODB.Stream');
$sResponseStream->Open();
$cmCmd->{'Properties'}->{'Output stream'}->{'Value'} = $sResponseStream;
$cmCmd->Execute({ 'Options' => adExecuteStream });
chkW32LastErr();
print $sResponseStream->ReadText();

sub chkW32LastErr
{
        my $warnonly = shift;
        my $lastError = Win32::OLE->LastError();
        if ($lastError)
        {
                my ($package, $filename, $line) = caller();
                unless ($warnonly) {
                        die qq{Error is\n----------\n$lastError\n----------\nin 
file
"$filename", package "$package", line $line\n};
                } else {
                        print STDERR qq{Warning 
is\n----------\n$lastError\n----------\nin
file "$filename", package "$package", line $line\n};
                }
        }
        return $lastError;
}
---------------------------------------------------------------------------------------------------------------

I believe the following line is not assigning the $sResponseStream
object to the "Output stream" property:

$cmCmd->{'Properties'}->{'Output stream'}->{'Value'} = $sResponseStream;

If I check the value of the "Output stream" property after the
assignment with Win32::OLE->QueryObjectType() it indicates that it is
not a valid Win32::OLE object.

Am I doing the assignment incorrectly? I can't imagine this being a
limitation of Win32::OLE.

Thanks,
Luke

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>