Jeff, I've been busy but finally managed to test the new version. It still fails my test.
Jeff Urlwin wrote: >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 > In my case there isn't a query running. > - 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" > By "describe" you mean SQLDescribeCol, right? Where does the parameter value come from? I'm calling the procedure with the parameter set to -1 (which doesn't produce a resultset) but SQLDescribeCol uses 0 which does produce a resultset. (See the SQL Profiler text below) Why does the call to NUM_OF_FIELDS trigger a describe? Doesn't the call to execute provide the necesarry information? Copied from BOL: For executed statements, the Microsoft® SQL Server™ ODBC driver does not need to query the server to describe columns in a result set. In this case, SQLDescribeCol does not cause a server roundtrip. The current implementation seems like a huge performance hog. > - 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. > Thanks for the update. Strange it didn't fix my problem. I rebooted my machine to make sure I wasn't using the old dll. I might have done some stupid error but ppm says I'm using 0.45_18. (Is there a way to see which version of the dll (not the module) Perl is using when the program is running?) May I see your test for this specific case? I couldn't (easily) find it in http://search.cpan.org/src/JURL/DBD-ODBC-0.45_18/ > >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 >> >> >> >> Cerate the testPrc procedure first and edit %options before you run. ============= Dbitest4.pl ==================== use strict; use warnings; use DBI; use constant LONG_READ_LEN => 8000; my %options = ( DbSrcServer => '(local)', DbSrcDatabase => 'databaseName', DbSrcLoginName => 'sa', DbSrcPassword => '', ); my @dbhPool; ########################################## ### Functions ########################################## sub newDbh() { my $dbh; if(defined($options{DbSrcServer}) && defined($options{DbSrcLoginName}) && defined($options{DbSrcDatabase})) { my $dsn = "DRIVER={SQL Server};SERVER=$options{DbSrcServer};DATABASE=$options{DbSrcDatabase};NETWORK=dbmssocn;UID=$options{DbSrcLoginName};PWD=$options{DbSrcPassword}"; # print "DSN: $dsn\n\n"; $dbh = DBI->connect("DBI:ODBC:$dsn") || die "DBI connect failed: $DBI::errstr\n"; $dbh->{AutoCommit} = 0; # enable transactions, if possible # $dbh->{RaiseError} = 0; # $dbh->{PrintError} = 1; # use RaiseError instead $dbh->{ShowErrorStatement} = 1; push @dbhPool, $dbh; return($dbh); } } sub test() { my $dbh = newDbh(); my $sth = $dbh->prepare("{call testPrc(?)}"); $sth->bind_param(1, -1, { TYPE => 4 }); $sth->execute(); print '$sth->{NUM_OF_FIELDS}: ', $sth->{NUM_OF_FIELDS}, " expected: 0\n"; if($sth->{NUM_OF_FIELDS}) { $sth->fetchrow_hashref('NAME'); } } ########################################## ### Test ########################################## test(); ########################################## ### Cleanup... ########################################## foreach my $dbh (@dbhPool) { $dbh->rollback(); $dbh->disconnect(); } =========== testPrc.sql ======================= create PROCEDURE dbo.testPrc @parameter1 int = 0 As if(@parameter1 >= 0) select * from systypes -- RETURN(@parameter1)
