David, Patches welcome. I'm not sure what happened to your earlier patches, but I'm more on top of things lately and a fresh patch against .41 would be welcome.
Thanks, jeff > -----Original Message----- > From: David L. Good [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 13, 2002 9:42 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: DBD::ODBC and Sybase/SQL Server server messages > > > I just took a look at DBD::ODBC again after nearly a two year hiatus and > discovered that this patch I submitted two years ago was never put in. > The project I was working on at the time got back burnered soon after I > sent in the patch, so I never really checked. Now I'm starting a similar > project and I think this old patch might still be useful, but I'm not sure > how much stuff has changed since DBD::ODBC 0.28. I'm including my (rather > longwinded) explanation of what it does and why it might be useful. > Does this seem like something that might be useful to anyone else? > If so, and if nobody else wants to do it, I'll take a look at integrating > it into 0.41. One potential problem though is that I don't currently have > a box to test it on. > > ----- Forwarded message from David Good <[EMAIL PROTECTED]> ----- > > Date: Thu, 29 Jun 2000 13:41:31 -0700 > From: David Good <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: DBD::ODBC and Sybase/SQL Server server messages > > > 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. > > > > --- dbdimp.c.orig Wed Jun 14 14:31:48 2000 > +++ dbdimp.c Wed Jun 28 12:21:53 2000 > @@ -327,9 +327,6 @@ > HSTMT hstmt = SQL_NULL_HSTMT; > SV *errstr; > > - if (err_rc == SQL_SUCCESS && DBIS->debug<3) /* nothing to do */ > - return; > - > switch(DBIc_TYPE(imp_xxh)) { > case DBIt_ST: > imp_sth = (struct imp_sth_st *)(imp_xxh); > @@ -342,6 +339,17 @@ > default: > croak("panic: dbd_error on bad handle type"); > } > + > + /* > + * If status is SQL_SUCCESS, there's no error, so we can just return. > + * There may be status or other non-error messsages though. > + * We want those messages if the debug level is set to at least 3. > + * If an error handler is installed, let it decide what messages > + * should or shouldn't be reported. > + */ > + if (err_rc == SQL_SUCCESS && DBIS->debug < 3 && > !imp_dbh->err_handler) > + return; > + > hdbc = imp_dbh->hdbc; > henv = imp_dbh->henv; > > @@ -368,6 +376,50 @@ > ErrorMsg, sizeof(ErrorMsg)-1, &ErrorMsgLen > )) == SQL_SUCCESS || rc == > SQL_SUCCESS_WITH_INFO) { > sv_setpvn(DBIc_STATE(imp_xxh), sqlstate, 5); > + /* > + * If there's an error handler, run it and see what it > returns... > + * (lifted from DBD:Sybase 0.21) > + */ > + > + if(imp_dbh->err_handler) { > + dSP; > + SV *sv, **svp; > + HV *hv; > + int retval, count; > + > + ENTER; > + SAVETMPS; > + PUSHMARK(sp); > + > + if (DBIS->debug >= 3) > + fprintf(DBILOGFP, "dbd_error: calling err_handler\n"); > + > + /* > + * Here are the args to the error handler routine: > + * 1. sqlstate (string) > + * 2. ErrorMsg (string) > + * > + * That's it for now... > + */ > + XPUSHs(sv_2mortal(newSVpv(sqlstate, 0))); > + XPUSHs(sv_2mortal(newSVpv(ErrorMsg, 0))); > + > + > + PUTBACK; > + if((count = perl_call_sv(imp_dbh->err_handler, > G_SCALAR)) != 1) > + croak("An error handler can't return a LIST."); > + SPAGAIN; > + retval = POPi; > + > + PUTBACK; > + FREETMPS; > + LEAVE; > + > + /* If the called sub returns 0 then ignore this error */ > + if(retval == 0) > + continue; > + } > + > if (SvCUR(errstr) > 0) { > sv_catpv(errstr, "\n"); > /* JLU: attempt to get a reasonable error */ > @@ -406,7 +458,7 @@ > else henv = SQL_NULL_HENV; /* done the top */ > } > > - if (err_rc != SQL_SUCCESS) { > + if (err_rc != SQL_SUCCESS && err_rc != SQL_STILL_EXECUTING) { > if (what) { > char buf[10]; > sprintf(buf, " err=%d", err_rc); > @@ -631,6 +683,23 @@ > imp_sth->RowCount = -1; > imp_sth->eod = -1; > > + /* If AsyncExecute is set and AsyncType is SQL_AM_STATEMENT, > we need to > + * set the SQL_ATTR_ASYNC_ENABLE attribute. > + */ > + if (imp_dbh->AsyncExecute && imp_dbh->AsyncType == SQL_AM_STATEMENT){ > + rc = SQLSetStmtAttr(imp_sth->hstmt, > + SQL_ATTR_ASYNC_ENABLE, > + (SQLPOINTER) SQL_ASYNC_ENABLE_ON, > + SQL_IS_UINTEGER > + ); > + if (!SQL_ok(rc)) { > + dbd_error(sth, rc, "st_prepare/SQLSetStmtAttr"); > + SQLFreeStmt(imp_sth->hstmt, SQL_DROP); > + imp_sth->hstmt = SQL_NULL_HSTMT; > + return 0; > + } > + } > + > DBIc_IMPSET_on(imp_sth); > return 1; > } > @@ -934,6 +1003,23 @@ > imp_sth->hstmt); > > rc = SQLExecute(imp_sth->hstmt); > + > + /* > + * If asynchronous execution has been enabled, SQLExecute will > + * return SQL_STILL_EXECUTING until it has finished. > + * Grab whatever messages occur during execution... > + */ > + while (rc == SQL_STILL_EXECUTING){ > + dbd_error(sth, rc, "st_execute/SQLExecute"); > + > + /* Wait a second so we don't loop too fast and bring the machine > + * to its knees > + */ > + sleep(1); > + > + rc = SQLExecute(imp_sth->hstmt); > + } > + > /* patches to handle blobs better, via Jochen Wiedmann */ > while (rc == SQL_NEED_DATA) { > phs_t* phs; > @@ -956,11 +1042,16 @@ > } > rc = SQL_NEED_DATA; /* So the loop continues ... */ > } > - > + > + /* > + * Call dbd_error regardless of the value of rc so we can > + * get any status messages that are desired. > + */ > + dbd_error(sth, rc, "st_execute/SQLExecute"); > if (!SQL_ok(rc)) { > - dbd_error(sth, rc, "st_execute/SQLExecute"); > - return -2; > + return -2; > } > + > > rc = SQLRowCount(imp_sth->hstmt, &imp_sth->RowCount); > if (!SQL_ok(rc)) { > @@ -1664,13 +1755,118 @@ > STRLEN kl; > STRLEN plen; > char *key = SvPV(keysv,kl); > - SV *cachesv = NULL; > + SV *cachesv = NULL; /* This never seems to be used?!? > [dgood 6/16/00] */ > int on; > UDWORD vParam; > const db_params *pars; > > - if ((pars = S_dbOption(S_db_storeOptions, key, kl)) == NULL) > + if ((pars = S_dbOption(S_db_storeOptions, key, kl)) == NULL) { > + > + /* Here's where we put attributes that are *not* just */ > + /* SQLSetConnectOption attributes */ > + > + /* This was taken from DBD::Sybase 0.21 */ > + if (kl == 16 && strEQ(key, "odbc_err_handler")) { > + if(valuesv == &sv_undef) { > + imp_dbh->err_handler = NULL; > + } else if(imp_dbh->err_handler == (SV*)NULL) { > + imp_dbh->err_handler = newSVsv(valuesv); > + } else { > + sv_setsv(imp_dbh->err_handler, valuesv); > + } > + return TRUE; > + } > + if (kl == 15 && strEQ(key, "odbc_async_exec")) { > + on = SvTRUE(valuesv); > + if(on) { > + /* Only bother setting the attribute if it's not > already set! */ > + if (imp_dbh->AsyncExecute == 1) > + return TRUE; > + > + /* > + * Determine which method of async execution this > + * driver allows -- per-connection or per-statement > + */ > + rc = SQLGetInfo(imp_dbh->hdbc, > + SQL_ASYNC_MODE, > + &imp_dbh->AsyncType, > + sizeof(imp_dbh->AsyncType), > + NULL > + ); > + /* > + * Normally, we'd do a if (!SQL_ok(rc)) ... here. > + * Unfortunately, if the driver doesn't support async > + * mode, it may return an error here. There doesn't > + * seem to be any other way to check (other than doing > + * a special check for the SQLSTATE). We'll just default > + * to doing nothing and not bother checking errors. > + */ > + > + > + if (imp_dbh->AsyncType == SQL_AM_CONNECTION){ > + /* > + * Driver has per-connection async option. Set it > + * now in the dbh. > + */ > + if (DBIS->debug >= 2) > + fprintf(DBILOGFP, > + "Supported AsyncType is > SQL_AM_CONNECTION\n"); > + rc = SQLSetConnectOption(imp_dbh->hdbc, > + SQL_ATTR_ASYNC_ENABLE, > + SQL_ASYNC_ENABLE_ON > + ); > + if (!SQL_ok(rc)) { > + dbd_error(dbh, rc, "db_STORE/SQLSetConnectOption"); > + return FALSE; > + } > + imp_dbh->AsyncExecute = 1; > + } > + else if (imp_dbh->AsyncType == SQL_AM_STATEMENT){ > + /* > + * Driver has per-statement async option. Just set > + * AsyncExecute and the rest will be handled by > + * dbd_st_prepare. > + */ > + if (DBIS->debug >= 2) > + fprintf(DBILOGFP, > + "Supported AsyncType is > SQL_AM_STATEMENT\n"); > + imp_dbh->AsyncExecute = 1; > + } > + else { /* (imp_dbh->AsyncType == SQL_AM_NONE) */ > + /* > + * We're out of luck. > + */ > + if (DBIS->debug >= 2) > + fprintf(DBILOGFP, > + "Supported AsyncType is SQL_AM_NONE\n"); > + imp_dbh->AsyncExecute = 0; > + return FALSE; > + } > + } else { > + /* Only bother turning it off if it was previously set... */ > + if (imp_dbh->AsyncExecute == 1) { > + > + /* We only need to do anything here if the AsyncType is > + * SQL_AM_CONNECTION since the per-statement async type > + * is turned on only when the statement handle > is created. > + */ > + if (imp_dbh->AsyncType == SQL_AM_CONNECTION){ > + rc = SQLSetConnectOption(imp_dbh->hdbc, > + SQL_ATTR_ASYNC_ENABLE, > + SQL_ASYNC_ENABLE_OFF > + ); > + if (!SQL_ok(rc)) { > + dbd_error(dbh, rc, > "db_STORE/SQLSetConnectOption"); > + return FALSE; > + } > + } > + } > + imp_dbh->AsyncExecute = 0; > + } > + return TRUE; > + } > return FALSE; > + } > > switch(pars->fOption) > { > @@ -1731,8 +1927,27 @@ > > /* checking pars we need FAST */ > > - if ((pars = S_dbOption(S_db_fetchOptions, key, kl)) == NULL) > - return Nullsv; > + if ((pars = S_dbOption(S_db_fetchOptions, key, kl)) == NULL) { > + > + /* Here's where we put attributes that are *not* just */ > + /* SQLGetConnectOption attributes */ > + > + /* This was taken from DBD::Sybase 0.21 */ > + if (kl == 16 && strEQ(key, "odbc_err_handler")) { > + if(imp_dbh->err_handler) { > + retsv = newSVsv(imp_dbh->err_handler); > + } else { > + retsv = &sv_undef; > + } > + } > + if (kl == 15 && strEQ(key, "odbc_async_exec")) { > + if(imp_dbh->AsyncExecute) > + retsv = newSViv(1); > + else > + retsv = newSViv(0); > + } > + return retsv; > + } > > /* > * readonly, tracefile etc. isn't working yet. only > AutoCommit supported. > --- dbdimp.h.orig Wed Jun 14 14:31:55 2000 > +++ dbdimp.h Wed Jun 21 15:24:05 2000 > @@ -27,6 +27,13 @@ > dbih_dbc_t com; /* MUST be first element in structure */ > HENV henv; /* copy from imp_drh for > speed */ > HDBC hdbc; > + > + SV *err_handler; > + > + int AsyncExecute; /* Execute statements > asynchronously */ > + SQLUINTEGER AsyncType; /* What type of async > execution the driver > + * supports > + */ > }; > > > @@ -38,7 +45,7 @@ > HDBC hdbc; /* copy for speed */ > HSTMT hstmt; > > - int moreResults; /* are there more results to fetch? */ > + int moreResults; /* are there more results to fetch? */ > int done_desc; /* have we described this sth yet ? */ > > /* Input Details */ > > > ----- End forwarded message ----- > > -- > David Good > [EMAIL PROTECTED] > > This space intentionally left blank. >
