745.aspx

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