15.10 - Using UPSERT in Embedded SQL - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

This program illustrates the use of the upsert SQL request (update, if target row exists, else insert new row) in a PP2 application program.

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();
 }