Jeff Urlwin wrote:
>
>
>>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
>
I'm beginning to suspect that my problems are due to a crappy
implementation of SQLExecute in Microsofts ODBC driver or in SQL Server.
I suspect that under certain conditions the ODBC function
SQLNumResultCols gives the wrong results back. However I assume that it
would be possible to work around it and in the process significantly
increase performance :-)
I've had a quick look at dbdimp.c and dbd_st_execute()
Maybe it's a unnecessary call to dbd_describe() (or SQLNumResultCols)
that triggers the use of "SET FMTONLY...". Or maybe some important
information is lost before the call to SQLNumResultCols. It might also
be caused by the code that follows after SQLNumResultCols in
dbd_describe(). Maybe Jeff could insert a direct call to
SQLNumResultCols right after SQLExecute and use this in debug mode. (If
I had a compiler and debugger I could check myself where the "SET
FMTONLY..." is triggered, suppose I'm not a real programmer)
After SQLExecute have been called I would assume that calling
SQLNumResultCols would not trigger a round trip to the server and the
use of "SET FMTONLY..." to reexecute the statement. After all running a
statement under fmtonly just returns an empty resultset. It's like
running "select column from table where 1 = 2". All information should
be available from SQLExecute. On the other hand if SQLNumResultCols is
called after prepare but before SQLExecute there will be trouble because
there is no resultset to examine.
From MSDN:
"The application can call SQLNumResultCols at any time after the
statement is
prepared or executed. However, because some data sources cannot
easily describe
the result sets that will be created by prepared statements,
performance will suffer
if SQLNumResultCols is called after a statement is prepared but
before it is executed."
Maybe it possible to rewrite dbd_st_execute() in a more optimistic way
and don't request so much metadata. Only request metadata if something
goes wrong.
Defered bind
Execute
read data (cache first row for fetch)
try to get output parameters if there is no more data.
I recall that it's not possible to get output parameters and returncode
until all data have been fetched (for SQL Server).
I've also noticed that the way I call my procedure make huge difference.
(The output from Profiler is hard to cut and paste so I just copied the
equivalent SQL)
This is what I see in Profiler if I use ODBC call syntax "{call
testPrc(?)}":
---------------------------------------------------------------------------
set implicit_transactions on
go
testPrc -1
go
SET FMTONLY ON EXEC testPrc 0 SET FMTONLY OFF
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
---------------------------------------------------------------------------
Except for the "SET FMTONLY ON" stuff that's the same result I'll get if
I use a pure Microsoft solution with JScript, OLEDB and ODBC.
This is what I get with Transact syntax "exec testPrc ?":
---------------------------------------------------------------------------
set implicit_transactions on
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 int', N'exec testPrc @P1', 1
select @P1
go
sp_execute 1, -1
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
----------------------------------------------------------------------------
Finally, I noticed that dbdimp.c uses a mix of space and tab for
indentation. It would be nice if this could be cleaned up unless it
messes up Jeff's source control system.
/Roger P
>
>
>
>
>>>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
>>
>>
>>
>
>
>
>
>
