FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org]On Behalf Of 
> > On Thu, 13 May 2004, Frediano Ziglio <freddyz77 AT tin.it> wrote:
> > > - getting store procedure informations (I don't know how)
> > 
> > What information, exactly?
> 
> The first recordset types returned (and possibly store procedure
> parameters list)

Parameters are no problem:

SELECT   @proc_name as 'procedure'
        , c.name as 'parameter_name'
        , t.name as 'type'
        , c.length
        , case c.isnullable when 1 then 'Yes' else 'No' end as Nulls
FROM    syscolumns as c
JOIN    systypes as t
ON t.xusertype = c.xusertype
WHERE   c.id = object_id(@proc_name)

Result set metadata *is* a problem.  It can't be had without either A) 
running the procedure, or B) parsing its SQL.  Which ODBC function requires 
it?  

If you are thinking of SQLProcedureColumns(), there is this note on 
SQLProcedureColumns() in MDAC SDK:  

"Note SQLProcedureColumns might not return all columns used by a procedure. 
For example, a driver might only return information about the parameters used 
by a procedure and not the columns in a result set it generates."

Books Online specifically states that in Microsoft's ODBC implementation 
"SQLProcedureColumns uses the catalog stored procedure sp_sproc_columns to 
report the attributes of stored procedure columns."  

Note well the Remarks for sp_sproc_columns:

"Remarks
The returned columns belong to the parameters or result set of a stored 
procedure. If the SP_NUM_PARAMETERS and SP_NUM_RESULT_SETS columns returned 
by sp_stored_procedures for a particular stored procedure are -1 
(indeterminate), sp_sproc_columns returns no rows for that stored procedure."

Then comes this glorious Alice-in-Wonderland observation:

"In SQL Server, only the column information about input and output parameters 
for the stored procedure are [sic] returned."

One wonders what besides SQL Server runs sp_sproc_columns?  

Looks like a dead end to me, amico.  

--jkl

P.S.  This is what I love about Microsoft's documentation.  It's written by 
the Marketing department.  One never sees "X doesn't work" or "X doesn't 
exist".  One occasionally sees "X might not work" (what does "might" mean in 
a deterministic system?), but more often a missing feature is simply not 
mentioned anywhere, forcing the reader to spend enormous effort trying to 
prove a negative.  How many thousands of man hours have been repetitively 
wasted proving in isolation that neither of these functions does what they 
suggest they "might" do?  











-----------------------------------------
The information contained in this transmission may contain privileged and 
confidential information and is intended only for the use of the person(s) 
named above. If you are not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, any 
review, dissemination, distribution or duplication of this communication is 
strictly prohibited. If you are not the intended recipient, please contact 
the sender immediately by reply e-mail and destroy all copies of the original 
message. Please note that we do not accept account orders and/or instructions 
by e-mail, and therefore will not be responsible for carrying out such orders 
and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to 
inform and update our clients, prospects and consultants of developments 
relating to our services and products, would not like to receive further 
e-mail correspondence from the sender, please "reply" to the sender 
indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, 
NY 10105.