Multiple DDT CSV's one test

[rmnrdi]

We have an application called Job Entry. It takes some of its data from the JobEntryData.csv as shown below.

  var csvPath = "C:\\TestData\\JobEntryData.csv";
  Driver = DDT.CSVDriver(csvPath);
  
  ih.RunInnovationsProgram("JobEntry.exe");
  ih.LoginToInnovations("JobEntry","Ocuco","");
    
    while (!Driver.EOF())
    {
      PopulateIDPage(Driver);
      PopulateFramePage(Driver);      
      PopulateLensesPage(Driver);
      PopulateRxPage(Driver);
      PopulateExtrasPage(Driver);
      
      //Indexes to next line of data source
     Driver.Next();  
    }
      DDT.CloseDriver(Driver.Name);
}

Some of these PopulateXXXXPage() functions are straightforward, but others data will change based on a combo box. In the switch statement below, you can see the FrameSource_xxxxx changes based on a combo box value. Library, Manual, Uncut in this case.

  switch (FrameSource)
  {
    case "Library" : 
      FrameSource_Library(groupBox);
      groupBox.ckEdge2.Keys('[Enter]');
      break;  
    case "Manual" :
      FrameSource_Manual(groupBox);
      groupBox.ckEdge2.Keys('[Enter]');  
      break;
    case "Uncut" :
      FrameSource_Uncut(groupBox); 
      break;     
  }

Which executes a given function, like  the FrameSource_Library():

function FrameSource_Library(groupBox)
{
  var csvPath = "C:\\TestData\\FramesPage\\FrameSource_Library.csv";
   Driver = DDT.CSVDriver(csvPath); 
  
  var libraryTracing = Driver.Value(0);
  var type = Driver.Value(1);
  var fClass = Driver.Value(2);
  var dbl = Driver.Value(3);
  
  groupBox.cbxSource2.Keys("[Enter]");
  groupBox.ISTraceLib2.Keys(libraryTracing + "[Enter]");
  groupBox.cbxType2.Keys(type + "[Enter]");
  groupBox.cbxClass2.Keys("[Enter]");     
}

The problem I'm having is after the original pass and after the driver indexes to the next record. It uses the values from FrameSource_Library.csv and not the JobEntry.csv.

I'm pretty sure it has to do with the value persisting in the driver variable. 

Before getting into a solution, is this even a good way to do this? If not, got any suggestions?

I hope this is clearer than mud.

[rmnrdi]

Wow, This is a cool idea!

I'll play around with it and see how it goes.

One thing though, I couldn't get the query to work right with the string you gave me.

'SELECT * from MySecondFile.csv where orderID = ''' + OrderID + '''

I fell into escape character hell and couldn't get it to work (using JS).

I ended up doing a template string.

 `Select * from JobEntryData.csv where TestID ="${testID}"`;

Although if I was to try to script extension it, that wouldn't work.

[tristaanogre]

It's probably the escape characters for the string... PITA, yes, but once you get that SQL query figured out, you're golden.

I use this method of querying CSV files frequently.  It's more portable than using an SQL database and anyone can access and edit it... really nice for building out an automation framework. 

Let me know how it works out for you and if you need any further help.

[rmnrdi]

So I figured out how I'm going to have the data structured.

I have individual csv's that supply data to each page of the app.

They'll be queried sort of as you outlined, but I'll pass in a "TestID". This test ID will be what keeps all the csv's in sequence. So all csv's will have a 1 row, 2 row that correspond to each other.

What I do is, I dynamically query and store them in project suite level variables. I then set them to global variables to be used for the test.

This works great except I'm having one issue. I get the following error on the 11th test with a path error for "RxFields.csv"

JSRuntimeError

I've tried recreating the csv, moving the 10th and 11th rows, deleting the 10th and 11th rows, changing the test to run rows 1 through 5, 3 times and it still fails on the 11th test run.

function SetGlobalVariablesFromCSV(csvPath,id,index)
{ 
  //Make sure csv column format is set to text!
  let Driver = DDT.CSVDriver(csvPath); <-----This is where it fails every time.
  let curDriver = DDT.CurrentDriver.Name;
  let csvName = aqFileSystem.GetFileName(csvPath);
    
    var queryByID = 'SELECT * FROM ' + csvName  + ' WHERE ' + id + '=' + "'" +  index + "'" + '';
    Driver.ADOCommandObject.CommandText = queryByID;

    let colCount = Driver.ColumnCount;
    while(!Driver. EOF())
    {
      Log.Message("------- Setting global variables for " + csvName + "...-------------------------------------");
    
        for(var i = 0; i < colCount ;i++)
        { 
        var colName = Driver.ColumnName(i);
        var colVal = Driver.Value(i);
          Log.Message("Column " + i + " name is " + colName);
          CreateVariable(colName,"String");
         ProjectSuite.Variables.$set("VariableByName",colName, colVal);     
        }
      Log.Message("------------------ Global variables for " + csvName +  " are set. -----------------------------")                                  ;
      Driver.Next();
    }
    //DDT.CloseDriver("CSV0");
}

Any ideas what could be causing this?

[tristaanogre · Accepted solution]

My best guess is this...   You have too many CSV connections open.  You need to make sure that, when you're finished iterating through a CSV result set, you use DDT.CloseDriver to close the connection. There is a limitation in the database engine for CSV connections that restricts how many connections can be open.  

I see, in your code, you have the call... but it's commented out.

Change that line to DDT.CloseDriver(Driver.Name) and that should work.  You need to make sure you have the same sort of call for all your DDT drivers so that you're closing the connection when it's no longer needed

[rmnrdi]

Making this code change at the bottom, did in fact solve the issue.

function SetGlobalVariablesFromCSV(csvPath,id,index)
{ 
  //Make sure csv column format is set to text!
  let Driver = DDT.CSVDriver(csvPath); 
  let curDriver = DDT.CurrentDriver.Name;
  let csvName = aqFileSystem.GetFileName(csvPath);
    
    var queryByID = 'SELECT * FROM ' + csvName  + ' WHERE ' + id + '=' + "'" +  index + "'" + '';
    Driver.ADOCommandObject.CommandText = queryByID;

    let colCount = Driver.ColumnCount;
    while(!Driver. EOF())
    {
      Log.Message("------- Setting global variables for " + csvName + "...-------------------------------------");
    
        for(var i = 0; i < colCount ;i++)
        { 
        var colName = Driver.ColumnName(i);
        var colVal = Driver.Value(i);
          Log.Message("Column " + i + " name is " + colName);
          CreateVariable(colName,"String");
         ProjectSuite.Variables.$set("VariableByName",colName, colVal);     
        }
      Log.Message("------------------ Global variables for " + csvName +  " are set. -----------------------------")                                  ;
      Driver.Next();
    }
    DDT.CloseDriver(DDT.CurrentDriver.Name);<------------------Actually closing the driver.
}

The funny thing is, I had that in there, but I removed it because it was taking 90 seconds to load my variables. I later realized I had a loop in a loop situation. I fixed that, then didn't put that line back, hence not closing the driver.

I would say that I owe you a beer, but actually I owe you a keg at this point.

Thanks,