pears.DBCleanupOrphanBlobstore

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DBCleanupOrphanBlobstore" IS 
{CREATE FUNCTION DBCleanupOrphanBlobstore 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN countordo CHAR(1))
RETURNS INTEGER
BEGIN
  DECLARE @RC INTEGER;
  SET @RC = 0;
  IF countordo = 'C' THEN
    SELECT COUNT(*) INTO @RC FROM Blobstore WHERE 
     (
     (((class = 'V') OR(class = 'J')) AND NOT EXISTS(SELECT * FROM person WHERE personid = Blobstore.id)) OR 
     ((class = 'O') AND NOT EXISTS(SELECT * FROM OLEDocument WHERE OLEDocumentid = Blobstore.id)) OR 
     ((class = 'L') AND NOT EXISTS(SELECT * FROM Contactevent WHERE Contacteventid = Blobstore.id)) OR 
     ((class = 'M') AND NOT EXISTS(SELECT * FROM MailMerge WHERE MailMergeid = Blobstore.id)) OR 
     ((class = 'T') AND NOT EXISTS(SELECT * FROM TemplateStore WHERE Templateid = Blobstore.id)) 
     );
    RETURN @RC
  END IF;
  DELETE FROM Blobstore WHERE((class = 'V') OR(class = 'J')) AND
    NOT EXISTS(SELECT * FROM person WHERE personid = Blobstore.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM Blobstore WHERE(class = 'O') AND
    NOT EXISTS(SELECT * FROM OLEDocument WHERE OLEDocumentid = Blobstore.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM Blobstore WHERE(class = 'L') AND
    NOT EXISTS(SELECT * FROM ContactEvent WHERE Contacteventid = Blobstore.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM Blobstore WHERE(class = 'M') AND
    NOT EXISTS(SELECT * FROM MailMerge WHERE MailMergeid = Blobstore.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM Blobstore WHERE(class = 'T') AND
    NOT EXISTS(SELECT * FROM TemplateStore WHERE Templateid = Blobstore.id);
  SET @RC = @RC + (SELECT @@rowcount);
  CALL AuditLog('CLEANUP',NULL,string(@RC,' Unattached Blobstore Records deleted '),NULL,NULL);
  RETURN @RC;
END
}