[freetds] problem with ODBC and store procedure output params

Roger Reynolds rogerr at softix.com
Thu Apr 22 03:24:32 EDT 2004


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;
}




More information about the FreeTDS mailing list