This example replaces only the first 400 kilobytes starting from a certain binary data column (search_data) of the data in column C2 of row C1=1 in table T1 with other data (change_data). The result is inserted into table T1 in column C2 of a new row (C1=2).
The data types of the columns in table T1 are shown below:
void abnormalend(void);
main()
{
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB AS LOCATOR alldata_loc; /* Locator representing all data */
long change_pos; /* Change start position */
SQL TYPE IS BLOB(10) search_data; /* Binary data column to be searched */
SQL TYPE IS BLOB(400K) change_data; /* Binary data column to be changed */
SQL TYPE IS BLOB AS LOCATOR enddata_loc; /* Locator representing data */
/* that follows section to be changed */
long pos;
EXEC SQL END DECLARE SECTION;
-------(CONNECT process to HiRDB (omitted)) -------
-------(Settings for binary data column to be searched (omitted))-------
-------(Settings for binary data column to be changed (omitted))-------
EXEC SQL WHENEVER SQLERROR PERFORM abnormalend;
/* Use locator to get column data */
EXEC SQL SELECT C2 INTO :alldata_loc FROM T1 WHERE C1 = 1;
/* Get start position that includes binary data to be searched */
EXEC SQL SET :change_pos = POSITION(:search_data AS BLOB(10)
IN :alldata_loc AS BLOB(100M));
pos = change_pos + 409600;
/* Use locator to get data that follows changed portion */
EXEC SQL SET :enddata_loc = SUBSTR(:alldata_loc AS BLOB(100M), :pos);
pos = change_pos -1;
/* Use locator to insert data in front of changed section */
EXEC SQL INSERT INTO T1 VALUES(2, SUBSTR
(:alldata_loc AS BLOB(100M), 1, :pos));
/* Locator representing all data is nullified because it is no longer necessary */
EXEC SQL FREE LOCATOR :alldata_loc;
/* Link data of changed section and update */
EXEC SQL UPDATE T1 SET C2 = C2 || :change_data WHERE C1 = 2;
/* Use locator to link data that follows changed section and update */
EXEC SQL UPDATE T1 SET C2 = C2 || :enddata_loc WHERE C1 = 2;
EXEC SQL COMMIT;
printf(" *** normally ended ***\n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL DISCONNECT;
return(0);
}
void abnormalend()
{
int wsqlcode;
wsqlcode = -SQLCODE;printf("\n*** HiRDB SQL ERROR SQLCODE
= %d \n", wsqlcode);
printf("SQLERRMC = %s\n", SQLERRMC);
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
exit(1);
}