> >
> 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 :-)
>From the link that I read, the driver itself triggers the SET FMTONLY ON
exec yourproc(0) SET FMTONLY OFF to gather the expected columns. I think
the 0 is probably coming from a default where the driver determines the
stored proc only takes one parameter and it's an integer, and they call it
with a default value for each type of parameter. I'm just guessing here,
but it actually seems reasonable from the outside. Especially where the
same stored procedure can return wildly different result sets.
>
> 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)
Well -- perl programmers are real programmers to me :)
Also, I think the article Martin mentioned shows that Microsoft's driver
issues the SET FMTONLY ON, etc, when it thinks it needs to get information
about the result set. In fact, I do call various functions after the
execute. Get A DBI trace and you will see the order of some of the things
you need, without going into Debug mode.
>
> 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.
Guess what: what DBD::ODBC does is call the "dbd_describe" which only after
execute(), which is what calls SQLNumResultCols. However, if your code does
this:
$sth = $dbh->prepare(something);
$sth->{NUM_OF_FIELDS};
$sth->execute;
That will trigger the grabbing of the meta data before the execute (which
doesn't always work, BTW, based upon the driver).
>
> I recall that it's not possible to get output parameters and returncode
> until all data have been fetched (for SQL Server).
Yep.
>
> 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.
And, that's coming from the ODBC driver itself...
>
>
> 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.
Actually, my editor, by default, uses N spaces for the indent and when the
tabs can be used, it fills them. It's convenient for those with fixed tab
sizes and printing...
BTW, Martin noticed a dumb bug in my code where the certain fix for you
didn't go into effect unless you have DBI_TRACE set to two or above...I'll
try to re-release tonight with a few other things...
Regards,
Jeff
