SOAPUI & Groovy Scripts that execute multiple SQL statements at a time

Question

I have some soapUI tests that use groovy scripts to insert some data into a table first

For this, I used the following code snippet:

def conn = context.dbConnEtopup conn.execute( "INSERT INTO A(ID, NAME) VALUES (1, "Johnny")" ) 

This works fine, however, I have many test scripts that now execute similar (if not the same) SQL statements, so I try to approach this by loading it from the properties file, so my actual SQL statement is only in one place, to simplify editing

However, my SQL statement that I am trying to use is actually 2 inserts (or deletes), so the loadable property is:

 DELETE * FROM TABLE_A; DELETE * FROM TABLE_B; 

conn.execute() cannot handle ; , which means that I can only use the first DELETE

How can I get around this? I do not want to load each property separately and execute them. Ideally, I just want one property, so I can add additional instructions for deletion in the future

Answer 1

Could you just save them in a semicolon property file and then delete them after reading them, for example.

 String sqlProperty = // read SQL property from file def statements = sqlProperty.split(";") // Execute each statment using conn (an instance of groov.sql.Sql?) statements.each { conn.execute(it); 

Answer 2

Some JDBC drivers support multiple statements, and this functionality will be available through the Groovy Sql class, for example. with MySql:

 def props = [user: 'myuser', password: 'mypassword', allowMultiQueries: 'true'] as Properties def url = 'jdbc:mysql://127.0.0.1:3306/mydb' def driver = 'com.mysql.jdbc.Driver' def sql = Sql.newInstance(url, props, driver) sql.execute """ insert into PERSON (id, firstname, lastname) values (1, 'Dierk', 'Koenig'); insert into PERSON (id, firstname, lastname) values (2, 'Guillaume', 'Laforge'); insert into PERSON (id, firstname, lastname) values (3, 'Jon', 'Skeet'); """ 

Answer 3

Sometimes it’s not very good. Check out my solution: Running multiple SQL statements from Groovy