Web Service and SOA Testing - the leaders- eviware

John Overbaugh
Newbie

Posts: 16

« 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.


	Logged

omatzura
Administrator
Hero Member

Posts: 1,506

Re: Errors in interpretation

« Reply #1 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


	Logged

John Overbaugh
Newbie

Posts: 16

Re: Errors in interpretation

« Reply #2 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.


	Logged

omatzura
Administrator
Hero Member

Posts: 1,506

Re: Errors in interpretation

« Reply #3 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


	Logged

John Overbaugh
Newbie

Posts: 16

Re: Errors in interpretation

« Reply #4 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.


	Logged

omatzura
Administrator
Hero Member

Posts: 1,506

Re: Errors in interpretation

« Reply #5 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


	Logged

omatzura
Administrator
Hero Member

Posts: 1,506

Re: Errors in interpretation

« Reply #6 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


	Logged

John Overbaugh
Newbie

Posts: 16

Re: Errors in interpretation

« Reply #7 on: January 07, 2008, 06:35:31 pm »

Thanks - I will give this a try ASAP.


	Logged

John Overbaugh
Newbie

Posts: 16

Re: Errors in interpretation

« Reply #8 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.


	Logged

omatzura
Administrator
Hero Member

Posts: 1,506

Re: Errors in interpretation

« Reply #9 on: January 09, 2008, 12:47:14 am »

Great!

I'm glad I could help :-)

regards,

/Ole
eviware.com


	Logged

Pages: [1]

« previous next »