Valued brothers,
as some of you may recall, I happened to have a problem with accessing multiple-statement queries using ADO under Win32::OLE. I have succeeded in working around the problem thanks to this article on MSDN and have come up with this simple code that could be of benefit to somebody else.

Its most important features are:

Here is the code:

use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; my $Conn = Win32::OLE->new('ADODB.Connection'); # creates a connection + object my $RS = Win32::OLE->new('ADODB.Recordset'); # creates a recordset +object $Conn->Open( "DSN=myDsn;UID=myUid;PWD=myPwd" ); $Conn->{'CommandTimeout'} = 1200000; # no timeout $Conn->{'CursorLocation'} = 3; # adUseClient: counts rows on the clie +nt my $sql = << "__SQL__"; declare \@AZIONE int; set \@AZIONE = (SELECT id FROM desAz WHERE az LIKE 'XXXX%' ); select * from desCc; __SQL__ my $RS = execQuery( $Conn, $sql, "This is my query", 1, 1); # you can do things with your recorset in $RS.... $RS->Close; $Conn->Close; exit; # ----------------------------------------- # This function queries the database # and logs query, timing, results # and errors. # Support multiple statements. sub execQuery( ) { my ($Conn, $sql, $desc, $whichRS, $dumpRS ) = @_; logEntry(); my $est_err = 0; lg( "Query: $desc\n\n$sql\n\n" ); my $log_start = time; my $RS = $Conn->execute( $sql ); my $durata = time - $log_start; lg( "Time: $durata sec\n" ); my $errors = $Conn->Errors(); foreach my $error (in $errors) { $est_err = 1; lg( "Error: [" . $error->{Number} . "] " . $error->{Descript +ion} ); }; $errors->Clear; if ( $whichRS > 0 ) { lg( "Using recordset # $whichRS" ); my $RS2; for ( my $rr = 0 ; $rr < $whichRS; $rr++ ) { $RS2 = $RS->NextRecordset; } $RS = $RS2; } my $nRighe = $RS->RecordCount * 1.0; if ( $nRighe > 0 ) { $RS->MoveFirst; }; # I dump the recordset if needed if ( $dumpRS && ($nRighe > 0) ) { my $nColonne = $RS->Fields->Count; for (my $r = 0; $r < $nRighe; $r++ ) { my @Riga; for( my $c = 0; $c < $nColonne; $c++ ) { push @Riga, $RS-> +Fields($c)->Value; }; lg( ">[$r] " . join( "|", @Riga ) ); $RS->MoveNext; } $RS->MoveFirst; } lg( "N. returned rows: " . $nRighe ); return $RS; } # This logs all activity on a file sub lg() { my ($s) = @_; open F, ">>logfile.txt" or die "$! logfile.txt"; print F $s . "\n"; close F; };

Please note how you may access any resultset (setting the fourth parameter of execQuery()), keeping in mind this rule:

Now I have a question for you: is there a standard way to access ADO constants via Perl? As you can see, I looked up the constants's value, but I don't like it very much. Is there a package like ADO::Constants that will let me access such constants using a symbolic name?

(any other comment or criticism on the code is warmly welcome)

update (broquaint): added <readmore> tags

In reply to ADO, multiple statements and Perl by l3nz

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.