Re: Describe failed for NUM_OF_FIELDS when using odbc_exec_direct (MS SQL Server / DBI::ODBC)

Fri, 16 Nov 2007 11:21:19 -0800 

Martin Evans wrote:

Martin Evans wrote:

Toni Salomäki wrote:

Hi,

I'm trying to write MS SQL Server support for a generic perl module
used to execute arbitrary SQL statements and then return the array
reference for the result set (if one exists). My basic idea was to do
in following way:

my $sth = $dbh->prepare($clause);
$sth->execute();

return if ($sth->{NUM_OF_FIELDS} == 0);
return @{$sth->fetchall_arrayref()};

But I also want to be able to create temporary tables using this same
routine (for example with select ... into #tmp). This is not possible
in MS SQL when prepare + execute is used. Changing the behavior of
execute method to use SQLExecDirect will solve this problem (by
setting $dbh->{odbc_exec_direct} = 1). But this causes following error
message:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

The script works ok, but is there any way to get rid of this error
message? I tried to read all the documentation, but I could not figure
out any other way to check if the statement contains any result sets
than using the NUM_OF_FIELDS.

Versions in use:

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
Binary build 822 [280952] provided by ActiveState http://www.ActiveState.com 
DBD-ODBC: ODBC Driver for DBI Version: 1.13
SQL Native Client 2005.90.3042.00

Thanks in advance for any help, Toni
I'll try and reproduce here but in the mean time can you send my a simple example demonstrating this problem. 

Martin


Toni,

Thanks for the report and off this list the example code.

So others can keep up with this:
It is expected prepare/execute which generates a temporary table will lose the temporary table immediately (see http://technet.microsoft.com/en-US/library/ms131667.aspx which describes the issue). As a result, as you have also found you need to set odbc_exec_direct if you want to use temporary tables. I will be updating the pod and faq on this to make it clear. 

The error:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)
is caused by DBD::ODBC calling SQLNumResultCols after SQLMoreResults says there are no more results and the ODBC driver manager is returning a function sequence error for the second call. Strangely, some Microsoft ODBC driver managers (e.g. the one that comes with Vista) don't report this problem as MS seem to have relaxed their reporting of function sequence errors in some places.
The bug in DBD::ODBC which caused SQLNumResultCols to be called in this situation is now fixed. I can supply you with a new DBD::ODBC (1.15_3) tar source file which includes this fix (although I'm afraid if you cannot build it yourself we could have a problem here as builds I do for activestate installs do not seem to work). Alternatively, you can attempt to apply the fix below but you'll still need to be able to build it: 

In dbdimp.c in the function dbd_describe look for:

   imp_sth->done_desc = 1;    /* assume ok from here on */
   if (!more) {
while (num_fields == 0 && imp_dbh->odbc_sqlmoreresults_supported == 1) { 
     rc = SQLMoreResults(imp_sth->hstmt);
     if (ODBC_TRACE_LEVEL(imp_sth) >= 8) {
PerlIO_printf(DBIc_LOGPIO(imp_sth), "Numfields == 0, SQLMoreResults == %d\n", rc); 
        PerlIO_flush(DBIc_LOGPIO(imp_sth));
     }
     if (rc == SQL_SUCCESS_WITH_INFO) {
AllODBCErrors(imp_sth->henv, imp_sth->hdbc, imp_sth->hstmt, ODBC_TRACE_LEVEL(imp_sth) >= 8, DBIc_LOGPIO(imp_dbh)); 
     }
imp_sth->done_desc = 0; /* reset describe flags, so that we re-describe */ 
     if (rc == SQL_NO_DATA) {
        imp_sth->moreResults = 0;
        dbd_error(h, rc, "dbd_describe/SQLNumResultCols");
        break;
     }
     if (!SQL_ok(rc)) break;

and move the line:
imp_sth->done_desc = 0; /* reset describe flags, so that we re-describe */ 

to after the:

     if (!SQL_ok(rc)) break;
Let me know what you want to do; I can easily send you a 1.15_3 distribution. 

Martin
I forgot to mention using "do" should work if all you are doing is creating a temporary object but of course in your case you are attempting to use one single piece of code to do non-result-set-producing and result-set producing SQL. You would be best advised to split these into calling "do" and "prepare/execute" instead of checking for NUM_OF_FIELDS but if you can't/won't you will need the fix described previously. 

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Reply via email to