Example - Teradata Preprocessor2

EXEC SQL BEGIN DECLARE SECTION;

   char   LOGON_STRING[15];

   long   ITEMCOUNT;
   long   ITEM;
   short  EMPIND;

EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SqlCA;

char  REQUEST_TYPE[9];
/**************************************************************/
/* DECLARE CURSOR                                                   */
/**************************************************************/

EXEC SQL DECLARE EMPCUR CURSOR FOR
          SELECT itemcount, item
            FROM UPSTB;

/**************************************************************/
/* ERROR CHECK                                                      */
/**************************************************************/

ERROR_CHECK()
 {
   if (SqlCA.SqlCode != 0)
    {
      printf("\n");
      printf("ERROR/WARNING DETECTED IN %s\n", REQUEST_TYPE);
      printf("   SQL CODE  : %d\n", SqlCA.SqlCode);
      printf("   ERROR CODE: %d\n", SqlCA.SqlErrd[0]);
    }
 }
/**************************************************************/
/* FETCH_CURSOR                                                     */
/**************************************************************/

FETCH_CURSOR()
 {
   strcpy(REQUEST_TYPE, "FETCH");

   EXEC SQL FETCH EMPCUR
             INTO :ITEMCOUNT,ITEM;
   if (SqlCA.SqlCode != 0)
      return;
   ERROR_CHECK();

   if (SqlCA.SqlCode == 0)
    {
      printf("\n");
      printf("UPSTB row   : %ld  | %d\n",ITEM,ITEMCOUNT);
    }
 }
/**************************************************************/
/* MAIN PROGRAM                                                     */
/**************************************************************/
main()
 {
   printf("EXECUTING \"upsert\" SQL: \n\n");
   printf("update upstb set itemcount=11 where item=11 else\n");
   printf("insert into upstb(11,11)...\n");

   /* LOGON to the DBS */

   strcpy(REQUEST_TYPE, "LOGON");

   strcpy(LOGON_STRING, "ssdb/weekly,weekly");

   EXEC SQL LOGON :LOGON_STRING;

   ERROR_CHECK();

   if (SqlCA.SqlCode != 0)
      return;

   /* OPEN the cursor */

   strcpy(REQUEST_TYPE, "OPEN");

   EXEC SQL OPEN EMPCUR;

   ERROR_CHECK();

   if (SqlCA.SqlCode == 0)
    {
      printf("\n");
      printf("BEFORE UPSERT: %d ROWS SELECTED\n", SqlCA.SqlErrd[2]);
    }
   /* Retrieve and display the rows before the "upsert" */

   while (SqlCA.SqlCode == 0)
    {
      FETCH_CURSOR();
      if (SqlCA.SqlCode != 0)
         break;
    }

   /* CLOSE the cursor */

   strcpy(REQUEST_TYPE, "CLOSE");

   EXEC SQL CLOSE EMPCUR;
   ERROR_CHECK();
   /* Now, perform the SQL "upsert" operation */

   strcpy(REQUEST_TYPE, "update");
   EXEC SQL update upstb set itemcount=11 where item=11 else
            insert into upstb(11,11);
   ERROR_CHECK();
   /* ReOPEN the cursor */
   strcpy(REQUEST_TYPE, "OPEN");

   EXEC SQL OPEN EMPCUR;

   ERROR_CHECK();

   if (SqlCA.SqlCode == 0)
    {
      printf("\n");
      printf("AFTER UPSERT: %d ROWS SELECTED\n", SqlCA.SqlErrd[2]);
    }
   /* Retrieve and display the rows after the "upsert" */

   while (SqlCA.SqlCode == 0)
    {
      FETCH_CURSOR();
      if (SqlCA.SqlCode != 0)
         break;
    }

   /* CLOSE the cursor */

   strcpy(REQUEST_TYPE, "CLOSE");

   EXEC SQL CLOSE EMPCUR;
   ERROR_CHECK();
   /* LOGOFF the DBS */
   strcpy(REQUEST_TYPE, "LOGOFF");

   EXEC SQL LOGOFF;

   ERROR_CHECK();
 }