jfudickar wrote:Hey this looks like a remedy table
Yes! You are absolutely right. I am trying to write some code to automate Remedy change request tickets, based on the findings of a program called Retina, made by eEye Digital.
I have resolved my issue based on another message I have seen in the forum. I still don't understand why the .Post method just sits there and eats up all the memory. But here's what I did to get around the problem:
Code: Select all
if NewHitList.Count>0 then
begin
PostIt(TH_STATUS,'Creating '+IntToStr(NewHitList.Count)+' new Remedy tickets');
Query.SQL.Clear;
Query.SQL.Add('SELECT * FROM Properties');
Query.SQL.Add('WHERE [Data]=''ChangeID''');
Query.Open;
if Query.RecordCount>0 then ChangeID:=Query.FieldByName('Value').AsString
else ChangeID:='00000000000';
Query.Close;
ARCRID:='EURS'+ChangeID;
PostIt(TH_STATUS,ARCRID);
OraQuery1.SQL.Clear;
OraQuery1.SQL.Add('INSERT INTO T176(C1,C3,C5,C6,C7,C8,C260000001,C200000003,C200000004,C200000005,C200000006,C200000007,C200000012,C240000001,C240000005,C240000006,C240000007,C240000015,C240000017,C260000128)');
OraQuery1.SQL.Add('VALUES (:C1,:C3,:C5,:C6,:C7,:C8,:C260000001,:C200000003,:C200000004,:C200000005,:C200000006,:C200000007,:C200000012,:C240000001,:C240000005,:C240000006,:C240000007,:C240000015,:C240000017,:C260000128)');
OraQuery1.ParamByName('C3').AsInteger:=DateTimeToInt(Now);
OraQuery1.ParamByName('C5').AsString:='GatherInfo';
OraQuery1.ParamByName('C6').AsInteger:=DateTimeToInt(Now);
OraQuery1.ParamByName('C7').AsInteger:=0;
OraQuery1.ParamByName('C260000001').AsInteger:=5;
OraQuery1.ParamByName('C200000003').AsString:='Change';
OraQuery1.ParamByName('C200000004').AsString:='Information Assurance';
OraQuery1.ParamByName('C200000005').AsString:='Accreditation';
OraQuery1.ParamByName('C200000006').AsString:='N86';
OraQuery1.ParamByName('C200000007').AsString:='NCTS';
OraQuery1.ParamByName('C200000012').AsString:='EU Naples';
OraQuery1.ParamByName('C240000001').AsString:='Automated Retina Scan Import';
OraQuery1.ParamByName('C240000005').AsString:='retina.scan';
OraQuery1.ParamByName('C240000006').AsString:='EU NASM - RETINA SCANS';
OraQuery1.ParamByName('C240000015').AsString:='';
OraQuery1.ParamByName('C240000017').AsInteger:=1;
OraQuery1.ParamByName('C260000128').AsInteger:=0;
OraQuery2.SQL.Clear;
OraQuery2.SQL.Add('INSERT INTO H176(ENTRYID)');
OraQuery2.SQL.Add('VALUES (:ENTRYID)');
for i:=0 to NewHitList.Count-1 do
begin
j:=NewHitList.Count-i;
PostIt(TH_INFO,'Complete: '+IntToStr(i)+' ('+Format('%2.2f',[(i*100)/NewHitList.Count])+'%)'+' Remaining: '+IntToStr(j)+' ('+Format('%2.2f',[(j * 100) / NewHitList.Count])+'%)');
NewHit:=NewHitList[i];
IP:=ItemExtract(1,NewHit,'~');
AuditID:=ItemExtract(2,NewHit,'~');
SevCode:=ItemExtract(3,NewHit,'~');
FixInformation:=ItemExtract(4,NewHit,'~');
AuditName:=ItemExtract(5,NewHit,'~');
MAC:=ItemExtract(6,NewHit,'~');
ChangeID:=IntToStr(StrToInt(ChangeID)+1);
ChangeID:=StringOfChar('0',11-Length(ChangeID))+ChangeID;
ARCRID:='EURS'+ChangeID;
Query.SQL.Clear;
Query.SQL.Add('SELECT * FROM Properties');
Query.SQL.Add('WHERE Data = ''ChangeID''');
Query.Open;
if Query.RecordCount>0 then
begin
Query.Edit;
Query.FieldByName('Value').AsString:=ChangeID;
end
else
begin
Query.Append;
Query.FieldByName('Data').AsString:='ChangeID';
Query.FieldByName('Value').AsString:=ChangeID;
end;
Query.Post;
Query.Close;
if RightStr(ARCRID,2)='00' then PostIt(TH_STATUS,ARCRID);
OraQuery1.ParamByName('C1').AsString:=ARCRID;
OraQuery1.ParamByName('C8').AsString:='IP: '+IP+' MAC: '+MAC+' Audit ID '+AuditID;
OraQuery1.ParamByName('C240000007').AsString:='IP: '+IP+' MAC: '+MAC+' Severity: '+SevCode+' Retina Audit ID: '+AuditID+' Fix Information: '+FixInformation;
OraQuery1.Execute;
OraQuery2.ParamByName('ENTRYID').AsString:=ARCRID;
OraQuery2.Execute;
Query.SQL.Clear;
Query.SQL.Add('UPDATE RetinaScans');
Query.SQL.Add('SET [AR_CRID] = '''+ARCRID+''', [AR_CRS] = ''New''');
Query.SQL.Add('WHERE IP = '''+IP+''' AND AuditID = '''+AuditID+'''');
Query.ExecSQL;
PostIt(TH_INFO,IntToStr(j)+' '+Format('%2.2f%s',[((i+1) * 100) / NewHitList.Count,'%']));
end;
PostIt(TH_STATUS,ARCRID);
end;
So... now I use an OraQuery to do an Insert and then set the values by making them parameters that I set before I .Execute the query. Some values are static for all new tickets so I set those outside the loop. The calculated ones are the set inside the loop and the query is executed.
This runs much faster than my first attempt using the OraTable component. And... it doesn't eat up ANY extra memory. Which means this method wins, hands down!
I hope they can figure out the OraTable.Post thing, anyway.
Later...