Saving Images in SQL Servers Image Datatype

I would like to store images in and retrieve images from SQL Server. And the images are to be stored and retrieved from ASP. How do I do it?Hi..

Check out in this web site

http://www.sqlteam.com/item.asp?ItemID=986

Regards,
Selva Balaji B.sql

Saving documents in SQL2005

Hi,
I was wondering if I can save documents e.g. pdf, word, excel or anyother format in sql2005.
If yes what datatype should I use and what would be the best way to go about it.
Thanks in advanceYou could

But you would be better of just storing the file name and path of a file server in the database and storing the documents on the file server

Look up image datatype in BOL|||Thanks for the quick reply. So your suggestion is saving the docs on file server and the path and file name in sql server which is what I am doing at this time... but I thought saving docs in sql itself would have been a better option for security and ease of use etc.

Can you please give me some reasons why would it be better if I not save files on sql e.g. lots of load etc. so I will give that explaination to my boss here :).
Thanks|||From what I understand it'a a maint nightmare, plus you have to get the data out chunks, it's not a simple operation, and then you have to put them all back together

You have BOL?

http://msdn2.microsoft.com/en-us/library/ms130214.aspx|||Oh thank you for the link there for BOL :).
And I would go with your suggestion for using file server instead of SQL for saving files now it does makes sense.

Thank you very much for the help.

Save and Retrieve PDF Files in SQL

Using ASP.net I need to be able to save and retrieve PDF Files in SQL.

I believe the best way to do this is through a BLOB datatype.

I have been searching (without luck) for a tutorial/code sample explaining how to do this.

Any help would be greatly appreciated.

Chris

p.s. I know many prefer to store the files on the server and simply store pointers to the files in SQL, but I need to store the actual files in SQL.

Search for storing images inside SQL Server, the idea is the same. A blob is a blob.

The answer has been given at least 3 times in this forum alone once you know that.

|||

Thanks for the response.

Unfortunately, my searching skills must not be that good.

I'm having trouble finding an answer to my question (I did find someone else asking the same questio, but you responded to him suggesting he search as well).

In any case, attempting to search the web I have found conflicting advice. In particular people are recommending different datatypes for storage (BLOB, Image, and VarBinary).

Specifically I need to store about 1,000 PDF's in my table (each pdf will be under 100k in size).

Any suggestions on the best datatype to use? That should help me with my searching.

Thanks a lot,

Chris

|||

The create table statement below is from AdventureWorks modify it for your use and try the thread below take your pick of code sample. If you know the files will be 100k you can use the thumbnail but run some tests. Hope this helps.

CREATE TABLE [ProductPhoto] (
[ProductPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductPhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY CLUSTERED
(
[ProductPhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

http://forums.asp.net/thread/1401879.aspx

|||

Caddre,

Thanks for trying to help. I'm not sure I understand your reply though. I know how to create a table and records, my questions are:

1. Should I be using an Image, Blob, or VarBinary datatype to store pdf's?

2. Once I know what type I should be using is there some sample code for storing and retrieving that data?

Thanks though,

Chris

|||Varbinary is like Varchar meaning variable length PDF is IMAGE because the file will never change and there are several codes to store you need ExecuteNonQuery, to retrieve you need ExecuteReader or ExecuteScalar and those code are in Programming .NET by Jeff Prosise or in the link I posted. Hope this helps.|||

Image.

SQL Server does not have a "BLOB" data type.

Varbinary is limited to 8000 characters (in SQL Server 2000), so unless every PDF you want to store is less than 8k, this is a poor choice. There are other reasons as well, but the size limit should kill the idea by itself.

If you are using SQL Server 2005 (or later), you can use varbinary(max), but I see little reason to switch from image which works in both.

|||

Here's how to save:

ProtectedSub btnSave_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnSave.Click

If FileUpload1.HasFileThen

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdInsertAsNew SqlCommand("INSERT INTO Attachments(MimeType,Filename,Data) VALUES (@.MimeType,@.Filename,@.Data) SELECT SCOPE_IDENTITY()", conn)

cmdInsert.Parameters.Add(New SqlParameter("@.MimeType", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@.Filename", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@.Data", SqlDbType.Image))

Dim bArray(FileUpload1.PostedFile.ContentLength - 1)AsByte

FileUpload1.PostedFile.InputStream.Read(bArray, 0, FileUpload1.PostedFile.ContentLength)

cmdInsert.Parameters("@.MimeType").Value = FileUpload1.PostedFile.ContentType

cmdInsert.Parameters("@.Filename").Value = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName).ToLower

cmdInsert.Parameters("@.Data").Value = bArray

EndIf

Dim xAsInteger = cmdInsert.ExecuteScalar

conn.Close()

EndIf

EndSub

Here's how to retrieve:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdAsNew SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn)

cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = Request.QueryString("ID")

Dim drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader("Content-type", dr("MimeType"))

Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")

Dim buffer()AsByte = dr("Data")

Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

EndSub

Save a large string in database

hi

is there a datatype thatcan handel a very large string ( i dont want to save it in a file and save the file in the bd ) i want to save it as it is

thanx a lot

Varchar can hold 8000 characters now. Use Text for longer strings.|||Hi,

SQL Server 2005 introduced VARCHAR(MAX) which can hold up to 2^31 byte of data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

i use sql server 2000 and its has text and it can hold up to 2^31-1

but i need a bigger datatype

|||

TEXT and IMAGE columns both have the ~2GB limit that you have identified and there are, unfortunately, no other options available for you to store larger data directly in SQL Server 2000. This same limit will be hit with the varchar(max) column types in SQL Server 2005 that Jens mentioned previously.

If it's feasible, it would obviously be helpful to segment the data such that it can be stored in columns of this size. Failing that, I think you've identified a workable solution to store some kind of URI (UNC path, URL, etc.) and allow the client app to take care of retrieving the file as a separate "query".

|||+1 for the file approach as if you hit the limit you will have no other chance, but consider this paper for storing a BLOB in the database or on the file system.

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de