Re: FreeTDS problems with converting SQL BigInt

hi!

On debugging this further, I found the following.

1) The output parameter works fine if the Sproc has an OUTPUT parameter, and does not return a resultset.
2) OTOH, if the sproc returns a resultset, then the output parameter does not work.

In my program, I was callig a sproc that has an OUTPUT parameter, and also returns a resultset.

For testing, I modified my program to call another sproc that does not return a resultset but has an output parameter. I call this new sproc after that I called my sproc that returns both output parameter and resultset.

I found something interesting. When I execute the sproc statement after I call SQLBindParameter(...), my output parameter is not being set. However, when I call SQLBindParameter() for the second sproc call, at that time I see that my output parameter has the correct value from the previous sproc call. It almost seems as if the output parameter value is being set after the resultset has come in.

NOTE, that I cannot hack my scenario to call a sproc that returns an OUTPUT parameter and no resultset.

I found the following text in the documentation at http://www.freetds.org/faq.html#ms.output.parameters

Output Parameters
I'm not getting my output parameters returned, but I seem to be doing everything right! 
That's not a question!
Microsoft SQL Server 7 with SP3, and later versions, quietly 
changed (which is to say, broke) how they respond to queries that 
execute stored procedures with output parameters.  Earlier servers let 
you send a query like EXECUTE A @P OUTPUT and fetch the output parameter as a special result row (technique 
varying by library).  Newer servers simply don't send back that data.  
To elicit output parameters from them, you have to use the RPC functions such as the DB-Library dbrpcparam. 
--

However, the dbrpcparam link in the above document doesnt reference anything. Can someone point me to the correct link?

FWIW we are running "Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) "

Thanks

feroze.




----- Original Message -----
From: James K. Lowden <[email protected]>
To: [email protected]
Cc: Feroze Daud <[email protected]>; FreeTDS Development Group <[email protected]>
Sent: Monday, September 16, 2013 3:51 PM
Subject: Re: [freetds] FreeTDS problems with converting SQL BigInt

On Mon, 16 Sep 2013 10:30:54 -0700 (PDT)
Feroze Daud <[email protected]> wrote:

> I am using odbc.
> 
> I know 0.64 is not supported, but I dont have the liberty to upgrade.
> I could make a case for upgrading if this bug is fixed in 0.82.
> 
> Is this a known bug that was fixed in 0.82?

Even 0.82 is five years old now.  

The unit test src/odbc/unittests/const_params.c uses an output
parameter of type integer.  I'd suggest modifying it to test a bigint
instead.  If it fails, that's a bug worth fixing.  If it succeeds, you
have your solution.  

HTH.  

--jkl