Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

ADO, multiple statements and Perl

by l3nz (Friar)
on Nov 24, 2003 at 12:53 UTC ( #309515=perlquestion: print w/replies, xml ) Need Help??

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); # 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:

  • 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

Replies are listed 'Best First'.
Re: ADO, multiple statements and Perl
by Anonymous Monk on Nov 24, 2003 at 13:18 UTC
      Funny! they use the TLB instead of tablulating the constants used. Definitely nice! I think I'll have to learn to do that.... :-)
Re: ADO, multiple statements and Perl
by mpeppler (Vicar) on Nov 24, 2003 at 16:00 UTC
    (any other comment or criticism on the code is warmly welcome)
    I'll point out a SQL bug in your code:
    my $sql = << "__SQL__"; declare \@AZIONE int; set \@AZIONE = (SELECT id FROM desAz WHERE az LIKE 'XXXX%' ); select * from desCc where id = \@AZIONE; __SQL__
    It is possible that SELECT id FROM desAz WHERE az like '...' could return more than one row, @AZIONE will get set to the last value returned. That's one of the reasons why using a JOIN here would be a lot better.


      Yes, I know; in fact the problem does not arise for the very data that area in the "original" query that I used when developing. In the case it should, it means that such data is corrupted and I want everything to abend and be notified when looking at the logs. Anyway thanks.
Re: ADO, multiple statements and Perl
by xenchu (Friar) on Nov 24, 2003 at 14:40 UTC

    Thank you for the information. It looks as if it might be valuable to know if you program on Windows.

    However, I think you should have submitted it as a Perl Meditation instead of a question since, after all, you are offering information not asking for it. The discussion could be beneficial.


    On the Internet no one knows you're a dog. -woof woof
      You are probably right. Next time I promise I'll split the thing into two separare articles - one for the meditations and another (with the question) for the "Seekers". Thank you anyway.
Re: ADO, multiple statements and Perl
by NetWallah (Canon) on Nov 24, 2003 at 17:39 UTC

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://309515]
Approved by broquaint
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2022-01-26 21:45 GMT
Find Nodes?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:

    Results (70 votes). Check out past polls.