Skip to Content.
Sympa Menu

freetds - RE: [freetds] problem with ODBC and store procedure output params

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Roger Reynolds <rogerr AT softix.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] problem with ODBC and store procedure output params
  • Date: Fri, 23 Apr 2004 08:42:45 +1000


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
params


>
> 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,
> SQL_C_LONG, SQL_INTEGER,
> 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

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




Archive powered by MHonArc 2.6.24.

Top of Page