Forum Discussion

John_Overbaugh's avatar
John_Overbaugh
Occasional Contributor
17 years ago

Errors in interpretation

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.

9 Replies

Sort By
  • omatzura's avatar
    omatzura
    Super Contributor
    17 years ago
    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's avatar
    John_Overbaugh
    Occasional Contributor
    17 years ago
    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's avatar
    omatzura
    Super Contributor
    17 years ago
    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's avatar
    John_Overbaugh
    Occasional Contributor
    17 years ago
    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's avatar
    omatzura
    Super Contributor
    17 years ago
    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's avatar
    omatzura
    Super Contributor
    17 years ago
    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's avatar
    John_Overbaugh
    Occasional Contributor
    17 years ago
    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's avatar
    omatzura
    Super Contributor
    17 years ago
    Great!

    I'm glad I could help :-)

    regards,

    /Ole
    eviware.com