Fix for broken images in .TEXT RSS feed
.TEXT replaces links to images on the local site (http://text2blogger.appspot.com/static/egilh/Images/something) with a relative urls (/blog/something). This is a feature in some scenarios as you can move the blog to a new domain without problems. It causes a problem for me though: some RSS readers are not smart enough to render images with relative links. The problem got worse after I moved my feeds to FeedBurner as my RSS feed http://feeds.feedburner.com/egilh uses relative urls to images (/blog/images/something). Some RSS readers use the link element in the RSS to correctly compose the url as http://www.egilh.com/blog/images but not all.
The links are changed before the post is insert in the DB. The latest .TEXT code base adds some new features like search, so recompiling it may add new problems. Luckily the RSS feed uses the stored procedure blog_GetConditionalEntries to extract the data so the issue can be fixed there.
I changed the stored procedure for RSS feeds (PostConfig=17). The text of the post is a text field so I could not use the tsql replace function :-( Converting to a varchar, and truncating at 8kb, is not nice as it may leave invalid XML so I had to do it the hard way:
- Create a temporary table with the records we should return
- Use UPDATETEXT to replace /blog/image with blog root + /blog/image
- Return the data in the temporary table
Replace the existing blog_GetConditionalEntries stored procedure with this one after you have taken a backup to use fully qualified url to local images in .TEXT RSS feeds:
CREATE PROCEDURE blog_GetConditionalEntries
(
@ItemCount int,
@PostType int,
@PostConfig int,
@BlogID int
)
AS
DECLARE
@FindString varchar(8000),
@ReplaceString varchar(8000),
@TextPointer varbinary( 16 ) ,
@DeleteLength int,
@Offset int
IF @PostConfig = 17
BEGIN
-- Insert the top ItemCount records into a temporary table
set rowcount @ItemCount
SELECT blog_Content.BlogID, blog_Content.[ID], blog_Content.Title, blog_Content.DateAdded, blog_Content.[Text],
blog_Content.[Description], blog_Content.SourceUrl, blog_Content.PostType, blog_Content.Author,
blog_Content.Email, blog_Content.SourceName, blog_Content.DateUpdated, blog_Content.TitleUrl,
blog_Content.FeedBackCount, blog_Content.ParentID, Blog_Content.PostConfig, blog_Content.EntryName,
Blog_Config.Host, Blog_Config.Application
INTO #fixImageUrls
FROM blog_Content inner join Blog_Config on (Blog_Content.BlogID = Blog_Config.BlogID)
WHERE blog_Content.PostType=@PostType and blog_Content.BlogID = @BlogID
and blog_Content.PostConfig & @PostConfig = @PostConfig
ORDER BY blog_Content.[dateadded] desc
-- Construct the find and replace strings using the NAME and HOST of the blog
SELECT TOP 1 @FindString = '"/' + Application + '/image', @ReplaceString = '"http://www.' + Host + '/' + Application + '/image'
FROM #fixImageUrls
SET @DeleteLength = len( @FindString )
SET @OffSet = 0
-- Replace all the occurences of FindString with ReplaceString
WHILE ( SELECT count( * )
FROM #fixImageUrls
WHERE patindex( '%' + @FindString + '%', [Text]) <> 0) > 0
BEGIN
SELECT @TextPointer = textptr( [Text]), @OffSet = patindex( '%' + @FindString + '%', [Text]) - 1
FROM #fixImageUrls
WHERE patindex( '%' + @FindString + '%', [Text]) <> 0
UPDATETEXT #fixImageUrls.[Text] @TextPointer @OffSet @DeleteLength @ReplaceString
END
-- Return data and drop temp table
SELECT * FROM #fixImageUrls
DROP TABLE #fixImageUrls
END
ELSE
BEGIN
SET rowcount @ItemCount
SELECT blog_Content.BlogID, blog_Content.[ID], blog_Content.Title, blog_Content.DateAdded, blog_Content.[Text], blog_Content.[Description],
blog_Content.SourceUrl, blog_Content.PostType, blog_Content.Author, blog_Content.Email, blog_Content.SourceName, blog_Content.DateUpdated, blog_Content.TitleUrl,
blog_Content.FeedBackCount, blog_Content.ParentID, Blog_Content.PostConfig, blog_Content.EntryName FROM blog_Content
WHERE blog_Content.PostType=@PostType and blog_Content.BlogID = @BlogID
and blog_Content.PostConfig & @PostConfig = @PostConfig
ORDER BY blog_Content.[dateadded] desc
END
GO
