dbo.ObjectTree Bug Fix: Impacts ImportFile Dashboard

posted Oct 21, 2016, 10:04 AM by Eric Patrick
The dbo.ObjectTree user-defined function (UDF) in qbo.DB has been updated to handle an edge case resulting in timeouts. When uploading a file to import, the Attachment must inserted before the ImportFile is inserted. Thus, for brief period of time, Attachment.Object = 'ImportFile' and Attachment.ObjectID = NULL. Once the ImportFile has been created, the Attachment.ObjectID is updated accordingly.

If an extranet user attempts to import a document, the standard Attachment Extranet filter leverages dbo.ObjectTree, which enters an infinite loop, causing a timeout:

WHILE @Object IS NOT NULL BEGIN
INSERT INTO @ObjectTree (Object, ObjectID, Generation) VALUES (@Object, @ObjectID, @Generation)
-- No matching row in Entity, so @Object is never changed
SELECT @Object = Parent, @ObjectID = ParentID, @Generation = @Generation - 1 FROM Entity WHERE Object = @Object AND ObjectID = @ObjectID
END

 The fix is to check for a NULL Object and ObjectID:

WHILE @Object IS NOT NULL AND @ObjectID IS NOT NULL BEGIN
INSERT INTO @ObjectTree (Object, ObjectID, Generation) VALUES (@Object, @ObjectID, @Generation)
SELECT @Object = Parent, @ObjectID = ParentID, @Generation = @Generation - 1 FROM Entity WHERE Object = @Object AND ObjectID = @ObjectID
END


Comments