I found some references on MS site that helps explain: (beware my mailer's wrapping in these URLs)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc _d_1not.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc _d_1not.asp I think what might be happening in your case is: SQLPrepare exec testproc -1 sp_sproc_columns testproc this is so the ODBC driver can answer SQLDescribeParam calls set fmtonly on exec testproc set fmtonly off this generates an empty result so SQLDescribeCol can work now when SQLExecute is called with exec testproc -1 no new result-set is generated by your procedure and so you see the old one. Martin On 15-Oct-2002 [EMAIL PROTECTED] wrote: > I've lost track of what you originally wanted to do and guess the code and > procedure you included in this email (below) is cut down. I can however, > explain some of what is happening. > > As far as I understand it, TDS used by MS SQL Server does not have describe > column or describe parameter functionality. When you want to describe > parameters in ODBC, the SQL Server driver calls "sp_sproc_columns proc_name" > and when you want to describe columns the SQL Server driver rearranges your > SQL > e.g. > > select * from table where column=? > > becomes something like > > set fmtonly on select column from table set fmtonly off > > The "set fmtonly on" returns only metadata i.e. no rows are processed or sent > to > the client as a result of the request. > > If you run something like tcpdump on your machine when you run your Perl you > will see a: > > "set fmtonly on exec testproc set fmtonly off" > > which I presume is the SQL Server driver attempting to work out what the > columns are. Of course, because of the way your procedure is written, if a > parameter is not passed in, the procedure assumes a value of 0 and the select > is run. SQL Server is probably expecting the result-set composition not to > change but in your procedure it changes depending on whether p1 >= 0 or not. > > As a result, checking NUM_FIELDS in your Perl is not going to work as a > method > of deciding whether the procedure returns a result-set or not. Perhaps, > instead > (and I say this not really knowing your objective) you could: > > [1] make the the procedure return an output parameter which tells you > whether a result-set was generated or not > > [2] always return a result set e.g. if the real select is run fine, and if P1 > < > 0, do a select @p1. This way you will have a result-set containing 1 > column > containing P1 for a non-select and a result-set containing the 20 columns > from systypes for the real select. > > I don't see any of this being the fault of DBD::ODBC. There are other issues > with the MS SQL Server ODBC driver and procedures (specifically cursors) you > might want to be aware of - search microsoft's site as I don't have the URLs > to > hand (and they are always changing anyway). > > Martin > > On 15-Oct-2002 Roger Perttu wrote: >> Jeff Urlwin wrote: >> >>>Are you saying the wrong value is getting posted to the procedure? >>> >>> >> Yes, I call the procedure once with the parameter set to -1 but when the >> procedure is "examined" (run with SET FMTONLY ON) the parameter is set >> to 0 by someone. Look at the output from SQL Profiler (below somewere) >> The trace is the result of _one_ call from Perl. This might be a bug in >> ODBC, I don't know. >> >> It would be nice if someone else would test this. >> >> /Roger P >> >>>>-----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) >>>> >>>> >>>> > > -- > Martin J. Evans > Easysoft Ltd, UK > Development -- Martin J. Evans Easysoft Ltd, UK Development
