For some data entry and update applications (especially graphical), users may often insert, delete, or change many cells in a data entry form and then ask for the data to be send to the database. For these situations of bulk insert, delete, or update, DB2 CLI provides an array input method to save the application from having to repeatedly execute the same INSERT, DELETE, or UPDATE statement. In addition, there is significant saving in network messaging.
To execute a piece of code using multiple sets of values (i.e. value sets) use; the executeSql:withParameterTypes:withParameterValueSets: connection method, the executeOn:withParameterValueSets statement method, or the executeWithParameterValueSets: connection descriptor method. An example of each is provided in the following example.
connection executeSql: 'INSERT INTO TEMP VALUES (?, ?)' withParameterTypes: (Array with: (UtyDB2IntegerType new) with: (UtyDB2VarCharType precision: 30)) withParameterValueSets: #( (1 'one') (2 'two') (3 'three') ). sd := connection newSqlDescriptor sql: 'INSERT INTO TEMP VALUES ( ?, ?)'; parameterTypes: (Array with: (UtyDB2IntegerType new) with: (UtyDB2VarCharType precision: 30)); yourself. sd executeOn: connection withParameterValueSets: #( (4 'four') (5 'five') (6 'size') ). stmt := sd prepareOn: connection. stmt executeWithParameterValueSets: #( (7 'seven') (8 'eight') (9 'nine') ).