Skip to Content.
Sympa Menu

freetds - [freetds] Stored procedure output parameter binding

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Michael Gurfinkel [TSS]" <michael AT tsswireless.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: [freetds] Stored procedure output parameter binding
  • Date: Thu, 15 Sep 2011 16:48:26 -0700


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.






Archive powered by MHonArc 2.6.24.

Top of Page