> 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.
Not directly -- only via ODBC calls.
Jeff
>
> >
> >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
>
