Forum Discussion

dpurnima's avatar
dpurnima
Occasional Contributor
2 months ago

Using Dynamic SQL in TestComplete DBTable

Our application data is completely refreshed every month, so the record values/keys change. Is there a way i can make the queries dynamic in the DBTable wizard. For e.g. I want to use something like the following in the DBTable wizard.

select * from employee where account_no=?

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 months ago

    Try to use a variable, if it's possible. Otherwise use script code.

    • dpurnima's avatar
      dpurnima
      Occasional Contributor
      2 months ago

      I am looking for a way to specify the variable in the DBtable wizard in the following ways, but just get the error "invalid character". So what wound be the correct way to do that:

      select * from employee where account_no = Project.Variable.ACC_NO

      select * from employee where account_no = #Project.Variable.ACC_NO#

      select * from employee where account_no = '#Project.Variable.ACC_NO#'

       

      As you suggested, I too prefer to do this in a JavaScript. But unable to find a way to set the values retrieved into a Persistent DBTable.  So any advice here.

    • rraghvani's avatar
      rraghvani
      Champion Level 3
      2 months ago

      Are you able to provide the code that you are having issues with?

  • dpurnima's avatar
    dpurnima
    Occasional Contributor
    2 months ago
    function updateDBTable() {  
  var location_id = 1234;  
  var connectionString = "Provider=OraOLEDB.Oracle.1;Password=XXXX;Persist Security Info=True;User ID=XXXX;Data Source=DB_NAME";
  var query = "SELECT COL_1, COL_2, COL_3, COL_4, COL_5, COL_6, COL_7 FROM SAMPLE_TABLE WHERE LOCATION_ID = " + location_id ;
  
  var connection = ADO.CreateConnection();
  var recordset = ADO.CreateRecordset();    
    connection.Open(connectionString);
    recordset.Open(query, connection);
    if (!recordset.EOF) {
      recordset.MoveFirst();
      var dbTable = DBTables.Person_Address_Dyn;      
      dbTable.$set("Values", 0, 0, recordset.Fields.Item("COL_1").Value);
      dbTable.$set("Values", 0, 1, recordset.Fields.Item("COL_2").Value);
      dbTable.$set("Values", 0, 2, recordset.Fields.Item("COL_3").Value);
      dbTable.$set("Values", 0, 3, recordset.Fields.Item("COL_4").Value);
      dbTable.$set("Values", 0, 4, recordset.Fields.Item("COL_5").Value);
      dbTable.$set("Values", 0, 5, recordset.Fields.Item("COL_6").Value);
      dbTable.$set("Values", 0, 6, recordset.Fields.Item("COL_7").Value);      
    }  
    if (recordset.State == 1) recordset.Close();
    if (connection.State == 1) connection.Close();  
}

    The data is available in the Person_Address_Dyn in the same execution as this code, but not for later executions.