[freetds] problem with ODBC and store procedure output params

Roger Reynolds rogerr at softix.com
Thu Apr 22 18:42:45 EDT 2004

I got the {call ... } syntax from the odbc unittest params.c
You're saying that method for SP invocation is not working?

The workaround you suggsest "sp_test_1 9,9,?" does not work.
I get the error 
   Incorrect syntax near 'sp_test_1'

You suggest as a workaround that uses the {call ...} syntax, but
uses placeholders for, and binds all parameters.  

I've done this, and it works, and this is what params.c does too.

Please help me understand what is "not supported" here.
The use of {call ...} or using placeholders and binding for
some, but not all, of the parameters.

Here's another thing that doesn't work quite right, or maybe
it is supposed to be this way...

If I prepare a batch, like so

   delete from test_table
   {call sp_test_1(?,?,?)}

and bind all the parameters, I get the error

   Formal parameter '@P3' was defined as OUTPUT but 
   the actual parameter not declared OUTPUT

The statement does work properly if I omit the 
initial delete statement.

Thanks for your help.

-----Original Message-----
From: ZIGLIO, Frediano, VF-IT [mailto:Frediano.Ziglio at vodafone.com]
Sent: Friday, April 23, 2004 1:32 AM
To: FreeTDS Development Group
Subject: RE: [freetds] problem with ODBC and store procedure output

> Not sure whether this is something I'm doing wrong,
> a general characteristic of ODBC, or a new or existing
> problem with the freetds ODBC mechanism...
> >From freetds ODBC however, things aren't so good...
> I prepare the statement as
>    {call sp_test_1(9,9,?)}

This syntax is still not supported...

> and bind parameter like so:
>    int val=0, indicator=0;
>    SQLBindParameter(stmtHandle, 1, SQL_PARAM_OUTPUT,
> 			  0, 0, &val, sizeof(val), &indicator);
> The problem is, when I SQLExecute the statement, it returns
> SQL_SUCCESS_WITH_INFO. Using SqlGetDiagRec I see the
> error code is 8162 and the message is 
> [FreeTDS][SQL Server]Formal parameter '@P1' was defined as 
>  OUTPUT but the actual parameter not declared OUTPUT.
> However, I have noticed that if I change the invocation to
>    { call sp_test_1(?,?,?) }
> and then also bind parameters for the first two parameters,
> then the SQLExecute returns SQL_SUCCESS, and the output
> parameter value is properly returned to my application.
> I was able to demonstrate the same behavior by
> mucking around with the odbc/unittest/params.c slightly,
> and I have included the full version of that file below.
> So, the question is - what's up here? 
> Any help would be much appreciated.

A workaround is to prepare a ms sql syntax like "sp_test_1 9,9,?" (I'm
not that sure is works....). Or prepare something like "{call
sp_test_1(?,?,?)}" and bind parameters according.

> Modified odbc/unittest/params.c:

I'll have a look

FreeTDS mailing list
FreeTDS at lists.ibiblio.org

More information about the FreeTDS mailing list