Calling all Perl junkies!

Question

Post reply

Calling all Perl junkies!

Jeremy Brown

SSCarpal Tunnel

Points: 4223
More actions
December 19, 2002 at 2:24 pm

#159310
I have the dubious task of trying to capture the error description from a SQL statement passed through ADO.
Here's the gory details...
```
use Win32::OLE; 

$db = Win32::OLE->new("ADODB.Connection");

$dbConnString = "PROVIDER=SQLOLEDB;SERVER=STUFFSERVER;Initial Catalog=STUFFDB;Integrated Security=SSPI;";

$db->Open($dbConnString);

$SQL = "select stuff from stufftable";

$rst=$db->Execute($SQL);

while ( !$rst->EOF ) {

$val = $rst->Fields("stuff")->Value();

print "$val\n";

$rst->MoveNext();

}

$rst->Close()

close ($dbConnString);
```
I would like to trap any errors that happen when the SQL string is executed, but all attempts have failed. I have tried to use ADODB.Error and have managed to die the script when an error occurs, but am having bunches of problems getting the actual error text back.
I feel so dumb all of a sudden... :-P
Edited by - void on 12/19/2002 2:27:27 PM
December 23, 2002 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

A bit more about the problem...

Perl's Win32::OLE

doesn't seem to handle the ADODB.Errors object well. We've been trying to figure

out how to do the equivalent of:

For Each oError in oRst.ActiveConnection.Errors

Print oError.Description

a hash (like a collection but not quite) but isn't recognizing Item (aka

each individual ADODB.Error object that's part of the collection). For

instance, I could do oRst.ActiveConnection.Errors.Item(0) in VBScript, but

Perl isn't having any such nonsense, for whatever reason. As a result,

it's not pulling the ADODB.Error object from the collection. I told Void

it would just be simpler to write the whole thing in VBScript or JScript,

but he's not budging. 😉

Another solution would be to just break down and use DBI but that's not as

maintainable by others should Void and I move on.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

All right, the catch is you can't use foreach like you'd do FOR EACH in VBScript. You have to get each item individually, and of course, the array index starts at 0. Sample code:

use Win32::OLE;

my $connection;

my $connectionString;

my $SQL;

my $recordset;

my $Errors;

my $error;

my $count;

## Open Database Connection

$connectionString = "PROVIDER=SQLOLEDB.1;SERVER=SQLServer;Integrated Security=SSPI;Initial Catalog=Northwind;";

$connection = Win32::OLE->new("ADODB.Connection");

$connection->Open($connectionString);

## Get recordset

$recordset = Win32::OLE->new("ADODB.Recordset");

## This query should fail to execute as the table is Customers, not Customer

## So we should have the Errors object populated

$SQL = "SELECT * FROM Customer";

$recordset->Open($SQL, $connection);

## Return any Errors

$Errors = $recordset->ActiveConnection()->Errors();

$count = $Errors->Count;

print "Number of Errors: " . $count . "\n";

## Loop through the errors

for (my $i=0; $i < $count; $i++)

{

## Get error object

my $currentError;

$currentError = $Errors->Item($i);

## Return error information

print "Error from " . $currentError->Source . ":\n\tError # " . 

($currentError->Number & 0xFFFF) . ", " . $currentError->Description . "\n";

}

## release memory from Errors and recordset object

$Errors = undef;

$recordset = undef;

## close database connection and release memory

$connection->Close();

$connection = undef;

What's returned:

Number of Errors: 1

Error from Microsoft OLE DB Provider for SQL Server:

        Error # 3639, Invalid object name 'Customer'.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

Edited by - bkelley on 12/23/2002 7:14:31 PM

K. Brian Kelley
@kbriankelley

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 3

Wow. This is great Brian!

Now I should be able to write very well formed Perl code and trap the error properly from SQL Server.

Well done!

-Jeremy

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 4

Brian,

Just curious whether Win32::OLE's "in" construct would work on the $recordset->ActiveConnection()->Errors() collection, or not since its not really a collection. For example, does this work?

foreach my $currentError (in $recordset->ActiveConnection()->Errors()->Item) {

print "Error from " . $currentError->Source . ":\n\tError # " .

($currentError->Number & 0xFFFF) . ", " . $currentError->Description . "\n";

}

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

No. Because of the way the Errors object is built, it's not recognized as an Enum object. The error returned when you try to implement in this fashion (even if you leave off ->Item() since that refers to a specific object and not the collection) is:

Win32::OLE(0.1502): GetOleEnumObject() Not a Win32::OLE::Enum object at <path to .pm file> line 166.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

chafen SSC Veteran Points: 247 More actions · Answer 6

The errors collection work fine for me. Here is something I cobbled together from a larger program.

Cliff

use strict;

use Win32::OLE::Const ('Microsoft ActiveX Data Objects');

use Win32::OLE;

my( $rc, $conn, $rs, $err);

my $connstr = "Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind;";

my $sql = "select * from customer";

($rc, $conn) = db_open( $connstr, 'Main');

exit $rc if $rc;

$rs = $conn->Execute( $sql, , adCmdText);

($rc, $err) = ADO_OLE_errors( \$conn, \$rs);

if( $rc ) {

log_message( "Execute failed $sql");

log_message( $err) if $err;

exit 1;

}

log_message( "Execute worked");

exit 0;

#------------------------------------------------------------------------------

#

#db_open - Create ADO connection object and open based on a udl file.

#

sub db_open {

my( $connstr, $loc) = @_;

my( $conn, $err, $rc);

$conn = Win32::OLE->new( "ADODB.Connection");

if( not defined $conn) {

$err = Win32::OLE->LastError();

log_message( "Error creating ADODB.Connection in $loc", $err);

return 1;

}

$conn->Open( $connstr);

($rc, $err) = ADO_OLE_errors( \$conn) unless $conn->{State} == adStateOpen;

if( $rc ) {

log_message( "Failed to open $connstr in $loc");

log_message( $err) if $err;

return 1;

}

return 0, $conn;

}

#------------------------------------------------------------------------------

#

#The first arg is the ADO connection object ref that owns the Errors collection.

#The second arg is not required. If present, and if not defined, the OLE

#LastError method is called and returns 1 before using the 1st arg. If present

#and defined, LastError is checked, then Errors collection object; return 0 or 2.

#

# $rc = ADO_OLE_errors( \$conn, \$obj)

# $rc = ADO_OLE_errors( \$conn)

# $rc = ADO_OLE_errors( undef, \$obj)

#

#Return 0 if all OK.

#Return 1 if $objp is present and not defined.

#Return 2 if $opjp is defined but there are errors in the ${$connp} Errors

#collection (and possibly LastError). Clear the Errors collection.

#

sub ADO_OLE_errors {

my( $connp, $objp) = @_;

my( $rc) = 0;

my( $err) = '';

if( scalar(@_) == 2) {

$err = Win32::OLE->LastError();

if( not defined ${$objp}) {

$err = "OLE object not defined, but no LastError" unless $err;

$err .= "\n" if $err;

return 1, $err;

} elsif( $err ) {

$err .= "\n";

$rc = 2;

} else {}

}

if( ${$connp}->Errors->{Count}) {

foreach my $error (Win32::OLE::in( ${$connp}->Errors)) {

next unless $error->{Number};# ignore warnings.

foreach my $property (keys %{$error}) {

$err .= $property . ": ";

$err .= $error->{$property} . "\n"; # works

}

${$connp}->Clear;

$rc = 2;

}

return $rc, $err;

}

#------------------------------------------------------------------------------

#

sub log_message {

my( $msg, $opt) = @_;

$msg = $msg . ($opt ? ': ' . $opt : '');

print $msg, "\n";

}

It produces:

Execute failed select * from customer

OLE exception from "Microsoft OLE DB Provider for SQL Server":

Invalid object name 'customer'.

Win32::OLE(0.1501) error 0x80040e37

in METHOD/PROPERTYGET "Execute"

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

I'm following what you are doing. We've used properties for the ConnectionString, didn't consider it for the Error object, that's neat. I'm let Void now there's another solution on the table because I think he's still working on his scripts.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 8

Excellent! Thanks chafen!

This definately gets me pointed in the right direction.

Edited by - void on 01/03/2003 09:27:00 AM