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); # ...now 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

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