Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
star437 Absent Member.
Absent Member.
932 views

run sql queries on sql server and get the column names of the query result

Hello,

 

I need to make some sql queries in a LR script in order to check the values in front of internal data.

In addition, I need to get the column names of the result, which is not necessarily the names of the original tables in the database, because there are sometimse aliases in our requests.

 

For that purpose, I used the lr_db set of fonctions whi work pretty well, but the problem is that I don't get the column names in the result.

 

The function lr_db_getvalue requires the name of the column in input, and I don't have it.

I tested the function ld_db_dataset_action with the print option, and it gives the column names of the result. But the problem is that it is only possible to print it in the log files, and not to store the result in a local variable.

I don't have the possibility to parse the logs, so I am looking for another option.

 

So my question is:

 

How can I get the column names of a sql query result with load runner?

If not possible, is there a library that you recommend that I could use for that purpose?

The database is SQL SERVER

 

Thanks in advance,

 

Gilles

 

 

0 Likes
3 Replies
thientn Absent Member.
Absent Member.

Re: run sql queries on sql server and get the column names of the query result

Hi Gilles,

I suggest you query metadata for getting column names. Refer to sql-server-useful-metadata-queries.
The statement you need is: SELECT column_name FROM information_schema.columns WHERE table_name='<yourTableName>'
Here is sample:

vuser_init()
{
    lr_db_connect("StepName=myStep", 
        "ConnectionString=Initial Catalog=MyDB;Data Source=myDataSource;user id =sa ;password = 123456" ,
        "ConnectionName=MyConnection",
        "ConnectionType=SQL",
        LAST);
    lr_db_executeSQLStatement("StepName=MyStep", 
        "ConnectionName=MyConnection", 
        "SQLQuery=SELECT column_name FROM information_schema.columns WHERE table_name='tableName'", 
        "DatasetName=ds1", 
        LAST);
    return 0;
}

 

and use it in Action script

Action()
{
    lr_db_getvalue("StepName=MyStep", 
        "DatasetName=ds1", 
        "Column=column_name", 
        "Row=next", 
        "OutParam=columnNameParam", 
        LAST);
    ...
}


Hope it helps.

 

Regards,
Thien

0 Likes

Re: run sql queries on sql server and get the column names of the query result

I want to query column value count, How can I get count value? There is no column name for that.

0 Likes
Highlighted
Outstanding Contributor.. JHF Remmelzwaal Outstanding Contributor..
Outstanding Contributor..

Re: run sql queries on sql server and get the column names of the query result

When you write your own query do not use something like "SELECT *", but just write each column out and supply aliasses for expressions: Like

SELECT T1.fieldname1, T1.fieldname2, T2.fieldname1 as "fieldname1_1", count(*) as "Count1", T1.fieldname1 * T1.fieldname2 as "Expression1", ...

FROM T1 LEFT JOIN T2 ON ...

You have in this way full control on the column names, expressioins in your resultset.

I would have expected that columns can be addressed by name and number, but according documentation it is only by name.

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.