FreeTDS Development Group

["Michael Gurfinkel [TSS]" <michael AT tsswireless.com>]

After reading documentation of dbrpcparam I thought that if 3rd parameter
passed to it (BYTE status) is set to DBRPCRETURN then the local variable
bound to it will get the actual value placed into it by stored proc, but
that is not working for me. In my example I have to run dbretdata to
actually retrieve the value. Then what is the use of passing DBRPCRETURN ?
Please clarify.

For illustration I show my code snippet along with resulting output. For
brevity I skip login/close etc...

int     run_sp(DBPROCESS *dbproc)
{
  RETCODE       rc = 0,
                res_rc = 0;

  const char    *sp_name        = "getSessID";
  int           sess_id         = 99999;

        rc = dbrpcinit(dbproc, sp_name, 0);
        if(rc == FAIL)
        {
                printf("dbrpcinit failed\n");
                return(FAIL);
        }
        printf("dbrpcinit \t rc=%i\n", rc);

        rc = dbrpcparam(dbproc, "@SessID", DBRPCRETURN, SYBINT4, -1, -1,
                        (BYTE *)&sess_id);
        if(rc == FAIL)
        {
                printf("dbrpcparam failed\n");
                return(FAIL);
        }
        printf("dbrpcparam \t rc=%i\n", rc);

        if((rc = dbrpcsend(dbproc)) == FAIL)
        {
                printf("dbrpcsend failed\n");
                return(FAIL);
        }
        printf("dbrpcsend \t rc=%i\n", rc);

        if((rc = dbsqlok(dbproc)) == FAIL)
        {
                printf("dbsqlok 1 failed\n");
                return(FAIL);
        }
        printf("dbsqlok \t rc=%i\n", rc);

        while((res_rc = dbresults(dbproc)) != NO_MORE_RESULTS)
        {
                printf("dbresults \t rc=%i\n", res_rc);

                while((rc = dbnextrow(dbproc)) != NO_MORE_ROWS)
                {
                        printf("\t dbnextrow \t rc=%i\n", rc);
                        if(rc == FAIL)
                                return(FAIL);
                        printf("\t\tsome unexpected data here\n");
                }
                printf("\t\t after the dbnextrow loop rc=%i\n", rc);
        }

        printf("At The End <<<res_rc = %i, rc=%i>>>\n", res_rc, rc);

        if(res_rc == NO_MORE_RESULTS)
        {
                printf("\tNO MORE RESULTS\n");
                printf("\t===============\n");
                printf("dbhasretstat = %i\n", dbhasretstat(dbproc));
                printf("dbnumrets    = %i\n", dbnumrets(dbproc));
                printf("dbretname    = %s\n", dbretname(dbproc, 1));
                printf("dbrettype    = %i\n", dbrettype(dbproc, 1));
                printf("dbretdata    = %i\n", *( (int *)dbretdata(dbproc, 1)
));
        }
        printf("SESS_ID=%i\n", sess_id);
        return(res_rc);
}



The stored procedure itself is here:
CREATE PROCEDURE [dbo].[getSessID]
        @SessID int     OUTPUT
AS
BEGIN
        SET NOCOUNT ON;
        SELECT @SessID = @@SPID
END


The output is:
dbrpcinit        rc=1
dbrpcparam       rc=1
dbrpcsend        rc=1
dbsqlok          rc=1
dbresults        rc=1
                 after the dbnextrow loop rc=-2 At The End <<<res_rc = 2,
rc=-2>>>
        NO MORE RESULTS
        ===============
dbhasretstat = 1
dbnumrets    = 1
dbretname    = @SessID
dbrettype    = 56
dbretdata    = 59
SESS_ID=99999

As you can see, my intention was to get session_id in my SESS_ID variable,
but it did not happen. Can I get the output value straight into the local
var? or I have to use dbretdata?

My setup: linux, TDS 0.92, db is SQL 2005, tds version is set to 7.1

Thanks in advance.