DML with results

The driver supports the Microsoft SQL Server Output clause for Insert, Update, and Delete statements. For example, suppose you created a table with the following statement:

CREATE TABLE table1(id int, name varchar(30))

The following Update statement updates the values in the id column of table1 and returns a result set that includes the old ID (replaced by the new ID), the new ID, and the name associated with these IDs:

UPDATE table1 SET id=id*10 OUTPUT deleted.id as oldId, inserted.id as
newId, inserted.name

The driver returns the results of Insert, Update, or Delete statements and the update count in separate result sets. The output result set is returned first, followed by the update count for the Insert, Update, or Delete statement. To execute DML with Results statements in an application, use the Statement.execute() or PreparedStatement.execute() method. Then, use Statement.getMoreResults () to obtain the output result set and the update count. For example:

String sql = "UPDATE table1 SET id=id*10 OUTPUT deleted.id as oldId, " +
   "inserted.id as newId, inserted.name";
boolean isResultSet = stmt.execute(sql);
int   updateCount = 0;
while (true) {
   if (isResultSet) {
         resultSet = stmt.getResultSet();
         while (resultSet.next()) {
            System.out.println("oldId: " + resultSet.getInt(1) +
                               "newId: " + resultSet.getInt(2) +
                               "name: " + resultSet.getString(3));
         }
         resultSet.close();
   }
   else {
         updateCount = stmt.getUpdateCount();
         if (updateCount == -1) {
            break;
         }
         System.out.println("Update Count: " + updateCount);
   }
   isResultSet = stmt.getMoreResults();
}