Following is the tip cut from MSDN regarding to the handling of text and
image :
It’s from the article Top Ten Tips Accessing SQL Through ADO and ASP
Tip 3: Avoid Blobs
Blobs (Binary Large Objects) are stored in SQL Server as either text or
image datatypes. SQL Server does not store the Blob data on the data page
along with the other fields in the row. Instead, SQL Server maintains a
pointer to the Blob data. The data itself is stored in 2KB pages linked
through 16-bit text pointers, meaning that there are really about 1800 bytes
available for actual data storage if the column is not NULL. If the column
is explicitly set to NULL, the storage size will be 0, since there is no
need for the text pointers. This essentially means that storing data in
Blobs will increase your storage requirements in 2KB increments.
While storage size alone may not be a deterrent, there are also functional
limitations with Blobs. For example, if you use a WHERE clause to search on
a text column, you are limited to using the LIKE operator. This can be a
very time consuming and will add a lot of overhead to your application.
Also, when you’re working with large quantities of data, it may become
necessary to read the data in chunks, rather than pulling it out of a column
all at once.
Before using a Blob field, consider the alternatives. If you want to store
images in the database, you may find it more appropriate to store the images
outside of the database, and simply maintain URLs that point to the images
within the table. If you are storing lots of text data, you may find that
rather than using a Blob field, you can denormalize the table and break the
data into varchar(255) fields.
If you still require a Blob field despite the storage overhead and
limitations in functionality, there are a few caveats. If you’re using a
forward-only cursor (the default), you should retrieve the Blob fields from
left to right, and to the right of any scalar values you include in your
SELECT statement. If your underlying table definition looked like this
CREATE TABLE MyTABLE(
Field1 Identity(0, 1),
Field2 Text,
Field3 Int,
Field4 Image
)
retrieve your records using the following SELECT statement:
SELECT Field3, Field2, Field4 FROM MyTable
There is another point to be aware of concerning presentation. If you will
be retrieving images from your database through ASP, you will need to
manipulate the HTTP header information. If you were to retrieve a GIF image
from the database, you’d need to first clear out any existing HTTP header
information, then set the ContentType to Image/Gif. See the article
“Delivering Web Images from SQL Server,” by Scott Stanfield (MIND, July
1998), for a complete discussion of how to do this effectively.
To put additional text on the returned page, you need to create a separate
page to host the image. Figure 8, RetrieveImage.asp, demonstrates how you
can retrieve an image from Pubs. Notice how this page doesn’t write any text
with the Response object. Since I would like to provide some text with the
image, I created an additional page, ShowImage.asp (see Figure 9). Another
page is necessary because once you have set Response.ContentType to
Image/Gif, you cannot write text to the ASP page. ShowImage.asp actually
displays the image by referring to the RetrieveImage.asp in the IMAGE tag’s
SRC argument.
<%
Option Explicit
Dim cnnPubs
Dim rstPub_Info
‘ Clear existing HTTP header info
Response.Expires = 0
Response.Buffer = TRUE
Response.Clear
‘ Set the HTTP header to an image type.
Response.ContentType = “image/gif”
Set cnnPubs = Server.CreateObject(“ADODB.Connection”)
cnnPubs.Open “pubs”, “sa”
Set rstPub_Info = cnnPubs.Execute(“SELECT logo FROM pub_info WHERE _
pub_id=’1389'”)
Response.BinaryWrite rstPub_Info(“logo”)
Response.End
%>
Figure 8 RetrieveImage.asp
<HTML>
<HEAD>
<TITLE>Show the Image</TITLE>
</HEAD>
<BODY>
<H2>Presenting the Logo
/H2>
<!— This page contains a link to the image so that you can display
the text. The RetrieveImage.asp page won’t allow you to write out text
since you have set the ContentType to image/gif” —>
<IMG SRC=”RetrieveImage.asp”>
</BODY>
</HTML>
Figure 9 ShowImage.asp
This two-page trick comes in handy for operations such as providing dynamic
banners while minimizing work for your server. Simply create a static HTML
page, then reference the ASP page through your IMG tag. The ASP page would
be responsible for choosing which banner to display. Figure 10 shows the
output of ShowImage.asp.
Uploading Images to a database
Originally Posted by Captain Proton
Bookmarks