Statistikdatenbank füllen

Dieses Beispiel füllt eine Statistik-Datenbanktabelle mit verschiedenen Informationen zum verarbeiteten Dokument.

Erforderliche Tabellenfelder:

CCCDATE (DATETIME)
CCDOCID (CHAR(32))
CCPSTEP (VARCHAR(10))
CCSAPPL (VARCHAR(50))
CCCUSER (VARCHAR(50))
CCBLOBS (INT)

Alle weiteren Felder sind optional.
Die Feldnamen sind wie die Indexfeldnamen in der Queue zu benennen.
Sobald ein passendes Feld gefunden wird, wird es befüllt.

Ist der Parameter TableFieldPrefix gefüllt, muss den Feldnamen in
der Datenbank dieser Präfix vorangestellt werden.

// ==================================================================================
// FillStatisticTable
// ==================================================================================
                                                                   
ccDocument.AllowProcessing := false;

// ----------------------------------------------------------------------------------
// Ressourcen laden
// ----------------------------------------------------------------------------------
ConnectionString     := ccScriptEngine.GetResource('ConnectionString','');
TableName            := ccScriptEngine.GetResource('TableName','');
TableFieldPrefix     := ccScriptEngine.GetResource('TableFieldPrefix','');
MaxIndexAge          := ccScriptEngine.GetResource('MaxIndexAge',0); 
DeleteDuplicates     := ccScriptEngine.GetResource('DeleteDuplicates',false);
DuplicateCheckFields := ccScriptEngine.GetResource('DuplicateCheckFields','');
ProcessStepId        := ccScriptEngine.GetResource('ProcessStepId','EXPORT');
ProcessStepId        := Uppercase(ProcessStepId);

// ----------------------------------------------------------------------------------
// Datenbankverbindung
// ----------------------------------------------------------------------------------
ObjConnC := CreateOleObject('ADODB.Connection');
ObjConnC.Open(ConnectionString);

ObjRSC                  := CreateOleObject('ADODB.RecordSet');
ObjRSC.LockType         := 3;
ObjRSC.ActiveConnection := ObjConnC;

// ----------------------------------------------------------------------------------
// Alte Indexdaten löschen
// ----------------------------------------------------------------------------------
if MaxIndexAge > 0 then
begin
  DelIndexDate := Now - MaxIndexAge;

  Query := 'DELETE FROM '+TableName+' WHERE CCCDATE < "'+FormatDateTime('yyyymmdd hh:nn:ss',DelIndexDate)+'"';
  ObjRSC.Open(Replace(Query, '"',chr(39)));
end;

// ----------------------------------------------------------------------------------
// Duplikate suchen
// ----------------------------------------------------------------------------------
if (DeleteDuplicates = true) and (length(DuplicateCheckFields) > 0) then
begin
  List := TStringList.Create;
  try
    DuplicateCheckFields := DuplicateCheckFields + ',';
    while pos(',',DuplicateCheckFields) > 0 do
    begin
      Field := trim(copy(DuplicateCheckFields,1,pos(',',DuplicateCheckFields)-1));
      delete(DuplicateCheckFields,1,pos(',',DuplicateCheckFields));
      if length(Field) > 0 then List.Add(Field);
    end;

    Query := 'SELECT * FROM '+TableName+' WHERE NOT(CCDOCID = "'+ccDocument.Id+'" AND CCPSTEP = "'+ProcessStepId+'") AND ';

    for i:=0 to List.Count-1 do
    begin
      Query := Query + TableFieldPrefix + List.Strings[i] + ' = "' + ccDocument.GetFieldValue(List.Strings[i]) + '"';
      if i < List.Count-1 then Query := Query + ' AND ';
    end;

    ObjRSC.Open(Replace(Query, '"',chr(39)));

    if ObjRSC.EOF = false then
    begin
      ccDocument.Message         := 'duplicate document found (DOCID '+VarToStr(ObjRSC.Fields('CCDOCID').Value)+') - deleting from queue!';
      ccDocument.AllowProcessing := false;
      ccDocument.DeleteFromQueue := true;
      exit;
    end;

    ObjRSC.Close;
  finally
    List.Free;
  end;
end;

// ----------------------------------------------------------------------------------
// Existierenden Satz suchen
// ----------------------------------------------------------------------------------
Query := 'SELECT * FROM '+TableName+' WHERE CCDOCID = "'+ccDocument.Id+'" AND CCPSTEP = "'+ProcessStepId+'"';
ObjRSC.Open(Replace(Query, '"',chr(39)));

// ----------------------------------------------------------------------------------
// Satz anlegen / updaten
// ----------------------------------------------------------------------------------
if ObjRSC.EOF = true then
begin
  ObjRSC.AddNew;
end;

ObjRSC.Fields('CCCDATE').Value := Now;
ObjRSC.Fields('CCDOCID').Value := ccDocument.Id;
ObjRSC.Fields('CCPSTEP').Value := ProcessStepId;
ObjRSC.Fields('CCSAPPL').Value := ccDocument.Application;
ObjRSC.Fields('CCCUSER').Value := Uppercase(ccDocument.UserName);
ObjRSC.Fields('CCBLOBS').Value := ccDocument.BlobCount;

for i:=0 to ObjRSC.Fields.Count-1 do
begin
  FieldName := ObjRSC.Fields.Item(i).Name;

  if (length(TableFieldPrefix) <= 0) or (pos(TableFieldPrefix,FieldName) > 0) then
  begin
    FieldName := copy(FieldName,length(TableFieldPrefix)+1,length(FieldName));

    FId := ccDocument.GetFieldId(FieldName);
    if FId >= 0 then
    begin
      try
        ObjRSC.Fields(TableFieldPrefix + ccDocument.GetFieldName(FId)).Value  := ccDocument.GetFieldValue(FId);
      except
        // Ignore exceptions (type conflicts)
      end;
    end;
  end;
end;

ObjRSC.Update;

// ----------------------------------------------------------------------------------
// Alles schliessen
// ----------------------------------------------------------------------------------
ObjRSC.Close;
ObjConnC.Close;

// ----------------------------------------------------------------------------------
// Finish
// ----------------------------------------------------------------------------------
ccDocument.AllowProcessing := true;

Browser nicht unterstützt