cancel
Showing results for 
Search instead for 
Did you mean: 

Groovy's Sql broken after update to Ready API 3.1

SOLVED
Highlighted
New Contributor

Groovy's Sql broken after update to Ready API 3.1

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 ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Manager

Re: Groovy's Sql broken after update to Ready API 3.1

Thank you richie!

 

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

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.


Sonya Mihaljova
Community and Education Specialist

 Join the virtual SmartBear Connect user conference on April 27-28! Get your free virtual ticket today!

View solution in original post

4 REPLIES 4
Community Hero

Re: Groovy's Sql broken after update to Ready API 3.1

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
New Contributor

Re: Groovy's Sql broken after update to Ready API 3.1

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. 

Highlighted
Community Hero

Re: Groovy's Sql broken after update to Ready API 3.1

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

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Community Manager

Re: Groovy's Sql broken after update to Ready API 3.1

Thank you richie!

 

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

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.


Sonya Mihaljova
Community and Education Specialist

 Join the virtual SmartBear Connect user conference on April 27-28! Get your free virtual ticket today!

View solution in original post