|
Title: Errors in interpretation Post by: John Overbaugh on December 20, 2007, 12:00:00 am I'm working with Groovy scripts and soapUI. I have a Groovy script full of helper functions, and I've been creating a new helper function which is
SELECT COUNT (columnName) FROM tableName WHERE columnName = 'value' As you can see in the attached code, my helper function takes the columnName, tableName, entryValue, as well as a reference to the log and a binary (whether to throw an exception or error silently). All these parameters have been inserted into the SQL statement using $tableName and the likes: db.eachRow("SELECT COUNT ('$entryColumnName') from '$tableName' WHERE '$entryColumnName' = '$entryValue'") I have also simply built the query string: myQuery = "SELECT COUNT ($entryColumnName) from $tableName WHERE $entryColumnName = '$entryValue'" In all cases, when I reference the function from within soapUI, I get a SQL exception "invalid table name". I can literally copy the SQL statement and run it in SQL Developer and it succeeds. And if I hard-code values (instead of $tableName and other variables), it succeeds. There seems to be a problem interpreting variables when building a SQL string. It has worked consistently when it interprets '$condition' in a SQL statement substring enclosed in single-quotes; this is the first time I've tried to create an entire SQL statement this way. Any help = much appreciated. I've banged me head against the wall all day on this one. Very frustrating. Title: Re: Errors in interpretation Post by: omatzura on December 20, 2007, 12:09:55 am Hi John,
have you tried to add {} to your interpolations? ie db.eachRow("SELECT COUNT ('${entryColumnName}') from '${tableName}' WHERE '${entryColumnName}' = '${entryValue}'") ? regards, /Ole eviware.com Title: Re: Errors in interpretation Post by: John Overbaugh on December 20, 2007, 12:41:07 am No, I didn't think to try it. So here is my Groovy script then:
db.eachRow("SELECT COUNT ('${entryColumnName}') from '${tableName}' WHERE '${entryColumnName}' = '${entryValue}'") { row -> (myCount = row[0])} Unfortunately, it made no difference. It's almost as if soapUI cannot interpret variables in a script name - I think I mentioned that the ONLY variable I can pass in as $variableName is when it's in singlequotes - so '$variableName' works for me. John O. Title: Re: Errors in interpretation Post by: omatzura on December 20, 2007, 01:04:29 am Hi,
hmm.. I've copied your method into a local groovy file ("DBUtils.groovy"), removed the sql-parts and just called it with DButils.CountEntriesInTable( "1", "2", "3", log, true ) which gives the (to me) correct output of Thu Dec 20 01:00:36 CET 2007:INFO:Counting the instances of 2 in column 1 in table 3 Thu Dec 20 01:00:36 CET 2007:INFO:SELECT COUNT (1) from 3 WHERE 1 = '2' !? Are you getting correct log outputs initially in your method? How are you calling the method? regards! /Ole eviware.com Title: Re: Errors in interpretation Post by: John Overbaugh on January 02, 2008, 07:13:55 pm Hi Ole - sorry for the delay in replying; I was out for the holidays. Yes, yu are correct - if I hard-code the SQL statement and pass that into the function, it works fine. What I'm trying to do is great a simple generic function that returns the count of something.
Any other ideas, or is there any additional info I can provide you to see if there's a fix possible? Thanks!! John O. Title: Re: Errors in interpretation Post by: omatzura on January 04, 2008, 01:30:13 am Hi John,
I'll set up a local database and will try to mimic your setup as closely as possible.. can you share a script that you are using to call your function as well? regards! /Ole eviware.com Title: Re: Errors in interpretation Post by: omatzura on January 07, 2008, 11:05:21 am Hi again John,
I've set up a local MySQL database and modified your function to be as follows: Code: package john.overbaugh.utils import groovy.sql.Sql public class DBUtils { def static CountEntriesInTable(entryColumnName, entryValue, tableName, log, stopOnError) { def myCount def db = Sql.newInstance('jdbc:mysql://127.0.0.1/db_ole', 'root', 'XXX', 'com.mysql.jdbc.Driver') def myQuery = "SELECT count($entryColumnName) from $tableName WHERE $entryColumnName = '$entryValue'" log.info("Counting the instances of $entryValue in column $entryColumnName in table $tableName") log.info(myQuery) try { myCount = db.firstRow(myQuery)[0] } catch (Exception e) { e.printStackTrace() } finally { log.info(" - Count = " + myCount) return myCount } } } Then I call this with Code: import john.overbaugh.utils.DBUtils log.info( DBUtils.CountEntriesInTable( 'name', 'ole', 'db_ole.tb_test', log, false )) and get the correct result back.. could you see how this differs from your setup? maybe it works for you as well? regards! /Ole eviware.com Title: Re: Errors in interpretation Post by: John Overbaugh on January 07, 2008, 06:35:31 pm Thanks - I will give this a try ASAP.
Title: Re: Errors in interpretation Post by: John Overbaugh on January 08, 2008, 08:05:07 pm Sweet!! I finally had a chance to use this code, Ole, and it works brilliantly. Thanks very much - you made it so I can remove a ton of tedious code.
Title: Re: Errors in interpretation Post by: omatzura on January 09, 2008, 12:47:14 am Great!
I'm glad I could help :-) regards, /Ole eviware.com |