This is for the most part a repost of my earlier post "Bug in DBD-ODBC 0.45_16"
I've now tested it (DBD-ODBC 0.45_18) on a fresh install of W2k and Active Perl 633. Even three different MDAC's 2.5 sp2, 2.6 sp2 and 2.7 RTM sp2. AFIK the bug is still there. The problem is that my sp is called with the wrong parameter value in (0 instead of -1): SET FMTONLY ON EXEC testPrc 0 SET FMTONLY OFF Just for testing I changed the default value from 0 to -1 but that didn't have any effect. I then added a new parameter to the procedure to see if describe would use two parameters but it didn't. Finally I changed my test script to use two parameters and the procedure is now called with both parameters wrong. /Roger P -------- Original Message -------- Subject: Re: Bug in DBD-ODBC 0.45_16 Date: Thu, 03 Oct 2002 18:01:20 +0200 From: Roger Perttu <[EMAIL PROTECTED]> Organization: Easit AB To: [EMAIL PROTECTED] References: <[EMAIL PROTECTED]> 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)
