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

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

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

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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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

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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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

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