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

首頁 >

SoapUI Cookbook

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

主站蜘蛛池模板： 西充县| 周宁县| 葫芦岛市| 吉隆县| 铜山县| 岑巩县| 金乡县| 九龙县| 栾城县| 遂宁市| 五河县| 茌平县| 鱼台县| 汤原县| 外汇| 资中县| 永和县| 布拖县| 田阳县| 东宁县| 灵寿县| 前郭尔| 全州县| 兴和县| 同江市| 贡觉县| 拜城县| 长岭县| 阳高县| 青神县| 凤阳县| 万宁市| 南昌县| 开封市| 海门市| 任丘市| 宁德市| 二手房| 元阳县| 寻乌县| 丽江市|