On Wed, Jul 03, 2002 at 10:14:10PM +0100, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Wed, Jul 03, 2002 at 11:34:17AM -0700, David L. Good wrote:
> > On Wed, Jul 03, 2002 at 09:43:53AM -0700, Michael Peppler <[EMAIL PROTECTED]>
>wrote:
> > > On Wed, 2002-07-03 at 09:28, Accardo, Glen wrote:
> > > >
> > > > I just tried to prepare/execute/fetch results for the following
> > > > a dbcc call in MS SQL Server. I get an error stating that
> > > > there is no select statement running--which is true.
> > > >
> > > > Is there a mechanism for getting the results of DBCC calls through
> > > > DBI? The do method doesn't seem to return anything more than
> > > > pass/fail, and what I need is all of the output from the
> > > > DBCC calls.
> > >
> > > DBCC output is sent as "error" messages. DBD::Sybase handles this by
> > > letting you define an ad-hoc error handler. Maybe DBD::ODBC has the same
> > > sort of functionality.
> >
> > Nope, DBD::ODBC doesn't have that functionality. I submitted a patch a
> > couple of years ago to address the issue, but it kinda got lost in the
> > shuffle and I moved on to other projects without following up on it. I
> > hope to re-integrate the patch into the latest DBD::ODBC soon, but I'm
> > still trying to get access to an appropriate Windows box...
>
> Please discuss on dbi-dev first as I'd like to consider defining a
> standard API for that kind of functionality.
OK. I don't think there'll be much of a problem though, since I swiped a lot
of the code for my patch from DBD::Sybase and consciously tried to make it
as similar as possible.
However, since ODBC works quite a bit differently than the native Sybase
libraries, I also needed to add another attribute to turn on asynchronous
execution so the server messages could be gathered while the query (or
in this case the DBCC) is still running. Of course, you only need to do
that if you want to get the messages before the query finishes executing.
In my case, I wanted to be able to check the DBCC output and send an
e-mail notification (possibly to a pager) if a problem was found.
On large databases, using the async execution could mean getting a
notification of a problem a couple of hours sooner. Unfortunately, it
seems that ODBC buffers the messages, so you only get them in chunks.
If an error you're interested in occurs just before a large table is
DBCC'd, you may still have to wait awhile before you'll get the message.
For more details, below is a conversation I had with myself way back when
I first submitted the patch (nobody else seemed interested in discussing
it at the time). I've deleted the actual patch since it almost certainly
won't work any more on the more recent DBD::ODBC code. Probably way more
information than anyone needs...
> On Fri, Apr 28, 2000 at 10:16:58AM -0700, David Good <[EMAIL PROTECTED]> wrote:
> > On Fri, Apr 21, 2000 at 05:32:53PM -0700, David Good <[EMAIL PROTECTED]>
>wrote:
> > > I'm using DBD::ODBC to connect to Sybase and MS-SQL Server databases
> > > (among others) and I was wondering if there is an easy way to get
> > > server messages for things like DBCCs. In dblib and ct-lib you'd set
> > > up a message handler for this. Indeed, in DBD::Sybase you can set
> > > $dbh->{syb_err_handler} to a coderef to handle it. Looking through the
> > > ODBC API, I don't see anything really, really obvious, but I haven't
> > > gotten really in-depth yet. Anyone have any ideas?
> >
> > To answer my own question, it turns out that the server messages are
> > all there and readily available, they just aren't retrieved unless the
> > SQLExecute returns an error status. I think the most useful method
> > for retrieving these is through some sort of callback method similar
> > to DBD::Sybase's $dbh->{syb_err_handler}. Unfortunately, there's no
> > way to retrieve the messages while execution is in progress unless
> > SQLExecute is run in asynchronous mode. Now, async mode can be set
> > on a per-statement or per-connection basis depending on the data source
> > (i.e. some type of data sources can do per-statement async and some can do
> > per-connection async). I suppose the best way to go is to define another
> > attribute odbc_async_execute or somesuch and if set, set the async mode by
> > whichever means is available. If anyone has any ideas or suggestions let
> > me know as it will probably be a few days before I get started on this...
>
>
> I've finally finished this. Here's the details (with the patch following):
>
> I've implemented two separate functions. The first is an "error
> handler" similar to that in DBD::Sybase. The error handler can be used
> to intercept error and status messages from the server. It is the only
> way (at least currently) that you can retrieve non-error status messages
> when execution is successful.
>
> To use the error handler, set the "odbc_err_handler" attribute on
> your database handle to a reference to a subroutine that will act
> as the error handler. This subroutine will be passed two args, the
> SQLSTATE and the error message. If the subroutine returns 0, the
> error message will be otherwise ignored. If it returns non-zero,
> the error message will be processed normally.
>
> The second function implemented is asynchronous execution. It's only
> useful for retrieving server messages with an error handler during an
> execute() that takes a long time (such as a DBCC on a large database) ODBC
> doesn't have the concept of a callback routine like Sybase's DBlib/CTlib
> does, so asynchronous execution is needed to be able to get the server
> messages before the SQL statement is done processing.
>
> To use asynchronous execution, set the "odbc_async_exec" attribute on
> your database handle to 1. Not all ODBC drivers support asynchronous
> execution. To see if yours does, set odbc_async_exec to 1 and then check
> it's value. If the value is 1, your ODBC driver can do asynchronous
> execution. If the value is 0, your ODBC driver cannot.
>
> Here's a sample script:
>
> use strict;
>
> use DBI;
>
> sub err_handler {
> my ($state, $msg) = @_;
> # Strip out all of the driver ID stuff
> $msg =~ s/^(\[[\w\s]*\])+//;
> print "===> state: $state msg: $msg\n";
> return 0;
> }
> my $dbh = DBI->connect("dbi:ODBC:TEST248", "sa", "********")
> || die "Can't connect: $DBI::errstr\n";
>
> $dbh->{odbc_err_handler} = \&err_handler;
> $dbh->{odbc_async_exec} = 1;
> print "odbc_async_exec is: $dbh->{odbc_async_exec}\n";
>
> my $sth;
> $sth = $dbh->prepare("dbcc checkdb(model)") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> $sth->finish;
> $dbh->disconnect;
>
>
> Observations:
>
> - I've tested this on Sybase 11.0.3, MS SQL Server 6.5 and 7.0,
> Oracle 8.1.6 using Oracle 7.3 drivers. I doubt any of this will be
> tremendously useful to Oracle users though...
>
> - ODBC seems to buffer the messages it makes available, so you'll get
> server messages in chunks. This means there could be significant
> delays in getting messages from the server. For example, if while
> running a DBCC, you could get a significant message just before
> the DBCC starts on a huge table. Since ODBC is buffering the messages,
> you won't see that significant message until DBCC is done with the
> huge table (and possibly a few others) and fills the buffer.
>
> - Oracle's ODBC drivers do not seem to support asynchronous execution
> at all. But that's OK, since I doubt Oracle generates any messages
> during execution...
>
> - A lot of the guts to implement the error handler was lifted wholesale
> from DBD:Sybase 0.21. I don't even pretend to fully understand some
> of the stuff in this patch. Thanks go to Michael Peppler for his work
> on DBD:Sybase, making this patch possible.
--
David Good [EMAIL PROTECTED]
This space intentionally left blank.
