0

I need to be able to execute an update SQL script, but it isn't working

Here is a link to the site that I used for reference: https://groovyinsoapui.wordpress.com/tag/sql-eachrow-groovy-soapui/

Here is the format of the code that I ended up writing (due to the nature of the work I am doing, I am unable to provide the exact script that I wrote)

import groovy.sql.Sql

def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
groovyUtils.registerJdbcDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")

def connectString = "jdbc:microsoft:sqlserver://:;databaseName=?user=&password="

sql = Sql.newInstance(connectString)  // TEST YOUR CONNECT STRING IN A SQL BROWSER

sql.executeUpdate("UPDATE TABLE SET COLUMN_1 = 'VALUE_1' WHERE COLUMN_2 = 'VALUE_2'")

The response that I am getting is:

Script-result: 0

I also tried to use:

sql.execute("UPDATE TABLE SET COLUMN_1 = 'VALUE_1' WHERE COLUMN_2 = 'VALUE_2'")

Which returns the following response:

Script-result: false
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
timx1981
  • 1
  • Check if it is helpful - http://stackoverflow.com/questions/42294903/groovysql-how-to-update-a-table-with-arraylist-variables/42296189#42296189 – Rao Feb 18 '17 at 10:00

1 Answers1

0

From what you say it seems that no row has COLUMN_2 = 'VALUE_2', so then number of updated rows is 0.

I would first check that statement on Management Studio just to make sure.

minus
  • 2,646
  • 15
  • 18
  • When I change my query a bit to: sql.execute """ SELECT * FROM TABLE WHERE COLUMN_1 = 'VALUE_2' """ I get: Script-result: true – timx1981 Feb 17 '17 at 21:02
  • Try `sql.eachRow(" SELECT * FROM TABLE WHERE COLUMN_1 = 'VALUE_2' "){println(it)}` and see what you get. – minus Feb 17 '17 at 21:10
  • Nothing appears to happen. No message at all. When I execute the following in SQL Server, I get the expected results: SELECT * FROM TABLE WHERE COLUMN_1 = 'VALUE_2' – timx1981 Feb 17 '17 at 21:13
  • So there are 0 row which match your condition. Try with a query for which you know there are result ... if your table is not too big try without the `where`. – minus Feb 17 '17 at 21:18
  • There is for sure a record that matches the criteria I've specified: SELECT * FROM TABLE WHERE COLUMN_1 = 'VALUE_2' I have verified it directly in SQL Server as well as in Soap using: def res = sql.firstRow("SELECT * FROM TABLE WHERE COLUMN_1 = 'VALUE_2'") – timx1981 Feb 17 '17 at 21:38
  • I don't think I can help you further without seeing the exact statement. Try the prepared statement version of the query, double check types, make sure you are connecting with the right instance / db / schema and so on. – minus Feb 17 '17 at 21:43
  • Here is as close as I can come to the real thing. Everything is the exact same as my actual script, with mocked values and parameters: import groovy.sql.Sql def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context) groovyUtils.registerJdbcDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver") def connectString = "jdbc:sqlserver://10.110.25.72:60094;databaseName=CUSTOM;user=svcId&password=svcPwd" sql = Sql.newInstance(connectString) sql.execute("UPDATE userInfo SET userStatus = 'active' WHERE userId = '1'") – timx1981 Feb 17 '17 at 21:58
  • maybe userid is an int and you are passing it as a string, remove the quotes. `userid = 1` instead of `userid = '1'` – minus Feb 17 '17 at 22:04
  • The below updates as expected when I run in SQL Server: UPDATE userInfo SET userStatus = 'active' WHERE userId = '1' – timx1981 Feb 17 '17 at 22:10
  • Try it without quotes, I'm confident that userId is an int. – minus Feb 17 '17 at 22:14
  • Sorry about the delay. Went home for the night. I tried both with and without the single quotes. I appreciate you taking the time. I am stumped. – timx1981 Feb 18 '17 at 05:21