Forum Discussion

Computer_Resear's avatar
Computer_Resear
Occasional Contributor
16 years ago

Re: Datasource query property expansion errors.

I have created a datasource step to execute the following sql:

select *
from aTable
where column1 = '777'
and column2 = ${="'" + ${Properties#theProperty} + "'"}

I created a properties step called "Properties" which has a property called theProperty with a value B.  We are trying to add single quote to the property value.

I get the following exceptions:

groovy.lang.MissingPropertyException: No such property: B for class: Script1

It seems to be misinterpreting the property value of B as the property name.  This generates the following exception,

java.sql.SQLException: [SQL0104] Token SUCH was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. Cause . . . . . :  A syntax error was detected at token SUCH.  Token SUCH is not a valid token.  A partial list of valid tokens is FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :  Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token SUCH. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

We have done this in the past and it works in the datasource step.  Please advice.

Thanks,

Srini
  • Computer_Resear's avatar
    Computer_Resear
    Occasional Contributor
    16 years ago
    Some additional information:

    If the property value is a numeric (say 123), it works but if the property value contains an alphabet (say 1B or B or BBBB etc.), the exception is thrown.

    Thanks,

    Srini
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    Icon for Alumni rankAlumni
    16 years ago
    I am not sure what you are trying to do with the code you have there, could it be simplified to:
    select *
    from aTable
    where column1 = '777'
    and column2 = "'" + ${Properties#theProperty} + "'"

    What do you expect the resulting SQL to look like, could you send us an example?

    /Nenad
    http://eviware.com
  • Computer_Resear's avatar
    Computer_Resear
    Occasional Contributor
    16 years ago
    Thanks for your response.    I have tried your suggestion in the past and I tried it again.  I get the following exception:

    java.sql.SQLException: [SQL0206] Column "'" not in specified tables. Cause . . . . . : "'" is not a column of table

    In the data source step, we want to be able to run the following query,

    select *
    from aTable
    where column1 = '777'
    and column2 = 'B'

    where the value for column2 (B) is read from a spreadsheet in an earlier step (${Properties#theProperty} ) and using the Dynamic Properties facilities of SoapUI, we want to add the single quotes around the value and hence the expression ${="'" + ${Properties#theProperty} + "'"}.  Again, this works well if the ${Properties#theProperty}  is a numeric.

    Thanks,

    Srini
  • M_McDonald's avatar
    M_McDonald
    Super Contributor
    16 years ago
    The equivalent of this works for me (SQL Server):

    [tt:2rqwu4od]select *
    from aTable
    where column1 = '777'
    and column2 = '${Properties#theProperty}'[/tt:2rqwu4od]
  • Computer_Resear's avatar
    Computer_Resear
    Occasional Contributor
    16 years ago
    We had tried that and it works.  But why did the dynamic properties (property expansion with groovy script) not work.

    Thanks,

    Srini
  • M_McDonald's avatar
    M_McDonald
    Super Contributor
    16 years ago
    I think (the Eviware folks can correct me if I am wrong) but the evaluation of property expansions work from the inside outward, so

    [tt:2fwceowe]${="'" + ${Properties#theProperty} + "'"}[/tt:2fwceowe]

    first evaluates to

    [tt:2fwceowe]${="'" + b + "'"}[/tt:2fwceowe]

    which now is interpreted single quotes surrounding a variable b, which does not exist in scope.

    This works when the property contains a number since it would then be interpreted as a literal value and not a variable, eg:

    [tt:2fwceowe]${="'" + 123 + "'"}[/tt:2fwceowe]

    When I am trying to figure out this kind of problem I find it helpful to create a Groovy script step and wrap the stuff to be evaluated in a context.expand:

    log.info context.expand('''
    select *
    from aTable
    where column1 = '777'
    and column2 = ${="'" + ${Properties#theProperty} + "'"}
    ''')


    then execute the step to see the result.