FreeTDS Development Group

["Rogers, Tom" <tom.rogers AT ccur.com>]

I have used ODBC and FreeTDS with Stored Procs quite a bit, but I must say I
have never tried to do exactly what you are doing; which is to pass in only
one of n parameters to the stored procedure and hard code the rest in the
SQL statement.  I have ONLY ever passed in all as bound parameters and that
always works, even with output params.

I suspect that it's a bug in either ODBC or FreeTDS in assuming that your
bound parameter 1 will always be the stored procs parameter 1 (and 2 is 2,
etc).  I could be wrong and haven't tried the code, but I suspect that it is
binding your single bound parameter to the 1st and not the 3rd stored proc
parameter.  If this is correct, than a work-around might be for you to bind
your parameter as #3?

Have you tried that?

Thomas Rogers
System Engineer
Concurrent Computer Corporation
> phone: 215.712.7422 x7261
> 
> 
The information contained herein is proprietary and confidential and should
be treated in accordance with the Non-Disclosure Agreement and any
Amendments to said Agreement executed between the discloser and recipient.
If the reader of this message is not the intended recipient, you are hereby
notified that any dissemination, distribution, or copy of this information
is strictly prohibited.  If you have received this information in error,
please immediately notify discloser, and then destroy the information
contained herein.  Thank you.


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org]On Behalf Of Roger Reynolds
Sent: Thursday, April 22, 2004 3:25 AM
To: FreeTDS Development Group
Subject: [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...

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

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