Thanks for the info.
Since you've delved into both Sybase and ODBC code perhaps you could
suggest a suitable API definition for the $h->{HandleEvent} sub.
What parameters should it expect and what can assign any useful
meaning to the return value etc etc.
Tim.
On Thu, Jul 04, 2002 at 01:10:34AM -0700, David L. Good wrote:
> 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.
