Inserting 10000 rows of data in database
Hi all,
Goal: Use groovy to insert several rows in the database. Here I have to insert 10000 zipcodes with validity true or false in oracle database.
Achieved: Know to insert one row
Following is the code
import groovy.sql.Sql
import java.sql.SQLException
import com.eviware.soapui.support.GroovyUtils.*
try {
def dbURL = context.expand('${#Project#ORACLE12c}')
def dbUser = context.expand('${#Project#SchemaUser}')
def dbPass = context.expand('${#Project#Pass}')
def dbDriver = context.expand('${#Project#ORACLEDriver}')
def dbconf = context.expand('${#Project#SchemaConf}')
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver(dbDriver)
def sql = Sql.newInstance(dbURL, dbUser, dbPass, dbDriver)
sql.execute("DELETE FROM "+dbconf+".ZipCodes WHERE VALID='true'")
sql.execute("INSERT INTO "+dbconf+".ZipCodes (ZIPCODE, VALID) VALUES ('71232', 'true')");
}
catch (SQLException e){
def tmp = e.getSQLState ()
log.error (tmp, e)
}How to insert several rows using groovy teststep or even JDBC teststep? Pls ignore the syntax error, if any.
Define the zip codes and valid into a file as show below:
data.txt
zip, valid
12345, true
23456, false
Read the above data.txt file in groovy script and insert the data.
import groovy.sql.Sql import java.sql.SQLException import com.eviware.soapui.support.GroovyUtils.* //Define the file path of your data with zip and valid def fileName = '/absolute/path/to/data.txt' try { def dbURL = context.expand('${#Project#ORACLE12c}') def dbUser = context.expand('${#Project#SchemaUser}') def dbPass = context.expand('${#Project#Pass}') def dbDriver = context.expand('${#Project#ORACLEDriver}') def dbconf = context.expand('${#Project#SchemaConf}') com.eviware.soapui.support.GroovyUtils.registerJdbcDriver(dbDriver) def sql = Sql.newInstance(dbURL, dbUser, dbPass, dbDriver) def lines = new File(fileName).readLines() ? lines.eachWithIndex { line, index -> if (index) { def data = line.split(',')*.trim() //sql.execute("DELETE FROM "+dbconf+".ZipCodes WHERE VALID='true'") sql.execute("INSERT INTO ${dbconf}.ZipCodes (ZIPCODE, VALID) VALUES ('${data[0]}','${data[1]}')"); } } } catch (SQLException e){ def tmp = e.getSQLState () log.error (tmp, e) }
