how to use returned result from Script in Keyword test

[nakshatra]

Hi All,

I wrote one script for getting result from DB. Below is my code:

function query (sqlQuery,Connectionstring) { 

var dbObj = ADO.CreateADOQuery(); 

dbObj.ConnectionString = Connectionstring

dbObj.SQL = sqlQuery;  

dbObj.Open(); 

queryResult = dbObj; 

return queryResult; 

} 

Query Result:

col 1	col 2	col 3	col 4	col 5
data 1	data 2	data 3	data 4	data 5

Now I need to call this method in keyword test and need to use the values to pass like parameter in another method.

I tried to store by using set variable value but it is not working.

Can anyone help me to slove this.

Thnaks in advance.

Regards,

saritha.

[RUDOLF_BOTHMA]

What Type is your Variable you are trying to set ?  You may need to create your variable as a Type of Object.  You can send this variable to another script, but your other script will need to know how to interpret it.  In other words, it needs to know what the data is going to look like.  And of course be able to handle if the variable value is null or undefined

[nakshatra]

Thanks for the reply.

In my code return type is var. i am returning dbObj as a result. Now how to store the result?

Can you explain me little bit detail how to store and how to use in Keyword test.

function query (sqlQuery,Connectionstring) { 

var dbObj = ADO.CreateADOQuery(); 

dbObj.ConnectionString =  Connectionstring; 

dbObj.SQL = "Select top 5 col1, col2, col3,col4,col5 from table";  

dbObj.Open(); 

return dbObj ; 

} 

[RUDOLF_BOTHMA]

Good start in Support documentation:

Working With Project and Project Suite Variables in Keyword Tests

and

Working With Project and Project Suite Variables in Scripts

Quick overview:

Create the required scripts.  One returns a variable, one accepts a variable and does something with it

In your KWT, add the Run Script routine function

Set your local variable to the last result

Add the ConsumeObject Test Script and run the KWT:

Et voila !

There is nothing stopping you from using the local variable in code expressions in the KWT itself.  It isn't limited to only scripts.  You just need to make sure you know what type/structure your variable is.  This is all just a psuedo example though.  In a scenario like this, I wouldn't use the KWT to get a value from a script and pass it to another script.  I would just create a single script, which could in turn call the second script directly and cut out the KWT as middleman

[nakshatra]

Thanks for the detail explanation.

For single value working fine. But here my method will return one row data with 10 columns(Like In DB after running query result will display)

How to use that result value in keyword test we are not getting. Could you please help me on this?

Regards,

Saritha.

[RUDOLF_BOTHMA]

You get retrieve data from a Local variable through a code expression e.g. the following If statement in the KWT:

Since your variable is of type object, anything can be in there, but you need to know what the structure is and where to look.  I'm just guessing at what you should be looking at.  I've never retruned a dbObject to a KWT.

I have to ask though, why are you trying to do this ?  Is it perhaps something handled better with 

Data-Driven Testing

Also, you are probably better off doing this kind of work from within a script if you aren't looking at DDT.  I can't see any code for closing and cleaning the open connection.  You will have to have a seperate script to call from the KWT anyway

[nakshatra]

Thanks for the reply.

I will try it . Here I am trying to implement dynamic data base connection with sql query and connection string as parameters.

So one generic method in any keyword test i can use it.

If i want to run application in Environment 1 to environment 2, in one place i can change the connection string.

This is my idea.

Regards,

Saritha Modiam.

[RUDOLF_BOTHMA · Accepted solution]

---

If i want to run application in Environment 1 to environment 2, in one place i can change the connection

---

Consider storing your connection string as a variable on the Project level, so your KWT won't always have to send the connection string as a parameter each time you call the function.  Note the RunQuery looks different.  It's a rough translation of what I would do with ADO.  There is also a second parameter that can be sent with an array, so you can parametrise your queries

function RunQuery(sqlQuery, params) { 
ADOConnection = OpenConnection(); //Function that does work to open an return a connection with some checking if a connection is already open etc.  Excercise for the reader
if(ADOConnection==null)
throw("ADO Connection Null");
ADOCommand = ADO.CreateCommand();
ADOCommand.ActiveConnection = ADOConnection; // Connection
ADOCommand.CommandType = CmdText;
ADOCommand.CommandText = sqlQuery;
if(params!= null && params.length>0)
{
  for(var i=0;i<params.length;i++)
  {
    ADOCommand.Parameters.Item(i) = params[i];
  }
} 
var returnValue = ADOCommand.Execute();
ADOConnection.Close();
return returnValue;
} 

You can then maintain the value of the project variable in a convenient, central place.  You may find that your SQL queries get too long/complex, so perhaps seperate them into different functions in a DBQueries script and call that from your KWT:

function GetAllUsers(){
return  RunQuery("Select * from [Users]");
}

function GetOneUser(userID){
return RunQuery("Select * from [Users] where userID = ?", [userid]);
}

The syntax and usages are approximate.  You will have to tinker till it works for you

[nakshatra]

Thanks for the reply :).

Expalined very well. I will try it.

Regards,

Saritha.