Roger,
I've finally had a chance to look at this (going through my queue of issues
& questions). What I can see is the following:
- No result columns are being returned
- DBD::ODBC calls SQLMoreResults (and gets SQL_SUCCESS_WITH_INFO) to skip
over the empty result set
- There are no more results, so execute() returns
- then, the call to NUM_OF_FIELDS triggers a "describe"
- to that, SQLServer returns a large description of columns (probably
describing what would be the select * from systypes would have been.
I would have thought it would not return anything at that point, other than
there are no rows in the result set.
I've fixed the problem/worked around it. My tests pass here, but it's
probably worth you testing further.
Thanks for the find and the test! I'm releasing _18 tonight.
Regards,
Jeff
>
> Hi,
>
> I've managed to produce a test case for the bug I reported earlier.
>
> When I call a stored procedure that should not return data
> $sth->{NUM_OF_FIELDS} is wrong and I get the following error:
>
> E:\Projekt\Helpdesk\Perl\UpgradeDB>dbitest4.pl
> $sth->{NUM_OF_FIELDS}: 18 expected: 0
> DBD::ODBC::st fetchrow_hashref failed: (DBD: no select statement
> currently executing err=-1) [for statement ``{call testPrc(?)}'' with
> params: 1=-1]) at E:\Projekt\Helpdesk\Perl\UpgradeDB\DbiTest4.pl line 47.
>
> The problem is caused by the fact that the procedure is called with
> different parameters when its output is examined (SET FMTONLY ON).
>
> Cut and paste from SQL Profiler:
> Event Class Text
> +Connect
> +ExistingConnection
> +SQL:BatchStarting set implicit_transactions on
> +RPC:Starting testPrc -1
> +SP:Starting testPrc
> +SP:StmtStarting if(@parameter1 >= 0)
> +SP:StmtStarting RETURN(@parameter1)
> +SP:Completed testPrc
> +SQL:BatchStarting SET FMTONLY ON EXEC testPrc 0 SET FMTONLY OFF
> +SP:Starting testPrc
> +SP:StmtStarting if(@parameter1 >= 0)
> +SP:StmtStarting select * from systypes
> +SP:StmtStarting RETURN(@parameter1)
> +SP:Completed testPrc
> +SQL:BatchStarting IF @@TRANCOUNT > 0 ROLLBACK TRAN
> Disconnect
>
>
> Platform:
> MDAC 2.7 RTM
>
> Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
> Oct 24 2000 18:39:12
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
>
>
> Have fun ;-)
>
> Regards,
> Roger P
>
