Forum Discussion

allenj2020's avatar
allenj2020
Contributor
10 years ago
Solved

Return stored procedure result into two dimensional array

Hi all,

 

I need to validate the results of a complex bill of materials tree, populated by an underlying stored procedure.

I'm currently doing this directly in the application (a Windows app using Delphi) by writing selected columns of the tree data to a text file and comparing it to a file checkpoint.  I would like to be able to do this comparison directly against the database.

The challenge is that I need to call a stored procedure with parameters, then walk through the multi row result, writing it to a file so I can compare it to the baseline as I do already.  DBTable checks won't work in this case, because if a different amount of rows are returned I need to see exactly what the differences are.

 

Can somebody help me out with some Delphiscript code here?  What I need, if possible, is the code to call the procedure and return the results into a two dimensional array or equivalent data set.  I can take it from there to write that into a file and check it.

I've looked at the CreateADOStoredProc help, but it isn't clear to me how to make the result set do what I want.

 

If you have any better ideas for how to do what I need, I'm always open to suggestions.

 

Thanks!

  • allenj2020's avatar
    allenj2020
    10 years ago

    Here's the Grid view in the application. This is an open Delphi app, so I have full Debug Agent access to all data in this grid.

     

    Grid_View.JPG

     

     Here's the SQL call and result set that feeds that grid, this is the data that I'm hoping to test directly without having to look at the application grid above:

    SQL_Data.JPG

     

    I'm not sure what you mean by the variable that hold the resultset, are you talking about the ADO data set, or RecordSet as Microsoft calls it?

     

    Anyway, after sleeping on it and doing some more work this morning, I managed to get it working.  I ended up using CreateADODataSet, call the procedure with parameters to populate the data set, then write the results into a text file, which I can then compare later using a StoredFile.Check call.

    The code is below if anyone is interested.

     

    Thanks.

    Allen

     

     

    // Call the spTST_getBOMByPONumber procedure
    // Write the results to a text file
    procedure GetBOMByPONumber(PONumber, LineNo, olnIDInstanceParm, AsOfDate, oFile);
    var
    DSet : OleVariant;
    rowloop, columnloop, checkloop: integer;
    rowstring, separator, itemNumber: string;
    begin
    // Create the new IAQAADODataset object
    DSet := ADO.CreateADODataset;

    DSet.CursorType := ctStatic;
    // Specify the connection string
    DSet.ConnectionString := 'Provider=MSDASQL.1;Data Source=inSight10';
    // Specify the command type and text
    DSet.CommandType := cmdStoredProc;
    DSet.CommandText := 'spTST_getBOMByPONumber';
    DSet.Parameters.AddParameter();
    DSet.Parameters.Items(0).Name := 'PONumber';
    DSet.Parameters.Items(0).Value := PONumber;

    DSet.Parameters.AddParameter();
    DSet.Parameters.Items(1).Name := 'LineNo';
    DSet.Parameters.Items(1).DataType:=ftInteger;
    DSet.Parameters.Items(1).Value := LineNo;

    DSet.Parameters.AddParameter();
    DSet.Parameters.Items(2).Name := 'olnIDInstanceParm';
    DSet.Parameters.Items(2).DataType:=ftInteger;
    DSet.Parameters.Items(2).Value := olnIDInstanceParm;

    DSet.Parameters.AddParameter();
    DSet.Parameters.Items(3).Name := 'AsOfDate';
    DSet.Parameters.Items(3).DataType:=ftDate;
    DSet.Parameters.Items(3).Value := AsOfDate;

    // Open the dataset
    DSet.Open;
    // Process records
    DSet.First;

    // Build and write file headers
    rowstring := '';
    rowstring := ' ID |itmItemNumber|Qty|PrimaryUOMCode|BOMLevel|itmIsPurchased|itmpCode|itmIDInstance|rotID|dlCode|sttDescription|ProgressState|pltPlantCode';
    oFile.Write(rowstring + #13#10);

    for columnloop := 1 to DSet.RecordCount do
    begin
    // Insert data into the test log
    //Log.Message(DSet.FieldByName('ID').Value, DSet.FieldByName('itmItemNumber').Value);
    rowstring := '';

    // Dump each row to the file
    rowstring := DSet.FieldByName('ID').Value
    + '|' + DSet.FieldByName('itmItemNumber').Value;
    + '|' + aqConvert.VarToStr(DSet.FieldByName('Qty').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('PrimaryUOMCode').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('BOMLevel').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('itmIsPurchased').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('itmpCode').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('itmIDInstance').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('rotID').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('dlCode').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('sttDescription').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('ProgressState').Value)
    + '|' + aqConvert.VarToStr(DSet.FieldByName('pltPlantCode').Value);

    oFile.Write(rowstring + #13#10);

    DSet.Next;
    end;
    DSet.Close;

    end;


    procedure TestBOMFromSQL();
    var ofile, inputstr;
    begin
    oFile := aqFile.OpenTextFile('C:\temp\TestBOMFile.txt', aqFile.faWrite, aqFile.ctANSI, true);
    oFile.Write(inputstr);
    GetBOMByPONumber('SqlTest', 1, 1, '6/12/2015', oFile);
    oFile.Close;
    end;

     

     

4 Replies

  • Hi allenj2020,

     

    Please provide:

     

    1 - A screenshot of the resultset (grid view);

    2 - A "watch" of the variable that holds the resultset (you can get that in debug mode inside Testcomplete)

     

    Best Regards,

     

    Leandro de Araújo Souza

    • allenj2020's avatar
      allenj2020
      Contributor

      Here's the Grid view in the application. This is an open Delphi app, so I have full Debug Agent access to all data in this grid.

       

      Grid_View.JPG

       

       Here's the SQL call and result set that feeds that grid, this is the data that I'm hoping to test directly without having to look at the application grid above:

      SQL_Data.JPG

       

      I'm not sure what you mean by the variable that hold the resultset, are you talking about the ADO data set, or RecordSet as Microsoft calls it?

       

      Anyway, after sleeping on it and doing some more work this morning, I managed to get it working.  I ended up using CreateADODataSet, call the procedure with parameters to populate the data set, then write the results into a text file, which I can then compare later using a StoredFile.Check call.

      The code is below if anyone is interested.

       

      Thanks.

      Allen

       

       

      // Call the spTST_getBOMByPONumber procedure
      // Write the results to a text file
      procedure GetBOMByPONumber(PONumber, LineNo, olnIDInstanceParm, AsOfDate, oFile);
      var
      DSet : OleVariant;
      rowloop, columnloop, checkloop: integer;
      rowstring, separator, itemNumber: string;
      begin
      // Create the new IAQAADODataset object
      DSet := ADO.CreateADODataset;

      DSet.CursorType := ctStatic;
      // Specify the connection string
      DSet.ConnectionString := 'Provider=MSDASQL.1;Data Source=inSight10';
      // Specify the command type and text
      DSet.CommandType := cmdStoredProc;
      DSet.CommandText := 'spTST_getBOMByPONumber';
      DSet.Parameters.AddParameter();
      DSet.Parameters.Items(0).Name := 'PONumber';
      DSet.Parameters.Items(0).Value := PONumber;

      DSet.Parameters.AddParameter();
      DSet.Parameters.Items(1).Name := 'LineNo';
      DSet.Parameters.Items(1).DataType:=ftInteger;
      DSet.Parameters.Items(1).Value := LineNo;

      DSet.Parameters.AddParameter();
      DSet.Parameters.Items(2).Name := 'olnIDInstanceParm';
      DSet.Parameters.Items(2).DataType:=ftInteger;
      DSet.Parameters.Items(2).Value := olnIDInstanceParm;

      DSet.Parameters.AddParameter();
      DSet.Parameters.Items(3).Name := 'AsOfDate';
      DSet.Parameters.Items(3).DataType:=ftDate;
      DSet.Parameters.Items(3).Value := AsOfDate;

      // Open the dataset
      DSet.Open;
      // Process records
      DSet.First;

      // Build and write file headers
      rowstring := '';
      rowstring := ' ID |itmItemNumber|Qty|PrimaryUOMCode|BOMLevel|itmIsPurchased|itmpCode|itmIDInstance|rotID|dlCode|sttDescription|ProgressState|pltPlantCode';
      oFile.Write(rowstring + #13#10);

      for columnloop := 1 to DSet.RecordCount do
      begin
      // Insert data into the test log
      //Log.Message(DSet.FieldByName('ID').Value, DSet.FieldByName('itmItemNumber').Value);
      rowstring := '';

      // Dump each row to the file
      rowstring := DSet.FieldByName('ID').Value
      + '|' + DSet.FieldByName('itmItemNumber').Value;
      + '|' + aqConvert.VarToStr(DSet.FieldByName('Qty').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('PrimaryUOMCode').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('BOMLevel').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('itmIsPurchased').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('itmpCode').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('itmIDInstance').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('rotID').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('dlCode').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('sttDescription').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('ProgressState').Value)
      + '|' + aqConvert.VarToStr(DSet.FieldByName('pltPlantCode').Value);

      oFile.Write(rowstring + #13#10);

      DSet.Next;
      end;
      DSet.Close;

      end;


      procedure TestBOMFromSQL();
      var ofile, inputstr;
      begin
      oFile := aqFile.OpenTextFile('C:\temp\TestBOMFile.txt', aqFile.faWrite, aqFile.ctANSI, true);
      oFile.Write(inputstr);
      GetBOMByPONumber('SqlTest', 1, 1, '6/12/2015', oFile);
      oFile.Close;
      end;

       

       

  • I updated the code I posted above with a very important optimization. 

    Instead of looping to the dataset EOF:

    set the dataset CursorType := ctStatic;

    Now the RecordCount in the dataset is a row counter, use a loop counter to iterate through the rows:

    for columnloop := 1 to DSet.RecordCount do

     

    This will allow datasets with many columns like mine to run in seconds instead of minutes...

     

    Allen