ADO, multiple statements and Perl

l3nz has asked for the wisdom of the Perl Monks concerning the following question:

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:

Logging all query activity in a single flat file, with errors if any
Logging of query running times
Single-line query execution (Sql in, resultset out)
Select any recordset in a multi-statement query
Optional full-text logging of returned resultset

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;
};
[download]

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

The first SQL resultset is #0, the second is #1, and so on
Any SQL statement will return a resultset, maybe empty
DECLARE statements will not count as statements.

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

Back to Seekers of Perl Wisdom