Using UPSERT in Embedded SQL
This program illustrates the use of the upsert SQL request (update, if target row exists, else insert new row) in a PP2 application program.
Example
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();
}