Groovy's Sql broken after update to Ready API 3.1

[dejvid-smth]

I have a Test Case where I need to first insert a row into an Oracle DB and then I need to do something with it. To solve that I have used a Groovy script Test Step to insert a row using groovy.sql.Sql class and then retrieve the autogenerated column. 

In Ready API 3.0 this worked:

import groovy.sql.Sql

def driver = 'oracle.jdbc.driver.OracleDriver'
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver( driver )

def col = null
Sql.withInstance('what', 'ev', 'er', driver) { sql ->
	//below values are not really important, we just need some data
  	def val1 = 1
  	def val2 = 2
  	
  	String[] keys = ["COL"]
  	def result = sql.executeInsert ("""insert into MY_TABLE 
  		  (COL1, COL2) 
  	values ($val1 , $val2)""", keys)
  	col = result[0].COL
  	log.info("Inserted row with col=$col")
}

assert col != null

return col

However in Ready API 3.1 this gives me an error. After many tries I could not figure out how to do an insert with parameters and specifying column names to return. None of the overloads of executeInsert method worked. So I think it is a BUG that this code does not work in Ready API 3.1.

Is there any other suggested method of inserting rows into DB and retrieving values of columns generated by the DB ?

[richie]

Hey @dejvid-smth,

I've seen your other post regarding your issue with v3.1 on an oracle db, but on this post youre asking for alternatives to using a groovyscript to insert a row into a table.

I suspect you might be stuffed with v3.1 but,

Have you tried running an INSERT query via the JDBC step?

I'm not in front my laptop at the moment but i can remember theres a data gen JDBC option that might help as well as the normal JDBC step.

The other option that springs to mind that might be possible (depending on the system youre working on), is there a webservice endpoint that allows you to POST (INSERT) your data record into your table via an HTTP/ReST request?

Thats all i got....one of the other forum members might have some alternatives however,

Nice one,

Rich

[dejvid-smth]

Hi @richie , 

I have tried INSERT via JDBC but to my knowlege you can't get the value of generated column back from the insert via JDBC step or by using DataSource step. We also don't support creating this data through HTTP API unfortunately. 

[richie]

Hi @dejvid-smth 

sorry - for my understanding - can you clarify what you mean by "you can't get the value of generated column back from the insert via JDBC step"

I can see in your groovy - your 'return col' statement.  Are you inserting a row into a table and then perhaps a stored proc fires updating the value and your script previously retrieved this updated value (via 'return col')? or something like this? 

If my notion is somewhere in the vicinity of correct and considering your comment I quoted above - do you mean that if you apply the insert you would need an additional query to retrieve the updated column value?

you can separate multiple queries within a single JDBC step - oracle sql requires semi colons terminating everything unlike say sqlserver (but its not enforced in soapui) - but could you not just follow up your INSERT query with a relevant SELECT within the same JDBC test step?

or have I totally misunderstood and got it totally backwards?

Nice one,

rich

[sonya_m · Accepted solution]

Thank you richie!

Hi @dejvid-smth, have you managed to solve this? Do let us know

We can try to continue the investigaion here if it is still an issue - I see that richie requested additional clarifications from you.

Also, this might be a good idea to contact Support and describe the issue to them in detail.