Return a pointer to a return parameter value generated by a stored procedure.
BYTE *dbretdata(dbproc, retnum) DBPROCESS *dbproc; int retnum;
A pointer to the DBPROCESS structure that provides the connection for a particular front-end/server process. It contains all the information that DB-Library uses to manage communications and data between the front end and server.
The number of the return parameter value of interest. The first return value is 1. Values are returned in the same order as the parameters were originally specified in the stored procedure’s create procedure statement. (Note that this is not necessarily the same order as specified in the remote procedure call.) When specifying retnum, non-return parameters do not count. For example, if the second parameter in a stored procedure is the only return parameter, its retnum is 1, not 2.
A pointer to the specified return value. If retnum is out of range, dbretdata returns NULL. To determine whether the data really has a null value (and retnum is not merely out of range), check for a return of 0 from dbretlen.
dbretdata returns a pointer to a return parameter value generated by a stored procedure. It is useful in conjunction with remote procedure calls and execute statements on stored procedures.
Transact-SQL stored procedures can return values for specified “return parameters.” Changes made to the value of a return parameter inside the stored procedure are then available to the program that called the procedure. This is analogous to the “pass by reference” facility available in some programming languages.
For a parameter to function as a return parameter, it must be declared as such within the stored procedure. The execute statement or remote procedure call that calls the stored procedure must also indicate that the parameter should function as a return parameter. In the case of a remote procedure call, it is the dbrpcparam routine that specifies whether a parameter is a return parameter.
When executing a stored procedure, the server returns any parameter values immediately after returning all other results. Therefore, the application can call dbretdata only after processing the stored procedure’s results by calling dbresults, as well as dbnextrow if appropriate. (Note that a stored procedure can generate several sets of results—one for each select it contains. Before the application can call dbretdata or any other routines that process return parameters, it must call dbresults and dbnextrow as many times as necessary to process all the results.)
If a stored procedure is invoked with a remote procedure call, the return parameter values are automatically available to the application. If, on the other hand, the stored procedure is invoked with an execute statement, the return parameter values are available only if the command batch containing the execute statement uses local variables, not constants, for the return parameters.
The routine dbnumrets indicates how many return parameter values are available. If dbnumrets returns less than or equal to 0, no return parameter values are available.
When a stored procedure is invoked with an RPC command (using dbrpcinit, dbrpcparam, and dbrpcsend), then the return parameter values can be retrieved after all other results have been processed. For an example of this usage, see the sample program example8.c.
When a stored procedure has been executed from a batch of Transact-SQL commands (with dbsqlexec or dbsqlsend), then other commands might execute after the stored procedure. This situation makes retrieval of return parameter values a little more complicated.
If you are sure that the stored procedure command is the only command in the batch, then you can retrieve the return parameter values after the dbresults loop, as shown in the sample program example8.c.
If the batch can contain multiple commands, then the return parameter values should be retrieved inside the dbresults loop, after all rows have been fetched with dbnextrow. The code below shows where the return parameters should be retrieved in this situation.
while ( (result_code = dbresults(dbproc)
!= NO_MORE_RESULTS)
{
if (result_code == SUCCEED)
{
... bind rows here ...
while ((row_code = dbnextrow(dbproc))
!= NO_MORE_ROWS)
{
... process rows here ...
}
/* Now check for a return status */
if (dbhasretstat(dbproc) == TRUE
{
printf(“(return status %d)\n”,
dbretstatus(dbproc));
}
/* Now check for return parameter values */
if (dbnumrets(dbproc) > 0)
{
... retrieve output parameters here ...
}
} /* if result_code */
else
{
printf(“Query failed.\n”);
}
} /* while dbresults */
The routines below are used to retrieve return parameter values:
dbnumrets returns the total number of return parameter values.
dbretlen returns the length of a parameter value.
dbretname returns the name of a parameter value.
dbrettype returns the datatype of a parameter value.
dbconvert converts the value to another datatype, if necessary.
The code fragment below shows how these routines are used together:
char dataval[512];
char *dataname;
DBINT datalen;
int i, numrets;
numrets = dbnumrets(dbproc);
for (i = 1; i <= numrets; i++)
{
dataname = dbretname(dbproc, i);
datalen = dbretlen(dbproc, i);
if (datalen == 0)
{
/* The parameter's value is NULL */
strcpy(dataval, "NULL");
}
else
{
/*
** Convert to char. dbconvert appends a null
** terminator because we pass the last
** parameter, destlen, as -1.
*/
result = dbconvert(dbproc,
dbrettype(dbproc, i),
dbretdata(dbproc, i), datalen,
SYBCHAR, (BYTE *)dataval, -1);
} /* else */
/* Now print out the converted value */
if (dataname == NULL || *dataname == '\0')
printf("\t%s\n", dataval);
else
printf("\t%s: %s\n", dataname, dataval);
}
dbnextrow, dbnumrets, dbresults, dbretlen, dbretname, dbrettype, dbrpcinit, dbrpcparam
