FreeTDS Development Group

[Roger Reynolds <rogerr AT softix.com>]

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