Hi Karine,
I think Brice is a little confused as to what you meant by a stored procedure, so that example program isn't really what you're looking for. Unfortunately we don't have any example programs displaying stored procedures. The various stored procedure functions themselves do have code snippets however. This one is from DBSetParamInt:
/* This example works with Microsoft SQL Server. */
/* Create a stored procedure with input, output, */
/* and return value parameters. */
resCode = DBImmediateSQL (hdbc, "create proc sp_AdoTest(@InParam int, @OutParam int OUTPUT ) \
AS SELECT * FROM meas WHERE val1 >= @InParam \
SET @OutParam = @InParam * 2 \
RETURN @InParam + 10");
/* Prepare a statement that calls the stored procedure. */
resCode = DBSetAttributeDefault (hdbc,
ATTR_DB_COMMAND_TYPE, DB_COMMAND_STORED_PROC);
hstmt = DBPrepareSQL (hdbc, "sp_AdoTest");
resCode = DBSetAttributeDefault (hdbc,
ATTR_DB_COMMAND_TYPE, DB_COMMAND_UNKNOWN);
/* Refresh the parameters from the stored procedure. */
resCode = DBRefreshParams (hstmt);
/* Set the input parameter. */
resCode = DBSetParamInt (hstmt, 2, 10);
/* Execute the statement. */
resCode = DBExecutePreparedSQL (hstmt);
while ((resCode = DBFetchNext (hstmt)) == DB_SUCCESS)
{
/* Process records returned by the stored procedure.
*/
}
/* Close the statement. Output parameters are invalid */
/* until you close the statement. */
resCode = DBClosePreparedSQL (hstmt);
/* Examine the parameter values. */
resCode = DBGetParamInt (hstmt, 1, &retParam);
resCode = DBGetParamInt (hstmt, 2, &inParam);
resCode = DBGetParamInt (hstmt, 3, &outParam);
/* Discard the statement. */
hstmt = DBDiscardSQLStatement (hstmt);
Now obviously you have your stored procedure already created so you can skip the first DBImmediateSQL call which creates the stored procedure, but you'll still have to set the command types, "prepare" the SQL and refresh the params. You'd use multiple DBSetParamChar calls to pass in the ZUserID, ZUserName, ZPassword, etc. and then DBSetParamInt for ZUserGroup, ZValidity, .... You'll notice that the SQL toolkit does not have functions for "bit" or "boolean" datatypes built in. However to set your ZUserActive and ZVerifier params, you should be able to use the DBSetParamVariant function. Just declare two variants, and then do something like:
CA_VariantSetBool(&varActive, 1);
CA_VariantSetBool(&varVerifier, 0);
DBSetParamVariant(hstmt, 5, varActive);
DBSetParamVariant(hstmt, 10, varVerifier);
Once you have all your parameters set up, just call DBExecutePreparedSQL and everything should be good. One thing to note, the parameter Index for these functions is 1-based, but I'm pretty sure that the 1-index is always going to be the return value, even if your stored procedure doesn't return anything, so when setting your ZUserID it should start at index 2 and continue from there. I'm going off of memory on that, so if it's wrong I apologize.
At the moment I don't have a working database system set up to test this on, but if you need more help I can do that. Stored Procedures are pretty advanced concepts, so don't hesitate to ask.
Kevin B.
