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) >> >> >>
smime.p7s
Description: S/MIME Cryptographic Signature
