retrieving and parsing text blobs

[amhaskar]

I have a database in which I store page content (html, links, text, template data, etc.) for webpages in text blobs. What I need to do is pull this information from the database and format special keywords that may or may not be found in the html or template data when outputting to the user.

content => template => output webpage

The content and template are pulled from the db, and I use the Vb Replace function to replace any occurance of <@pageContent@> in the template with the value of the content record. However, if the values are large they don't always get pulled from the db (memory issues?).

body = Replace(templateRS("template"), "<@pageContent@>", contentRS("content"))

Any ideas or suggestions on how to rectify this issue? Or maybe another data structure that I could use?

I am avoiding using physical text files to store the content due to space constraints on the server. Furthermore, I am trying to stay away from a finite amount of NVARCHAR fields because I can't keep an upperbound on the amount of content or size of the template.

Any and all help is appreciated, thanks in advance!

[russell]

please post the code in question, and a sample of the blob, as well as the truncated output for us to examine, so that we may help out

[amhaskar]

Below is the essential code I am using. If the pageRS("story") is large the value of body only maintains the value of templateRS("story"). However, I have reason to believe that if the value of templateRS("story") is large as well that in the end the variable "body" will not store the proper contents.

pageRS("template") refers to the id of the template being referenced in templateRS


Set pageRS = Server.CreateObject("ADODB.Recordset")
pageRS.Open "SELECT * FROM "&table_prefix&"content WHERE id='"&Request.QueryString("id")&"'", Conn

body = ""

Set templateRS = Server.CreateObject("ADODB.Recordset")
templateRS.Open "SELECT * FROM "&table_prefix&"template WHERE id='"&pageRS("template")&"'", Conn

if templateRS("active") = true then
body = Replace(templateRS("story"), "<@pageContent@>", pageRS("story"))
Response.Write("pageRS('content') = " & body & "<br />" & VbCrLf)
end if

templateRS.Close
Set templateRS = nothing

pageRS.Close
Set pageRS = nothing

If the value of template("story") =
hello <br /> <@pageContent@> <hr />
and the value of pageRS("story") =
hi how are you what is going on the dog jumped over the moon<br /> <a href='blah blah'>some link</a>
the output would return:

hello <br /> <hr />

[amhaskar]

Issue:
For large amounts of data such as binary large object fields, SQL Server stores a pointer to the value in the database, instead of the full value. As a result when using the VbScript Replace function to store the modified text blob data into a variable, the pointer may be lost. Thus, resulting in no value being returned.

Note: SQL Server 2000 has a method to store blob data in row, but it is only recommended for relatively small data sizes.

Solution:
Use the GetChunk method for Recordset fields to retrieve all the data for each text blob and store it in a variable. Then use the VbScript Replace function.

GetChunk Usage:
Field.GetChunk(Size in bytes)

If less than the specified size of data to read from storage exists, GetChunk will return the remaining data without padding the end with extra spaces.

Example:

' Divide the length of the text blob into workable pieces
' 512 refers to the number of bytes to read from storage

num_template_chunks = templateRS("story").ActualSize / 512
for i = 0 to num_template_chunks
template = template & templateRS("story").GetChunk(512)
next