PlugIns JdbcSlim UserGuide 4TheMappingBetweenTestDataAndCommands StoredProcedures
To avoid side effects we execute the test in a transaction
| Script | SQLCommand | TransactionDatabase |
| open Connection | ||
| execute | begin transaction | |
Input Parameters
To use Input Parameters you have to define a tuple of 3-4 values for each input column.
The values are separated with a ":".
1. Flag for Input or Output Parameter: I* = input parameter, O* = output parameter, I*O* = input and output parameter
2. Position in the statement, the first parameter has number 1
3. Integer number of SQL Type see the jdk java documentation or look here
4. The scale of the data type, might be required for VARCHAR, DECIMAL, etc. See the jdbc documentation of your database system. This flag is optional
These flags are defined in a separate Properties definition.
| Define Properties | inParameters |
| key | value |
| FILL | I:3:12:0 |
| No | in:1:8:0 |
| inStr | Input:1:12 |
| c | i:2:4 |
Link this definition to the query with the dbQueryParameters key
| SQLCommand | TransactionDatabase | select SQRT(?) as root | dbQueryParameters=inParameters |
| No | root? | ||
| 9.0 | 3.0 | ||
| 16.0 | 4.0 | ||
| SQLCommand | TransactionDatabase | select LPAD(?, ?, ?) as myString | dbQueryParameters=inParameters |
| inStr | c | FILL | myString? |
| xYz | 7 | * | ****xYz |
| ABC | 5 | - | --ABC |
Or define the parameters inline in the cmd
| SQLCommand | TransactionDatabase | select LPAD(?{inStr:12}, ?{c:4}, ?{FILL:12}) as myString | |
| inStr | c | FILL | myString? |
| xYz | 7 | * | ****xYz |
| ABC | 5 | - | --ABC |
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 | #null# | London | Butler |
| 7 | Bert | 432 | Washington | President |
| 8 | Kim | 1 | New York | Banker |