Create a registered procedure in Open Server.
sp_regcreate proc_name, parm1, parm2, ...
The value of proc_name specifies the name of the registered procedure to be created.
(Optional) If the client application passes additional parameters, they specify the names, datatypes, and default values of the new procedure’s parameters.
Calling sp_regcreate from a Client-Library Client
This example creates a registered procedure np_test that takes parameters:
@p1, datatype CS_INT, no default value (that is, the value defaults to NULL)
@p2, datatype CS_CHAR, default value is “No value given”
@p3, datatype CS_INT, default value is 0 (zero)
The fragment contains code for functions np_create, which creates the procedure, and rpc_results, which handles the results of the RPC command. The function ex_fetch_data (called by rpc_results) is not shown here. This function is defined in the file exutils.c in the Client-Library sample programs.
/*
** np_create() -- Example function to create a notification
** procedure on an Open Server.
**
** Parameters:
** cmd - Command handle for sending commands.
**
** Returns:
** CS_SUCCEED - The notification procedure was successfully
** created.
** CS_FAIL - Couldn't do it. This routine fails if the
** registered procedure already exists.
*/
CS_RETCODE np_create(cmd)
CS_COMMAND *cmd;
{
CS_DATAFMT datafmt;
CS_INT intval;
CS_CHAR charbuf[512];
CS_BOOL ok = CS_TRUE;
/*
** Build up an RPC command to create the notification
** procedure np_test, defined as follows:
** np_test @p1 = <integer value>,
** @p2 = <character value>,
** @p3 = <integer value>
*/
if (ok
&& (ct_command(cmd, CS_RPC_CMD,
"sp_regcreate", CS_NULLTERM,
CS_UNUSED) != CS_SUCCEED))
ok = CS_FALSE;
/*
** Name of the created procedure will be 'np_test'.
*/
strcpy(datafmt.name, "proc_name");
datafmt.namelen = strlen(datafmt.name);
datafmt.datatype = CS_CHAR_TYPE;
datafmt.status = CS_INPUTVALUE;
datafmt.maxlength = 255;
strcpy(charbuf, "np_test");
if (ok &&
ct_param(cmd, &datafmt,
(CS_VOID *)charbuf, strlen(charbuf), 0)
!= CS_SUCCEED)
{
fprintf(stdout, "np_create: ct_param() @proc_name failed\n");
ok = CS_FALSE;
}
/*
** First parameter is named '@p1', is integer type, and has
** no default (i.e., defaults to NULL). We pass -1 as the
** indicator to ct_param() to specify a NULL value.
*/
strcpy(datafmt.name, "@p1");
datafmt.namelen = strlen(datafmt.name);
datafmt.datatype = CS_INT_TYPE;
datafmt.status = CS_INPUTVALUE;
datafmt.maxlength = CS_UNUSED;
if (ok &&
ct_param(cmd, &datafmt, (CS_VOID *)NULL, CS_UNUSED, -1)
!= CS_SUCCEED)
{
fprintf(stdout, "np_create: ct_param() @p1 failed\n");
ok = CS_FALSE;
}
/*
** Second parameter is named '@p2', is character type, and has
** default “No value given”.
*/
strcpy(datafmt.name, "@p2");
datafmt.namelen = strlen(datafmt.name);
datafmt.datatype = CS_CHAR_TYPE;
datafmt.status = CS_INPUTVALUE;
datafmt.maxlength = 255;
strcpy(charbuf, "No value given");
if (ok &&
ct_param(cmd, &datafmt,
(CS_VOID *)&charbuf, strlen(charbuf), 0)
!= CS_SUCCEED)
{
fprintf(stdout, "np_create: ct_param() @p2 failed\n");
ok = CS_FALSE;
}
/*
** Third parameter is named '@p3', is integer type, and
** has default 0 (zero).
*/
strcpy(datafmt.name, "@p3");
datafmt.namelen = strlen(datafmt.name);
datafmt.datatype = CS_INT_TYPE;
datafmt.status = CS_INPUTVALUE;
datafmt.maxlength = CS_UNUSED;
intval = 0;
if (ok &&
ct_param(cmd, &datafmt, (CS_VOID *)&intval, CS_UNUSED, 0)
!= CS_SUCCEED)
{
fprintf(stdout, "np_create: ct_param() @p3 failed\n");
ok = CS_FALSE;
}
/*
** Send the RPC command.
*/
if (ok && ct_send(cmd) != CS_SUCCEED)
ok = CS_FALSE;
/*
** Process the results from the RPC execution.
*/
if (ok && rpc_results(cmd, CS_FALSE) != CS_SUCCEED)
ok = CS_FALSE;
return (ok ? CS_SUCCEED : CS_FAIL);
} /* np_create */
/*
** rpc_results() -- Process results from an rpc.
**
** Parameters
** cmd -- The command handle with results pending.
** expect_fetchable -- CS_TRUE means fetchable results
** are expected. They will be printed w/ the
** ex_fetch_data() routine (defined in file exutils.c).
** CS_FALSE means fetchable results cause this routine
** to fail.
**
** Returns
** CS_SUCCEED -- no errors.
** CS_FAIL -- ct_results failed, returned a result_type value
** of CS_CMD_FAIL, or returned unexpected fetchable results.
*/
CS_RETCODE rpc_results(cmd, expect_fetchable)
CS_COMMAND *cmd;
CS_BOOL expect_fetchable;
{
CS_RETCODE results_ret;
CS_INT result_type;
CS_BOOL ok = CS_TRUE;
CS_BOOL cmd_failed = CS_FALSE;
while (ok &&
(results_ret
= ct_results(cmd, &result_type))
== CS_SUCCEED)
{
switch((int)result_type)
{
case CS_STATUS_RESULT:
case CS_ROW_RESULT:
case CS_COMPUTE_RESULT:
case CS_PARAM_RESULT:
/*
** These cases indicate fetchable results.
*/
if (expect_fetchable)
{
/* ex_fetch_data() is defined in exutils.c */
ok = (ex_fetch_data(cmd) == CS_SUCCEED);
}
else
{
(CS_VOID)fprintf(stdout,
"RPC returned unexpected result\n");
(CS_VOID)ct_cancel(NULL, cmd, CS_CANCEL_ALL);
ok = CS_FALSE;
}
break;
case CS_CMD_SUCCEED:
case CS_CMD_DONE:
/* No action required */
break;
case CS_CMD_FAIL:
(CS_VOID)fprintf(stdout,
"RPC command failed on server.\n");
cmd_failed = CS_TRUE;
break;
default:
/*
** Unexpected result type.
*/
(CS_VOID)fprintf(stdout,
"RPC returned unexpected result\n");
(CS_VOID)ct_cancel(NULL, cmd, CS_CANCEL_ALL);
ok = CS_FALSE;
break;
} /* switch */
} /* while */
switch((int) results_ret)
{
case CS_END_RESULTS:
case CS_CANCELED:
break;
case CS_FAIL:
default:
ok = 0;
}
return ((ok && !cmd_failed) ? CS_SUCCEED : CS_FAIL);
} /* rpc_results() */
Calling sp_regcreate from a DB-Library Client
This example creates a registered procedure named pricechange with two parameters. The first parameter is @current_price and is represented using the SYBMONEY datatype. The second parameter is @sequence_num and is a SYBINT4 datatype. Neither parameter has a default value.
dbnpdefine(dbproc, "pricechange", DBNULLTERM);
dbregparam(dbproc, "@current_price", DBNULLTERM,
SYBMONEY, DBNODEFAULT, NULL);
dbregparam(dbproc, "@sequence_num", DBNULLTERM,
SYBINT4, DBNODEFAULT, NULL);
status = dbnpcreate(dbproc);
if (status == FAIL)
{
fprintf(stderr,
"Could not create pricechange procedure.\n");
}
Table 4-3 summarizes the calls a SRV_C_PROCEXEC callback handler would use to find that the pricechange procedure is being registered:
Function call |
Returns |
|---|---|
srv_procname(srvproc, (int *) NULL) |
“sp_regcreate” |
srv_rpcparams(srvproc) |
3 |
srv_paramdata(srvproc, 1) |
“pricechange” |
srv_paramdata(srvproc, 2) |
“@current_price” |
srv_paramdata(srvproc, 3) |
“@sequence_num” |
Client applications call sp_regcreate remotely to create registered procedures.
Registered procedures that are created by a client application are called notification procedures. They cannot contain application-defined code, and are primarily useful for client applications that rely on registered-procedure notifications.
sp_regcreate’s first parameter (proc_name) is the name of the procedure to create. If the new registered procedure takes parameters, they are defined by passing additional parameters. The new procedure’s first parameter is passed as sp_regcreate’s second parameter, the second as sp_regcreate’s third, and so forth.
Client applications built with Client-Library can create registered procedures by sending an RPC command that invokes sp_regcreate.
An example is provided in “Calling sp_regcreate from a Client-Library Client” on page 418.
DB-Library programs create registered procedures using dbnpdefine, dbregparam, and dbnpcreate. dbnpdefine internally generates an RPC command to remotely call sp_regcreate. dbnpcreate sends the RPC and processes the results.
An example is provided in “Calling sp_regcreate from a DB-Library Client” on page 423.
Server-Library programs can create registered procedures using srv_regdefine, srv_regparam, and srv_regcreate.
sp_regcreate can return the following messages:
Number |
Severity |
Text |
|---|---|---|
16505 |
0 |
Procedure was registered successfully. |
16506 |
11 |
Procedure is already registered. |
16507 |
11 |
Unable to register procedure. |
sp_regdrop, sp_regnowatch, sp_regwatch, srv_regdefine, srv_regexec, srv_reginit, srv_regparam
