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