Thanks, Martin. I've found similar information in BOL and MSDN. I'm _not_ an expert on ODBC so when I see the trace in SQL Profiler I don't know if the statements are generated by DBD-ODBC or implicitly by the ODBC driver.
A note to Tim if he reads this: It would be _very_ nice if it would be possible to turn of the warning generated when fetchrow_hashref is called and there is no result set. I know I can catch the error but that makes my code a bit ugly. [EMAIL PROTECTED] wrote: >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 > Should there be two different URLs? > >I think what might be happening in your case is: > >SQLPrepare exec testproc -1 > Strangely there is no prepare or Profiler refuses to show it to me. I use ODBC call syntax "{call testPrc(?)}" not Transact-SQL execute syntax "exec testPrc ?" If I use Transact syntax this code is executed on the server: exec sp_prepare @P1 output, N'@P1 int', N'exec testPrc @P1', 1 > sp_sproc_columns testproc > this is so the ODBC driver can answer SQLDescribeParam calls > Not called. However if I change my program to use DBD-ADO sp_sproc_columns is called. > 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. > Actually, "SET FMTONLY ON EXEC testPrc 0 SET FMTONLY OFF" with a parameter is used. But that's not the statement I called in the first place!!! If this comes from ODBC it self then there is a bug in Microsoft's code. I've just verified that it's possible to execute "SET FMTONLY ON {call testprc(-1)} SET FMTONLY OFF" on SQL Server and that gives the proper result. I rewrote my program using JScript and the OLEDB provider for ODBC. But that didn't result in any use of "SET FMTONLY..." so I assume this is something that Jeff is triggering in his code. > >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. >> Yes, this thread is messy >> >>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 >> Yes, I'm not sure of the syntax but column names is included to catch errors Sample from BOL: INSERT INTO Shippers (ShipperID, CompanyName, Phone) VALUES (?, ?, ?) On a call to SQLDescribeParam, this ODBC SQL statement causes the driver to execute the following Transact-SQL statement: SET FMTONLY ON SELECT ShipperID, CompanyName, Phone FROM Shippers SET FMTONLY OFF SQLDescribeParam can, therefore, return any error code that SQLExecute or SQLExecDirect might return. >>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" >> Yes, I see the same thing in SQL Profiler >> >>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. >> I'm not so sure. Why doesn't "set fmtonly on" use my statement? If it's possible to call SQLDescribeParam before parameters are bound then I can understand that it has to guess. And if there is guessing going on why aren't the default parameter values used or no parameters at all. >> >>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. >> I'm pretty sure DBD-ODBC could be rewritten in a way that avoids SQLDescribeParam (and the round trip to the server). Maybe it would be possible to fetch and cache the first row of the result set. That would get the columns of the first result set. The funny thing is that SQLDescribeParam is called even if I remove NUM_FIELDS >> 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've got no control over the stored procedures. >>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). >> >> >> I do blame DBI and DBD-ODBC (sort of). It's possible to do what I want using ODBC from JScript so it could be possible from Perl. DBI and DBD-ODBC are a bit noisy trying to get meta data from the database. Tim and Jeff are doing a great job with (I assume) limited time and resources. I really appreciate their work but that doesn't mean there can't be improvements. I try to help by finding bugs. Roger P
smime.p7s
Description: S/MIME Cryptographic Signature
