How do you capture SQL ODBC DUMPDATA to a logfile? 1

[ljsmith91]

I am using the Win32::ODBC module to connect to a SQL server and insert data into tables. It all works fine but I get occasional insert errors that I want to trap. I then use DUMPDATA to try and capture specific SQL insert errors and this also works fine. however, I cannot get the DUMPDATA info into my LOGFILE. I open a LOGFILE and write all pertinent messages here from the start of program but the DUMPDATA insists on going to STDOUT. I have tried many different ways to correct this but have been unsuccessful.

Here is only partial code, the important pieces. It should make sense to anyone who has done this. It is the DUMPDATA piece that I want to send to LOGFILE. This example does not work.

if (!($db=new Win32::ODBC("Driver={SQL Server};Server=$newdb;Database=$dbname;uid=$uid;pwd=$pw"))) { 

...(then later on in the code after connection successful etc.)

if ($db->Sql($sql_statement)) {
print (LOGFILE "Win32 error is \"Win32::ODBC::Error()\"\n");
my ($err) = $db->Error;
my ($dumpdata) = $db->DumpData(); 
$dumpmsg = sprintf("DUMPDATA: %150s", $dumpdata);
print (LOGFILE "\n$dumpmsg\n\n");  
} else {
print (LOGFILE "SQL Insert: Error=None\n");   $sql_success++;  } #-end if/ else

Any help would be great. Thanks so much.

 

[AHinMaine]

This is totally untested, but I'd probably give something like this a try.

use strict;
use warnings;

my $db =
  new Win32::ODBC(
          'Driver={SQL Server};Server=$newdb;Database=$dbname;uid=$uid;pwd=$pw')
  or die "Unable to connect to database";

my $sql_statement; # use your sql query

if ($db) {

    open( LOGFILE, ">yourfile" ) or die "Unable to open logfile\n";
    select(LOGFILE); # output defaults here now.

    if ( $db->Sql($sql_statement) ) {

        print "Win32 error is " 
              . Win32::ODBC::Error() 
              . "\n";

        my ($err)      = $db->Error;
        my ($dumpdata) = $db->DumpData();
        my $dumpmsg    = sprintf( "DUMPDATA: %150s", $dumpdata );

        print $dumpmsg;

    } else {
        print "SQL Insert: Error=None\n";
        exit 0;  # instead of $sql_success unless necessary
    }
}

--
Andy
"Historically speaking, the presence of wheels in Unix has never precluded their reinvention." -- Larry Wall

 

[ljsmith91]

Thank you so much...that did the trick.