Skip to Content.
Sympa Menu

freetds - [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: [freetds] problem with ODBC and store procedure output params
  • Date: Thu, 22 Apr 2004 17:24:32 +1000

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...

I have the following stored proc

create procedure dbi_sp_test_1
@f1 int,
@f2 int,
@f3 int output
as
delete from test_table where f1 = f1
insert into test_table (f1, f2) values (@f1, @f2)
select @f3 = f3 from test_table where f3 = @@identity
go

the table is defined as
create table test_table(f1 int, f2 int, primary key(f1,f2), f3 int
identity)


>From the SQL Query Analyzer, and tsql I can do this
declare @x int
exec sp_test_1 9, 9, @x output
print @x

and I see the new value for the identity column.
Good.

>From freetds ODBC however, things aren't so good...

I prepare the statement as

{call sp_test_1(9,9,?)}

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.


Modified odbc/unittest/params.c:



#include "common.h"

/* Test for store procedure and params */
/* Test from Tom Rogers */

static char software_version[] = "$Id: params.c,v 1.4 2003/11/08 18:00:33
freddy77 Exp $";
static void *no_unused_var_warn[] = { software_version, no_unused_var_warn
};

/* SP definition */

static const char sp_define[] = "CREATE PROCEDURE spTestProc\n"
" @InParam int,\n"
" @OutParam int OUTPUT,\n"
" @OutString varchar(20) OUTPUT\n"
"AS\n"
" SELECT @OutParam = @InParam\n"
" SELECT @OutString = 'This is cool!'\n"
" IF @InParam < 10\n" " RETURN (0)\n" " ELSE\n" "
RETURN (1)\n";

#define SP_TEXT "{?=call spTestProc(5,?,?)}"
#define OUTSTRING_LEN 20

int
main(int argc, char *argv[])
{
SQLSMALLINT ReturnCode = 0;
SQLSMALLINT InParam = 5;
SQLSMALLINT OutParam = 1;
SQLCHAR OutString[OUTSTRING_LEN];
SQLINTEGER cbReturnCode = 0, cbInParam = 0, cbOutParam = 0;
SQLINTEGER cbOutString = SQL_NTS;

Connect();

/* drop proc */
if (CommandWithResult(Statement, "drop proc spTestProc") !=
SQL_SUCCESS)
printf("Unable to execute statement\n");

/* create proc */
Command(Statement, sp_define);

if (SQLPrepare(Statement, (SQLCHAR *) SP_TEXT, strlen(SP_TEXT)) !=
SQL_SUCCESS) {
fprintf(stderr, "Unable to prepare statement\n");
return 1;
}

if (SQLBindParameter(Statement, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &ReturnCode, 0, &cbReturnCode) !=
SQL_SUCCESS) {
fprintf(stderr, "Unable to bind input parameter\n");
return 1;
}

if (SQLBindParameter(Statement, 2, SQL_PARAM_OUTPUT, SQL_C_SSHORT,
SQL_INTEGER, 0, 0, &OutParam, 0, &cbOutParam) !=
SQL_SUCCESS) {
fprintf(stderr, "Unable to bind input parameter\n");
return 1;
}

OutString[0] = '\0';
strcpy((char *) OutString, "Test"); /* Comment this line and we
get an error! Why? */
if (SQLBindParameter
(Statement, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR,
OUTSTRING_LEN, 0, OutString, OUTSTRING_LEN,
&cbOutString) != SQL_SUCCESS) {
fprintf(stderr, "Unable to bind input parameter\n");
return 1;
}

if (SQLExecute(Statement) != SQL_SUCCESS) {
fprintf(stderr, "Unable to execute statement\n");
ReportError("", 0, 0);
return 1;
}

// Command(Statement, "drop proc spTestProc");

printf("Output:\n");
printf(" Return Code = %d\n", (int) ReturnCode);
printf(" InParam = %d\n", (int) InParam);
printf(" OutParam = %d\n", (int) OutParam);
printf(" OutString = %s\n", (char *) OutString);

if (InParam != OutParam) {
fprintf(stderr, "InParam != OutParam\n");
return 1;
}

if (strcmp(OutString, "This is cool!") != 0) {
fprintf(stderr, "Bad string returned\n");
return 1;
}

printf("Done successfully!\n");
return 0;
}





Archive powered by MHonArc 2.6.24.

Top of Page