Share this page

# Use data retrieved from SQL queries

A normal use of database steps is fetching data from the database and using it at a later point in the script. Since the script retrieves the data during each test run, the data is up to date and relevant.

The following example illustrates a typical flow for a Web Service protocol script. A similar sequence can also be applied to other protocols.

Step | API function
--- | ---
Connect to database | lr_db_connect
Execute an SQL query | lr_db_executeSQLStatement
Retrieve and save the data | lr_db_getvalue to <param_name>
Web Service call | web_service_call with {<param_name>}
Disconnect from database | lr_db_disconnect

You can iterate through the results in two ways:

- Save them to a simple parameter during each iteration

- Use VuGen built-in iterations to scroll through the data

For more information, see the Function Reference.

In the following web service example, the vuser_init section connects to the database and performs a database query.

    vuser_init()
    {
    lr_db_connect("StepName=myStep",
        "ConnectionString=Initial Catalog=MyDB;Data Source=mylab.net;user id =sa ;password = 12345;" ,
        "ConnectionName=MyConnection",
        "ConnectionType=SQL",
        LAST);
    lr_db_executeSQLStatement("StepName=MyStep",
            "ConnectionName=MyConnection",
            "SQLQuery=SELECT * FROM Addresses",
            "DatasetName=ds1",
            LAST);
        return 0;
    }

At the end of your test, disconnect from the database in the vuser_end section.

    vuser_end()
    {
        lr_db_connect("StepName=myStep",
            "ConnectionString=Initial Catalog=MyDB;Data Source=LAB1.devlab.net;user id =sa ;password = soarnd1314;" ,
            "ConnectionName=MyConnection",
            "ConnectionType=SQL",
            LAST);
        return 0;
    }

In the Action section, you include the steps to repeat. Note the use of the Row argument. In the first call to the database, you specify the first row with Row=next. To retrieve another value in the same row, use current.

    Action()
    {
        lr_db_getvalue("StepName=MyStep",
            "DatasetName=ds1",
            "Column=Name",
            "Row=next",
            "OutParam=nameParam",
            LAST);
        lr_db_getvalue("StepName=MyStep",
            "DatasetName=ds1",
            "Column=city",
            "Row=current",
            "OutParam=cityParam",
            LAST);
    /* Use the values that you retrieved from the database in your Web Service call */
        web_service_call( "StepName=EchoAddr_101",
            "SOAPMethod=SanityService|SanityServiceSoap|EchoAddr",
            "ResponseParam=response",
            "Service=SanityService",
            "ExpectedResponse=SoapResult",
            "Snapshot=t1227168459.inf",
            BEGIN_ARGUMENTS,
            "xml:addr="
                "<addr>"
                    "<name>{nameParam}</name>"
                    "<street></street>"
                    "<city>{cityParam}</city>"
                    "<state></state>"
                    "<zip></zip>"
                "</addr>",
            END_ARGUMENTS,
            BEGIN_RESULT,
            END_RESULT,
            LAST);
    return 0;
    }

https://admhelp.microfocus.com VuGen,Professional Performance Engineering,Performance Engineering for Developers 8942
Tips to improve your search results... OpenText™ Virtual User Generator Help Center OpenText™ VuGen Help 26.1-26.3 All View Local Help Center View Online Help Center Version: