fetch data from SQL database at runtime

Yes.     Herre is some javascript code that I use for my application.  I had a difficult time  finding the correct database connection string and parameters. Theoretically your DBA's should be able to help you with that....



Regards,

Curt



  var conObj, rs;



  conObj = new ActiveXObject("ADODB.Connection");

 

  var connectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=QA_11G_RT)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<port>)))(CONNECT_DATA=(SERVICE_NAME=<srervicename>)(SERVER=DEDICATED)));User Id=,<user ID>;Password=<password>;";

  /*

   account cpdb has access to the data warehouse (rp_program_savings) from the realtime database

*/



/*   Real time   User ID = <user ID> password = <passsword> */

 

  conObj.Open(connectionString);



          var CurrentDate=aqDateTime.Now();

//          Log.Message("current date is " + CurrentDate);

        

  rs = new ActiveXObject("ADODB.Recordset");



  // select avg(savings) returns odd values for some undetermined reason

  // running the total manually and fixing the

  sql1 = "select savings from en_program_savings where to_date(trunc(start_dt) ) > to_date('"+date(-nbrOfDays)+"','DD-MON-YY HH24:MI:SS')";



  try {

 

    rs.Open(sql1, conObj, 3); // 3 indicates static cursor                                            

  }

  catch (e) {

     Log.Error("Database open error:"+ e.toString() );

     return (0);

  }

 

  // Not available for default opening method (forward-only cursor)

//  Log.Message ("record count is :" +rs.RecordCount);

  var count = rs.RecordCount;

 

  if (count == 0) {

      Log.Warning("Zero records returned for average savings.");

      return 0;

  }

  var total = 0;

  // rs(n) is actually rs.Fields.Item(n)  since Fields.Item is the default

  while (!rs.EOF) {

    total = total + rs(0);

    rs.MoveNext();              

  }

//  Log.Message(" total is: " + total);

  var average = total/count;

  average = Math.round(average*100)/100;

//  Log.Message(" average is : " + average);

                               

  try {

    //Log.Message(rs(0));

    rs.close();

    conObj.close();

  }

  catch(e){}  ;

  return average;

thanks Curt,



but, what I am looking is to make the data from the SQL available in the DBTable of Test Complete after the SQL connection is closed.

Since script has to process a lot of data from SQL, I need to fetch data and copy it to the DBTable (or any storage location in Test Complete), then close the connection and use the data for further processing.

Specifically, the VBS code to do the declaration, copying and fetching of / from DB Table



thank you

Jinith

There is no method of "copying" the data that I know of. You need to open the connection, recordset, loop and create the data table.



So a simple untested psydo code example would be:



' create a table var

 If Project.Variables.VariableExists("Person") then

    Project.Variables.RemoveVariable "Person"

  end if

 Project.Variables.AddVariable "Person", "Table"

set s = Project.Variables.VariableByName("Person")

s.AddColumn "FirstName"

s.AddColumn "LastName"



' open a recordset

set cn = Sys.OleObject("ADODB.Connection")

set rs = Sys.OleObject("ADODB.Recordset")

cn.Open ... connection string...

sSql = "Select Count(*) FROM ".... ' get the count of the records gonna return.  Where clause... whatever

rs.Open, sSql, cn, adOpenForwardOnly

iRecordCount = rs(0)

rs.Close

sSql = "Select FirstName, LastName " ' actually select the records same where claus...

set rs = Sys.OleObject("ADODB.Recordset")

rs.Open, sSql, cn, adOpenForwardOnly





s.RowCount = iRecordCount

i = 0

Do While Not rs.Eof

    s.FirstName(i) = rs("FirstName").Value

    s.LastName(i) = rs("LastName").Value

    rs.MoveNext

    i = i + 1

Loop



rs.close

cn.close





Error trapping, and all the other stuff left as an exercise to the reader! Good luck.

Thank you Stephanie,

It helped a lot to resolve my issue.

Though data from SQL is lost after the execution, I managed to write it to an html file while executing.



thank you once again

Regards

Jinith