Nonstop Database, HiRDB Version 9 UAP Development Guide
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); }
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.