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:

• C1: INTEGER NOT NULL (INDEX)
• C2: BLOB (100M) NOT NULL

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

Organization of this subsection

(1) Note