PlugIns JdbcSlim UserGuide 4TheMappingBetweenTestDataAndCommands InsertUpdateDelete
Some jdbc driver support multiple commands separated with ; in a single execution.
It would be nice to be able to do a combination of select and update statements
*select - to see the current status
*update - to modify data
*select - to validate the new status
To avoid side effects we execute the test in a transaction
| Script | SQLCommand | TransactionDatabase |
| open Connection | ||
| execute | begin transaction | |
| Define Properties | SelectChain |
| key | value |
| .include | TransactionDatabase |
| CMD | select City as OldCity from TestData where ID ='%ID%'; update TestData set City='%NewCITY%' where ID ='%ID%'; select City as FinalCity from TestData where ID ='%ID%' |
| debug |
| SQLCommand | SelectChain | |||
| ID | OldCity? | NewCITY | FinalCity? | Count? |
| 6 | London | +Prag+ | +Prag+ | 1 |
Verify all updates
| SQLCommand | SelectChain | select * from TestData | Query SUBQUERY | |
| ID< | Name | Phone | City | Profession |
| 6 | James | 5432 | +Prag+ | Butler |
Rollback the change to not impact future tests
| Script | SQLCommand | TransactionDatabase |
| open Connection | ||
| execute | rollback | |
| close Connection | ||
Check that the rollback worked
| SQLCommand | StartupDatabase | select * from TestData | Query | |
| ID | Name | Phone | City | Profession |
| 1 | Bill | 12345 | Berlin | Doctor |
| 2 | Ben | 6789 | Denver | Artist |
| 3 | Tom | 4567890 | Seoul | Milkman |
| 4 | Till | 332211 | Berlin | Senator |
| 5 | Sarah | 999999 | Paris | Hero |
| 6 | James | 5432 | London | Butler |
| 7 | Bert | 432 | Washington | President |
| 8 | Kim | 1 | New York | Banker |