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;
}