Sorry this has taken so long to respond to. > -----Original Message----- > From: Roger Perttu [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 03, 2002 12:01 PM > To: [EMAIL PROTECTED] > Subject: Re: Bug in DBD-ODBC 0.45_16 > > > Jeff, > > I've been busy but finally managed to test the new version. It still > fails my test.
Sigh .. . here's the test I used (at the bottom) -- isn't that what you sent? > > 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. Right. > > > - 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? No, the execute doesn't (at least I don't think it does). SQLExecute returns success or failure, not the number or type of columns. > > 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. That just means that the ODBC driver (the SQL Server part) "knows" about the query, without needing to re-query the database. At somepoint, probably right after the execute or during prepare, where the result set doesn't change or is predictable, it *must* retrieve something about the result set. > > The current implementation seems like a huge performance hog. It may be :), but after execute, DBD::ODBC must determine the right columns. Especially in the case where stored procs can return varying result sets (in your example one input can produce a select * from blah in the other, return @1... > > > - 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?) Just check the date. Or, untar the .tar.gz file and check the .dll in the blib/* directory. > > 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/ attached. It's in a "private" test area, where I didn't include it in the tarball. > >>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) > > > #!perl -w use DBI; use strict; use Data::Dumper; my $dbh = DBI->connect(); $dbh->{LongReadLen} = 8000; eval { local $dbh->{PrintError} = 0; $dbh->do("drop procedure PERL_DBD_TESTPRC"); }; $dbh->do("CREATE PROCEDURE PERL_DBD_TESTPRC \@parameter1 int = 0 AS if (\@parameter1 >= 0) select * from systypes RETURN(\@parameter1) "); sub test() { my $sth = $dbh->prepare("{call PERL_DBD_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}) { my @row; while (@row = $sth->fetchrow_array()) { print join(', ', @row), "\n"; } } } ########################################## ### Test ########################################## unlink("dbitrace.log") if (-e "dbitrace.log"); $dbh->trace(9, "dbitrace.log"); test(); ########################################## ### Cleanup... ########################################## $dbh->disconnect;
