RE: SQL errors not making it back to Perl

[Roode, Eric]

On Friday, September 10, 2010 11:47 AM, Martin J. Evans wrote:

> You don't look like you did anything but connect and disconnect. You
> are running it on the script you posted originally aren't you?
>
> Can you try again. Remove the use DBD::ODBC and connect flags I
> asked you to add and simply do the set DBI_TRACE=15=x.x.
>
> Search the log for the SQL you issue - if you don't see some of it
> the tracing is still not right.

I did run it against the original script!  Here it is once again:

   #----------------------------------------------------------------
   use Modern::Perl;
   use DBI;
   use Data::Dump qw(dump);
   
   sub handle_error
   {
       my ($state, $msg, $native) = @_;
       say qq{handle_error: \$state  is "$state".};
       say qq{handle_error: \$msg    is "$msg".};
       say qq{handle_error: \$native is "$native".};
       return 1;
   }
   
   my $dbh = DBI->connect('DBI:ODBC:driver={SQL
Server};Server=xxxxx;Database=xxxxx',
                          'xxxxx', 'xxxxx',
                          {odbc_cursortype        => 2,
                           odbc_default_bind_type => DBI::SQL_VARCHAR,
                           odbc_err_handler       => \&handle_error,
                           RaiseError             => 1,
                           PrintError             => 0,
                          });
   # Enable tracing
 
DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'));
   
   my $sth = $dbh->prepare('{? = call error_test(?, ?)}');
   
   # Set up parameters
   my $retval;
   my $p1 = 'Some input parameter';
   my $p2 = 75;
   $sth->bind_param_inout(1, \$retval, 4000, DBI::SQL_VARCHAR);
   $sth->bind_param      (2,  $p1,           DBI::SQL_VARCHAR);
   $sth->bind_param_inout(3, \$p2,       32, DBI::SQL_INTEGER);
   
   $sth->execute;
   
   $_ = defined($_)? qq{"$_"} : '{NULL}' for ($retval, $p1, $p2);
   say qq{After execute: \$retval is $retval.};
   say qq{After execute: \$p1     is $p1.};
   say qq{After execute: \$p2     is $p2.};
   say 'Done.';
   #----------------------------------------------------------------

Then I set DBI_TRACE=15=dt_trace.out in the cmd.exe shell and ran it.
Most puzzling.  Output was:

   After execute: $retval is {NULL}.
   After execute: $p1     is "Some input parameter".
   After execute: $p2     is "75".
   Done.


-- Eric