Errors in interpretation

[John_Overbaugh]

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.

[omatzura]

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

[John_Overbaugh]

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.

[omatzura]

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

[John_Overbaugh]

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.

[omatzura]

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

[omatzura]

Hi again John,

I've set up a local MySQL database and modified your function to be as follows:

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

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

[John_Overbaugh]

Thanks - I will give this a try ASAP.

[John_Overbaugh]

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.

[omatzura]

Great!

I'm glad I could help 🙂

regards,

/Ole
eviware.com