Adding adExecuteStream support to ADO.pm

------=_Part_6358_22227698.1142639218657
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

Attached/included is my hackish attempt to add support for the
adExecuteStream option to ADO.pm. I needed this for retrieving data
from several stored procedures that return XML and it works for my
purposes but I'm not sure if I've integrated it well into ADO.pm
(especially in the execute() subroutine.)

Enjoy and comments welcome, thanks.
Luke

Apologies if Gmail screws up formatting....

--- ADO.pm.orig=09Fri Mar 17 15:27:10 2006
+++ ADO.pm=09Fri Mar 17 15:25:01 2006
@@ -327,19 +327,20 @@

my ( $outer, $sth ) =3D DBI::_new_sth( $dbh, { Statement =3D> $state=
ment } );

- $sth->{ado_cachesize} =3D $dbh->{ado_cachesize};
- $sth->{ado_comm} =3D $comm;
- $sth->{ado_conn} =3D $conn;
- $sth->{ado_cursortype} =3D $dbh->{ado_cursortype} || $attr->{CursorT=
ype};
- $sth->{ado_fields} =3D undef;
- $sth->{ado_max_errors} =3D $dbh->{ado_max_errors};
- $sth->{ado_refresh} =3D 1;
- $sth->{ado_rownum} =3D -1;
- $sth->{ado_rows} =3D -1;
- $sth->{ado_rowset} =3D undef;
- $sth->{ado_type} =3D undef;
- $sth->{ado_usecmd} =3D undef;
- $sth->{ado_users} =3D undef;
+ $sth->{ado_cachesize} =3D $dbh->{ado_cachesize};
+ $sth->{ado_comm} =3D $comm;
+ $sth->{ado_conn} =3D $conn;
+ $sth->{ado_cursortype} =3D $dbh->{ado_cursortype} || $attr->{Curs=
orType};
+ $sth->{ado_fields} =3D undef;
+ $sth->{ado_max_errors} =3D $dbh->{ado_max_errors};
+ $sth->{ado_refresh} =3D 1;
+ $sth->{ado_rownum} =3D -1;
+ $sth->{ado_rows} =3D -1;
+ $sth->{ado_rowset} =3D undef;
+ $sth->{ado_type} =3D undef;
+ $sth->{ado_usecmd} =3D undef;
+ $sth->{ado_users} =3D undef;
+ $sth->{ado_executeoption} =3D 0;

# Set overrides for and attributes.
for my $key ( grep { /^ado_/ } keys %$attr ) {
@@ -375,6 +376,17 @@
if ( $sth->{ado_refresh} == 2 ) {
$Cnt =3D DBD::ADO::st::_refresh( $sth );
}
+=09# LRB
+=09if ( $sth->{ado_executeoption} ==
$Enums->{ExecuteOptionEnum}{adExecuteStream}) {
+ my $sResponseStream =3D Win32::OLE->new('ADODB.Stream');
+ return if DBD::ADO::Failed($dbh, "Can't create 'ADODB.Stream'");
+ $sResponseStream->Open();
+ return if DBD::ADO::Failed($dbh, "Can't open 'ADODB.Stream'");
+ my $vObj =3D Win32::OLE::Variant->new(Win32::OLE::Variant::VT_VARIAN=
T()|Win32::OLE::Variant::VT_BYREF(),
$sResponseStream);
+ return if DBD::ADO::Failed($dbh, "Can't create Variant for 'ADODB.St=
ream'");
+ $comm->{Properties}{'Output Stream'}{Value} =3D $vObj;
+ $sth->{ado_responsestream} =3D $sResponseStream;
+=09}
if ( $Cnt ) {
# Describe the Parameters:
for my $p ( Win32::OLE::in( $comm->Parameters ) ) {
@@ -1052,7 +1064,16 @@
=09|| defined $sth->{ado_users}
=09);

- if ( $UseRecordSet ) {
+ my $UseResponseStream =3D defined $sth->{ado_executeoption} &&
+ =09( $sth->{ado_executeoption} ==
$Enums->{ExecuteOptionEnum}{adExecuteStream} );
+
+ if ( $UseResponseStream ) {
+ =09$sth->trace_msg(" -- Execute: Using Response Stream\n", 5 );
+ =09$comm->Execute( { 'Options' =3D> $sth->{ado_executeoption} } );
+ =09return if DBD::ADO::Failed( $sth,"Can't Execute Command '$sql'");
+ =09return $sth->{ado_responsestream}->ReadText();
+ }
+ elsif ( $UseRecordSet ) {
=09$rs =3D Win32::OLE->new('ADODB.RecordSet');
=09return if DBD::ADO::Failed( $sth,"Can't create 'ADODB.RecordSet'"=
);

------=_Part_6358_22227698.1142639218657
Content-Type: text/plain; name=diff.txt; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Attachment-Id: f_ekx5u4xu
Content-Disposition: attachment; filename="diff.txt"

--- ADO.pm.orig Fri Mar 17 15:27:10 2006
+++ ADO.pm Fri Mar 17 15:25:01 2006
@@ -327,19 +327,20 @@

my ( $outer, $sth ) = DBI::_new_sth( $dbh, { Statement => $statement } );

- $sth->{ado_cachesize} = $dbh->{ado_cachesize};
- $sth->{ado_comm} = $comm;
- $sth->{ado_conn} = $conn;
- $sth->{ado_cursortype} = $dbh->{ado_cursortype} || $attr->{CursorType};
- $sth->{ado_fields} = undef;
- $sth->{ado_max_errors} = $dbh->{ado_max_errors};
- $sth->{ado_refresh} = 1;
- $sth->{ado_rownum} = -1;
- $sth->{ado_rows} = -1;
- $sth->{ado_rowset} = undef;
- $sth->{ado_type} = undef;
- $sth->{ado_usecmd} = undef;
- $sth->{ado_users} = undef;
+ $sth->{ado_cachesize} = $dbh->{ado_cachesize};
+ $sth->{ado_comm} = $comm;
+ $sth->{ado_conn} = $conn;
+ $sth->{ado_cursortype} = $dbh->{ado_cursortype} || $attr->{CursorType};
+ $sth->{ado_fields} = undef;
+ $sth->{ado_max_errors} = $dbh->{ado_max_errors};
+ $sth->{ado_refresh} = 1;
+ $sth->{ado_rownum} = -1;
+ $sth->{ado_rows} = -1;
+ $sth->{ado_rowset} = undef;
+ $sth->{ado_type} = undef;
+ $sth->{ado_usecmd} = undef;
+ $sth->{ado_users} = undef;
+ $sth->{ado_executeoption} = 0;

# Set overrides for and attributes.
for my $key ( grep { /^ado_/ } keys %$attr ) {
@@ -375,6 +376,17 @@
if ( $sth->{ado_refresh} == 2 ) {
$Cnt = DBD::ADO::st::_refresh( $sth );
}
+ # LRB
+ if ( $sth->{ado_executeoption} == $Enums->{ExecuteOptionEnum}{adExecuteStream}) {
+ my $sResponseStream = Win32::OLE->new('ADODB.Stream');
+ return if DBD::ADO::Failed($dbh, "Can't create 'ADODB.Stream'");
+ $sResponseStream->Open();
+ return if DBD::ADO::Failed($dbh, "Can't open 'ADODB.Stream'");
+ my $vObj = Win32::OLE::Variant->new(Win32::OLE::Variant::VT_VARIANT()|W in32::OLE::Variant::VT_BYREF(), $sResponseStream);
+ return if DBD::ADO::Failed($dbh, "Can't create Variant for 'ADODB.Stream'");
+ $comm->{Properties}{'Output Stream'}{Value} = $vObj;
+ $sth->{ado_responsestream} = $sResponseStream;
+ }
if ( $Cnt ) {
# Describe the Parameters:
for my $p ( Win32::OLE::in( $comm->Parameters ) ) {
@@ -1052,7 +1064,16 @@
|| defined $sth->{ado_users}
);

- if ( $UseRecordSet ) {
+ my $UseResponseStream = defined $sth->{ado_executeoption} &&
+ ( $sth->{ado_executeoption} == $Enums->{ExecuteOptionEnum}{adExecuteStream} );
+
+ if ( $UseResponseStream ) {
+ $sth->trace_msg(" -- Execute: Using Response Stream\n", 5 );
+ $comm->Execute( { 'Options' => $sth->{ado_executeoption} } );
+ return if DBD::ADO::Failed( $sth,"Can't Execute Command '$sql'");
+ return $sth->{ado_responsestream}->ReadText();
+ }
+ elsif ( $UseRecordSet ) {
$rs = Win32::OLE->new('ADODB.RecordSet');
return if DBD::ADO::Failed( $sth,"Can't create 'ADODB.RecordSet'");


------=_Part_6358_22227698.1142639218657--

Re: Adding adExecuteStream support to ADO.pm

Luke Bakken wrote:

> Hi all,
>
> Attached/included is my hackish attempt to add support for the
> adExecuteStream option to ADO.pm. I needed this for retrieving data
> from several stored procedures that return XML and it works for my
> purposes but I'm not sure if I've integrated it well into ADO.pm
> (especially in the execute() subroutine.)

Thanks, I applied your patch. Sure, integration into execute() is
suboptimal. Normally, data retrieval is achieved via fetch() and/or
blob_read(). But I have no experience in using ADO Streams.
Further comments are welcome.


Steffen

Re: Adding adExecuteStream support to ADO.pm

> > Hi all,
> >
> > Attached/included is my hackish attempt to add support for the
> > adExecuteStream option to ADO.pm. I needed this for retrieving data
> > from several stored procedures that return XML and it works for my
> > purposes but I'm not sure if I've integrated it well into ADO.pm
> > (especially in the execute() subroutine.)
>
> Thanks, I applied your patch. Sure, integration into execute() is
> suboptimal. Normally, data retrieval is achieved via fetch() and/or
> blob_read(). But I have no experience in using ADO Streams.
> Further comments are welcome.

Thanks!

I'll look into execute more() - this is my first foray into DBI
programming at all. Perhaps the stream output should be treated as a
single row, single column return set?

Luke

Re: Adding adExecuteStream support to ADO.pm

Luke Bakken wrote:

> I'll look into execute more() - this is my first foray into DBI
> programming at all. Perhaps the stream output should be treated as a
> single row, single column return set?

Perhaps. Or, because your data are returned from stored procedures,
it's time to implement bind_param_inout():



Can you provide a small, self-contained use case? Are stored
procedures the only way to select your data?


Steffen