官术网_书友最值得收藏!

首頁 >

SoapUI Cookbook

  • SoapUI Cookbook
  • Rupert Anderson
  • 578字
  • 2021-07-23 20:19:19

Parameterizing SQL queries with the JDBC Request TestStep

This recipe builds directly on the last one to show how the JDBC Request TestStep can be used to execute parameterized SQL queries based on property values from outside the TestStep. This can be useful, as it allows the JDBC Request TestStep to query and check data based on properties set from the results of other TestSteps, for example, executing a query using an ID obtained from a web service response.

How to do it...

First, we add a new parameter with a fixed value to the JDBC Request TestStep and use it as the criteria for a simple select query. Then, we change the JDBC Request TestStep parameter to take its value from the value of a TestCase property. Perform the following steps:

  1. Edit the JDBC Request TestStep from the previous recipe and add a new parameter called invoiceIdParam with a value of 2.
  2. Then, modify the SQL Query value to add a where clause to specify that the invoice id field must be equal to the value of invoiceIdParam: 
    select * from invoice where id=:invoiceIdParam

    Note

    Placeholder syntax

    Use : before the intended parameter name in the query.

  3. Running the query should now return only the invoice number as 2: 
    <Results>
    <ResultSet fetchSize="0">
        <Row rowNumber="1">
            <INVOICE.ID>2</INVOICE.ID>
            <INVOICE.COMPANY>comp2</INVOICE.COMPANY>
            <INVOICE.AMOUNT>200</INVOICE.AMOUNT>
            <INVOICE.DUE_DATE>2014-12-01 00:00:00.0</INVOICE.DUE_DATE>
        </Row>
    </ResultSet>
</Results>
  4. So it works, but big deal! To make this more useful, we can try and use a SoapUI property expansion to get the parameter value from somewhere outside of the test step.

    Tip

    Property expansions

    SoapUI has an expression language in order to reference properties across many of the objects in a project. For more info, see http://www.soapui.org/Scripting-Properties/property-expansion.html.

    As an example:

    • Add an invoiceNo property to the TestCase that contains the JDBC Request TestStep; that is, double-click on the TestCase, click on the Properties tab, and add a new property with name as invoiceNo and value as 1.
    • Open the JDBC Request TestStep and edit the invoiceIdParam property value to contain: 
      ${#TestCase#invoiceNo}
    • Run the TestStep, and you should see an invoice with id=1!

How it works...

This recipe has been mostly explained as we did it. The key learnings are the syntax used for query parameters in a JDBC Request TestStep and the property expansion expression language. Property expansions are a very important concept in SoapUI, as they effectively allow data to be passed between related objects like TestSteps. They can be used in many other places to insert property values. Common examples would be setting the value of variables in a Groovy script or setting properties in a web service request.

There's more...

Another example would be to use property expansions with the JDBC Request TestStep to insert data gathered by a previous step, for example, to store test results in a database for an external reporting tool to use or to populate a mock service's test data. To insert data based on parameter values, you could use a query like the following one:

INSERT INTO test.invoice
(id, company, amount, due_date)
VALUES
(:invoiceIdParam, :invoiceCompanyParam, :invoiceAmountParam, :invoiceDueDateParam);

Tip

Property scopes

When using property expansions, it can be important to consider the property's scope, especially if you update them. For example, a project or a globally scoped property that is updated by multiple TestCases could lead to concurrency or thread-safety issues. In general, try to keep the scope as narrow as possible for writeable properties and as broad as possible for read-only properties.

See also

主站蜘蛛池模板： 泗水县| 和林格尔县| 安溪县| 吉安市| 惠安县| 衡水市| 门头沟区| 紫云| 顺昌县| 含山县| 清徐县| 莒南县| 康平县| 彩票| 铁岭县| 平安县| 汝阳县| 鱼台县| 莱阳市| 彩票| 平原县| 关岭| 康保县| 财经| 丰台区| 文化| 绵阳市| 怀集县| 蒙城县| 德惠市| 南和县| 来宾市| 姜堰市| 乐至县| 涿州市| 新干县| 大兴区| 新蔡县| 凤台县| 临汾市| 敦煌市|