Re: Error during SQL Server restore fails to propagate back to DBI client on Windows

On 22/02/2010 19:27, pr wrote:
<huge amount snipped as I cannot keep track of this so I assume no one 
else can and I suspect we are getting close to some maximum post size>

>> Can I just wind back a bit as each time you email the
>> number of things
>> you seem unhappy with seems to grow.
>>
>> Starting with some facts (as I believe they to be):
>>
>> HandleError is only called for errors not warnings or
>> informational
>> messages. See the text "The HandleError attribute can be
>> used to provide
>> your own alternative behaviour in case of errors" in the
>> DBI docs.
>>
>> HandleSetError is called whenever the err, errstr or state
>> variables are
>> being set - this includes warnings and informational
>> messages. See the text
>>
>> "The HandleSetErr attribute can be used to intercept the
>> setting of
>> handle err, errstr, and state values." and
>>
>> "The HandleSetErr and HandleError subroutines differ in
>> subtle but
>> significant ways. HandleError is only invoked at the point
>> where the DBI
>> is about to return to the application with err set true.
>> It's not
>> invoked by the failure of a method that's been called by
>> another DBI
>> method. HandleSetErr, on the other hand, is called whenever
>> set_err() is
>> called with a defined err value, even if false. So it's not
>> just for
>> errors, despite the name, but also warn and info states."
>>
>> in the DBI docs.
>>
>> DBD::ODBC like many other DBDs supports (to the degree DBI
>> allows) the
>> reporting of errors, warnings and informational messages.
>> If you call
>> err you can find errors, warnings or informationals as
>> described in the
>> docs - "A driver may return 0 from err() to indicate a
>> warning condition
>> after a method call. Similarly, a driver may return an
>> empty string to
>> indicate a 'success with information' condition. In both
>> these cases the
>> value is false but not undef." As an example:
>>
>> use DBI;
>> use strict;
>> use warnings;
>>
>> my $h = DBI->connect;
>> print "last informational message was: ", $h->errstr,
>> "\n\n"
>>      if $h->err eq "";
>>
>> produces:
>>
>> last informational message was: [unixODBC][Easysoft][SQL
>> Server
>> Driver][SQL Server]Changed language setting to us_english.
>> (SQL-01000)
>> [unixODBC][Easysoft][SQL Server Driver][SQL Server]Changed
>> database
>> context to 'master'. (SQL-01000)
>>
>> in my setup.
>>
>> To my knowledge DBI does not allow multiple informational
>> messages to be
>> stacked i.e., there is only one err, errstr, state and so
>> even if
>> DBD::ODBC calls set_err multiple times you will only see
>> the last
>> informational in err, errstr and state (this is one aspect
>> of this I'm
>> not totally certain about and the above example might
>> illustrate why I
>> could be wrong here). You can catch these informationals
>> with
>> HandleSetError but in the case of DBD::ODBC calling
>> SQLMoreResults
>> automatically, it does not report each informational - I
>> can change that
>> specifically if you like and if you report it on rt.
>>
>> Now some history and operational notes on DBD::ODBC:
>>
>> SQL Server is rather strange when it comes to the calling
>> of procedures.
>> Firstly it does not batch result-sets up and hence
>> odbc_more_results
>> i.e., if you call select 1; select 2; etc in your procedure
>> you have to
>> call odbc_more_results (SQLMoreResults in the ODBC API) to
>> move to the
>> next result-set. Similarly if you call insert; insert; you
>> need to call
>> SQLMoreResults. However, because people using DBD::ODBC
>> said it was a
>> PITA the call odbc_more_results for each insert (as all
>> they'd find out
>> is the RowCount and nothing else unless an error occurs)
>> DBD::ODBC skips
>> non-result-set generating statements automatically i.e., if
>> you do:
>>
>> insert
>> insert
>> select
>> select
>>
>> the first 2 are skipped and the result from the first
>> select is
>> available then you call odbc_more_results to get the next
>> result-set.
>>
>> Also, the SQL Server ODBC driver works differently when
>> calling
>> prepare/execute from calling SQLExcDirect e.g., you can
>> only use
>> temporary tables when using SQLExecDirect. Initially, this
>> was the
>> reason for adding odbc_exec_direct. As it turns out there
>> are other
>> scenarios where odbc_exec_direct is worth using and you
>> have hit one of
>> them. As far as I am aware you need to set odbc_exec_direct
>> to call your
>> procedures reliably - it is something to do with the
>> done_in_proc states
>> returned by TDS.
>>
>> Also, you should note that because SQL Server does not
>> batch statements
>> up in a procedure the output bound parameters are not
>> available until
>> AFTER the procedure is completed and it has not completed
>> until
>> SQLMoreResults returns SQL_NO_DATA - this is the second
>> reason for
>> odbc_more_results.
>>
>> DBD::ODBC does not call SQLMoreResults if you use the do
>> method since do
>> should not be used for result-set generating statements.
>>
>> Now back to what I believe are the issues you have brought
>> up:
>>
>> o you need to use prepare/execute and set odbc_exec_direct
>> on the
>> prepare call.
>>
>> o you do not need to use odbc_more_results in this case if
>> you use
>> prepare/execute since DBD::ODBC will call SQLMoreResults
>> for you
>> internally. N.B. odbc_more_results does not cause
>> SQLMoreResults to be
>> called it simply tells you if when it was called it said
>> there were more
>> results.
>>
>> I am now unsure if you are still saying your restore does
>> not work but
>> the following code works for me:
>>
>> use DBI;
>> use strict;
>> use warnings;
>> use Data::Dumper;
>>
>> sub _error_handler {
>>      print "ERROR HANDLER ", Dumper(\@_);
>>      0;
>> }
>>
>> sub _seterr_handler {
>>        print "SETERR HANDLER ",
>> Dumper(\@_);
>>        0;
>> }
>>
>> my $h = DBI->connect;
>> print "Connection SUCCESS_WITH_INFO data", $h->errstr,
>> "\n\n"
>>      if $h->err eq "";
>>
>> $h->{RaiseError} = 1;
>> $h->{PrintError} = 1;
>> $h->{PrintWarn} = 1;
>> $h->{HandleError} = \&_error_handler;
>> $h->{HandleSetErr} = \&_seterr_handler;
>>
>> #eval {$h->do('create database foo');};
>>
>> my $s = $h->prepare(q{backup database foo to
>> disk='c:\foo.one'}
>> ,{odbc_exec_direct =>  1}
>> );
>> $s->execute;
>> print "errstr: ", $s->errstr, "\n" if $s->err eq "";
>>
>> print "backed up\n";
>> #$h->do(q{backup database foo to disk='c:\foo.bak'});
>>
>> $h->disconnect;
>>
>> print "Restoring\n";
>> $h = DBI->connect;
>> print "Connection SUCCESS_WITH_INFO data", $h->errstr,
>> "\n\n"
>>      if $h->err eq "";
>>
>> $h->{RaiseError} = 1;
>> $h->{PrintError} = 1;
>> $h->{PrintWarn} = 1;
>> $h->{HandleError} = \&_error_handler;
>>
>> $s = $h->prepare(q{restore database foo from
>> disk='c:\foo.one'}
>> ,{odbc_exec_direct =>  1});
>> $s->execute;
>> print "errstr: ", $s->errstr, "\n" if $s->err eq "";
>>
>> and produces:
>>
>> C:\perlbuild_xxx>perl backup_restore.pl
>> Connection SUCCESS_WITH_INFO data[Microsoft][ODBC SQL
>> Server Driver][SQL
>> Server]
>> Changed database context to 'master'. (SQL-01000)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
>> language setting
>> to us_english. (SQL-01000)
>>
>> SETERR HANDLER $VAR1 = [
>>            bless( {}, 'DBI::st' ),
>>            '',
>>            '[Microsoft][ODBC SQL
>> Server Driver][SQL Server]Processed 176
>> pages for database \'foo\', file \'foo\' on file 1.
>> (SQL-01000)',
>>            '01000',
>>            undef
>>          ];
>> errstr: [Microsoft][ODBC SQL Server Driver][SQL
>> Server]Processed 176
>> pages for database 'foo', file 'foo' on file 1.
>> (SQL-01000)
>> backed up
>> Restoring
>> Connection SUCCESS_WITH_INFO data[Microsoft][ODBC SQL
>> Server Driver][SQL
>> Server]
>> Changed database context to 'master'. (SQL-01000)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
>> language setting
>> to us_english. (SQL-01000)
>>
>> errstr: [Microsoft][ODBC SQL Server Driver][SQL
>> Server]Processed 176
>> pages for database 'foo', file 'foo' on file 1.
>> (SQL-01000)
>>
>> If I look at the ODBC trace I see it actually gets the
>> following
>> informationals:
>>
>> 01000 Processed 176 pages for database 'foo' file foo
>> 01000 Processed 1 pages for database foo, file foo_log
>> 01000 BACKUP DATABASE successfully processed 177 pages in
>> 0.735 seconds
>> (1972 MS/sec)
>>
>> Now if all you want is to capture all those informationals
>> in
>> HandleSetError then report it on rt and I'll try and change
>> it for you.
>>
>> Martin
>> -- 
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>      
>
>
> I apologize if it seems my point is wandering or if I am genuinely unhappy.  That's not my intention, to be sure.
>
> Yes, it does work for me when {odbc_exec_direct} is set to true.  I also understand your explanation of why this is the correct way to do things.
>
>    
You need to set odbc_exec_direct - sorry if this prove inconvenient for 
you but there is no other way for DBD::ODBC to know you need to do this.

> The real concern here is why error 3224 seems untrappable if this restore is done with {odbc_exec_direct} is set to false.  I cannot see how to trap this error at all.
>
>    
I don't think there is a way to do this. I replicated your situation in 
C via ODBC and get the same issue. You could perhaps talk to Microsoft 
but I doubt it will get you anywhere.

> Is it possible to (incorrectly) set odbc_exec_direct to false, issue the database restore, and trap error 3224 in perl?
>
>    
I don't believe so via Perl and DBD::ODBC or even directly via the ODBC API.

> Plausible use cases for why this is a concern: 1) A person wants to use prepared statements as much as possible; it's not clear without extensive server side tracing which may be subject to this problem.   2) Add to case #1 a large shop where a number of people may be writing code.  It would be best to have a sure way to trap any possible server-side error, even if they did something incorrect such as setting odbc_exec_direct to false for a database restore.
>
> Last, thanks for the suggestion about filing an rt ticket re: capturing all informational messages.  I think I will take you up on this, and I'll try and make a case for the value of this in the rt ticket.
>
>    
To be honest, I've already made the changes in DBD::ODBC and if you mail 
me privately I'll send you a new distribution.

> Thank you once again for your time on this.  I do really appreciate it.
>
> Regards,
> -rt
>    

In fairness, perhaps due to my inability to follow where you were going 
I've taken a bit too long to get to the gist of your issues and it has 
arguably highlighted an issue in SQLMoreResults not calling set_err in 
DBI. I always try to help although not always successfully.

Martin
--
Martin J. Evans

Easysoft Limited
http://www.easysoft.com