Example: DB2® Stored Procedure in C

The following is an example of a DB2® stored procedure written in C for the DB2® CLI preprocessor. This is the format that would be required for the Open event of an Advanced RealTime Virtual Fields activity assuming that the key field is called NUMBER1 and the data fields are called NUMBER2, TEXT1, and TEXT2. In this context, the key field is the input parameter, and the result set is expected to include the data fields followed by the key field. Note that the input parameter data type is checked and handled for both integer and float types.

SQL_API_RC SQL_API_FN sf_proc (void *reserved1,
                               void *reserved2,
                               struct sqlda *inout_sqlda,
                               struct sqlca *ca)
{
  /* Declare a local SQLCA */
  EXEC SQL INCLUDE SQLCA;
 
  /* Declare Host Variables */
  EXEC SQL BEGIN DECLARE SECTION;
    char *select_stmt  = "SELECT NUMBER2, TEXT1, TEXT2, NUMBER1 FROM TALBENAME WHERE NUMBER1 = ?";
    long int number1;
  EXEC SQL END DECLARE SECTION;
 
  if (inout_sqlda)
  {
    if ((inout_sqlda->sqlvar[0].sqltype == SQL_TYP_INTEGER) ||
        (inout_sqlda->sqlvar[0].sqltype == SQL_TYP_NINTEGER))
      number1 = *((long int *) (inout_sqlda->sqlvar[0].sqldata));
    else if ((inout_sqlda->sqlvar[0].sqltype == SQL_TYP_FLOAT) ||
             (inout_sqlda->sqlvar[0].sqltype == SQL_TYP_NFLOAT))
      number1 = (long int) *((double *) (inout_sqlda->sqlvar[0].sqldata));
    else
      number1 = 0;

    EXEC SQL WHENEVER SQLERROR GOTO err;
    EXEC SQL PREPARE stmt FROM :select_stmt;
    EXEC SQL DECLARE curs CURSOR FOR stmt;
    EXEC SQL OPEN curs using :number1;
  }
 
err:
  memcpy(ca, &sqlca, sizeof (struct sqlca));
  if (inout_sqlda)
    *(inout_sqlda->sqlvar[0].sqlind) = -128;
 
  return(SQLZ_DISCONNECT_PROC);
}