Are you saying the wrong value is getting posted to the procedure? Jeff
> -----Original Message----- > From: Roger Perttu [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 07, 2002 8:47 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Bug in DBD-ODBC 0.45_18 > > > 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{DbSrcDataba > se};NETWORK=dbmssocn;UID=$options{DbSrcLoginName};PWD=$options{DbS > rcPassword}"; > # 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) > > > > >
