FreeTDS Development Group

["Rogers, Tom" <tom.rogers AT ccur.com>]

Attached is a sample CPP file that has a main that uses UnixODBC and
FreeTDS.  In the comments of the CPP file you will find a stored procedure
declaration.  I created the stored procedure in the Northwinds database of a
MS SQL 7.0 server and tested from there.

The SP is pretty simple, as is the sample program:


CREATE PROCEDURE [dbo].[spTestProc]
    @InParam int,
    @OutParam int OUTPUT,
    @OutString varchar(20) OUTPUT
AS
     SET @OutParam = @InParam
     SET @OutString = 'This is cool!'
     IF @InParam<10
          RETURN (0)
     ELSE
          RETURN (1)
GO


Desired Stored Proc Behavior:  If you pass in an @InParam of 10 or lower you
get an SP return of 0, else you get an SP return of 1.  @OutParam should be
reset to whatever @InParam value is input. The @OutString param should be
returned as the string "This is cool."

Results Displayed:  The test program below displays that the return code is
getting returned properly based in by the @InParam parameter; thus I know
the SP is being called and input params are working properly.  However, the
output parameters are not getting returned and remain whatever they were
before calling SQLExecute.  I tried using "SET NOCOUNT ON" as the first
statement in the SP to assure that the output params aren't getting backed
up behind the row count info as Microsoft warns against with some drivers,
but that made no difference.

Sample Output:

Output:
   Result = 0
   Return Code = 0
   InParam = 5
   OutParam = 1
   OutString = Test


 <<tdstest.cpp>> 
I hope this is enough to reproduce the problem.  Also if interest is that if
the @OutString varchar input parameter isn't set to some non-blank string,
the SQLExecute call returns SQL_ERROR.  This is not the behavior I would
expect when dealing with an OUTPUT only parameter (or an in/out or even in
parameter for that matter).  I have had other problems with empty strings
with TDS.  Comments on that would be helpful as well.

Thomas Rogers
System Engineer
Concurrent Computer Corporation
phone: 215.712.7422 x7261
mailto:tomr AT ccur.com

 -----Original Message-----
From:   Frediano Ziglio [mailto:freddyz77 AT tin.it] 
Sent:   Monday, July 21, 2003 3:51 AM
To:     FreeTDS Development Group
Subject:        RE: [freetds] ODBC RPC

Il gio, 2003-07-17 alle 23:16, Rogers, Tom ha scritto:
> A couple of other things:
> 
> The test app that I wrote used UnixODBC, calling:
> 
> SQLPrepare()
> SQLBindParameter()  
> 
> and then
> 
> SQLExecute()
> 
> I am assuming this should work and use of SQLExecDirect is not required.
> 

Hi Thomas!
Yes, this should work. Can you post code please so I can try to
reproduce your problem? There is a unittest in CVS that do a similar
test but succeed so I don't understand why your code do not work...

freddy77


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Attachment: tdstest.cpp
Description: Binary data