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;