Superdopey’s Techblog

In het pre DNN 5 tijdperk, maakten veel mensen gebruik van de ’search and replace’ stored procedure van het  bi4ce forum. In  DNN 5 zijn er een aantal velden waarin de content wordt opgeslagen hernoemd (’DesktopHtml’ verandert in ‘Content’) waardoor deze stored procedure niet meer werkt.

Hieronder staat de aangepaste SP voor DNN 5:

CREATE Procedure Replace_Content(
@For as varchar(100),
@With as varchar(100))
AS
--* REPLACES TEXT IN THE DESKTOP HTML MATCHING THE FOR VARIABLE
--* SAMPLE: Replace_Content '/Portals/0/', ''

--* MAIN DECLARATION
DECLARE
@pointer binary(16),
@index INT,
@lenFor INT,
@lenWith INT,
@diff int,
@id INT,
@count INT

--* STANDARD RUNTIME VARIABLES
SET @lenFor = LEN(@For)
SET @diff = @lenFor - LEN(@With)

--* CREATE THE TEMPORARY TABLE
CREATE TABLE #Temporary
(
[id] int,     --MAPS TO YOUR SOURCE TABLE PKID
[oldtext] text, --ORIGINAL SOURCE TEXT (NOT REQUIRED)
[oldlength] int,--ORIGINAL SOURCE LENGTH
[text] text,     --NEW TEXT VALUE
[length] int,     --NEW LENGTH
[cLength] int    --CHECKSUM LENGTH
)

--* LOOP THROUGH THE SOURCE TABLE
--* IDENTIFY ALL RECORDS WHICH MATCH YOUR PATTERN
DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT moduleid
FROM HtmlText
WHERE PATINDEX('%'+@For+'%', Content)>0
OPEN irows

FETCH NEXT FROM irows INTO @id

WHILE (@@FETCH_STATUS = 0)
BEGIN
--* INSERT MATCHING RECORDS INTO THE TEMPORARY TABLE
INSERT INTO #Temporary(id, oldtext, oldlength, text)
SELECT
ModuleID,
Content,
datalength(Content),
Content from HtmlText
where ModuleID = @id

--* GRAB THE POINTER OF THE OBJECT TO BE USED FOR UPDATETEXT
SELECT
@pointer = TEXTPTR(text)
FROM #Temporary
WHERE id=@id
--* GET THE FIRST INDEX OF OUR PATTERN
SELECT
@index = PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id

--* IF WE FOUND ANY ENTRIES, LOOP UNTIL WE REPLACE ALL OF THEM
IF @index > 0
BEGIN
select @count = 0
WHILE (
SELECT
PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id
) > 0
BEGIN
--* KEEP A RUNNING TOTAL OF IDENTIFIED ENTRIES FOR LENGTH CHECKSUM
select @count = @count + 1
SELECT
@index = PATINDEX('%'+@For+'%', text)-1
FROM #Temporary
WHERE id=@id

--* UPDATE THE TEMPORARY VALUE
UPDATETEXT #Temporary.text @pointer @index @lenFor @With
END
--* UPDATE THE TEMPORARY RECORD, SET LENGTH AND CLENGTH VALUES FOR CHECKSUM
UPDATE #Temporary set
length=datalength(text),
cLength=datalength(oldtext) - @count * @diff
WHERE id=@id
END
FETCH NEXT FROM irows INTO @id
END

CLOSE irows
DEALLOCATE irows
--* UPDATE THE DATABASE FOR ALL RECORDS IN THE TEMPORARY
--* WHERE THE CHECKSUM LENGTH MATCHED THE RESULT LENGTH
UPDATE target set
target.Content = t.[text]
FROM
HtmlText target JOIN #Temporary t
ON
target.ModuleId = t.id AND
t.length = t.cLength

--* DELETE ALL RECORDS FROM TEMPORARY WHICH WERE SUCCESSFUL
DELETE from #Temporary where length=clength

--* RETURN ALL ROWS WHICH FAILED LENGTH COMPARISON
--* THIS SHOULD ALWAYS BE EMPTY
select * from #Temporary

--* DROP THE TEMP TABLE AND EXIT
DROP TABLE #Temporary