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!
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.
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:
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;
