Forum Discussion

allenj2020's avatar
allenj2020
Contributor
10 years ago

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...
  • 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;