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
