Uploading Tiff files using SQL 2005 and not using any application

Question

Post reply

Uploading Tiff files using SQL 2005 and not using any application

lavadan

Grasshopper

Points: 11
More actions
December 5, 2007 at 10:57 am

#177227

Hi Folks,
I have some tiff files which i need to upload into sql server 2005 database. Is it possible to do this without using any other application like asp/c#.
I will be having 10,000 tiff images in a hard drive. I have to upload them into the database table. The tiff file name is same as the emp-id in the emp table.
I have gone through this forum and found similar situations but all of them done with some applications.Can i do this using only sql. Please guide me in this as i am new to sql.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Nope, not with SQL only, but there are plenty of examples on how to load an image using programming languages.

I think you might be able to do this with scripting eve.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

SQL 2000 came with an undocumented command line utility called TextCopy.exe. It will be in the Binn directory on a SQL 2000 server.

It may have been part of the SDK, but I cannot remember. You may find some documentation on the web.

I have not tried it with a SQL 2005 database, but it will probably work fine.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 3

I have a note that the following should work in 2005. (I must have seen it on a forum somewhere.) I have never tried it as ADO/ADO.Net seems less hassle.

CREATE TABLE myTable(Document varbinary(max))

GO

INSERT INTO myTable(Document)

SELECT *

FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) AS Document

GO

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

Hi Ken

I tried it and it works.

As it is stored in binary, even if i look at the data, i can't determine whether image is successfully loaded and when it would be extracted, it is correct.

I think there is need to use some front end language to display it.

Anam

ecomkid SSC Veteran Points: 221 More actions · Answer 5

Maybe I am wrong here, but I would not upload 10,000 images to a db, but would upload to the server and put a path to the files to keep thedatabase small.

e.g.

/imagepath/imagename.tiff

However, it depends on your business requirements. If you cannot give write permissions on the server, I would store in the database. If you have more than one person who will be the admin to the images, store in the database. If performance is a real issue, do not store in the database. If all you are doing is showing an image with a path, do not store in a database.

Hope this helps.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

You would need some application to query the database and then stream the image data and view it. These are just bits to SQL Server, it has no idea how to "present it" to you and show you the image. A quick web page (query on PK, return image) would do it.

I wouldn't hesitate to put 10,000 images in the database based on load or anything else. SQL Server can handle it, but like the previous poster, I tend to store them outside the database and then store a path in SQL Server.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

Here is some VB script which you can easily adapt to ASP. Although the ADO field and file name are passed, they both expect an already existing File System Object.

If your app needs to manipulate these via a bank of clustered web servers then having the files in a database comes in really handy.

Sub BlobToFile(adoField, strFilename)

Dim objStream

If objFso.FileExists(strFilename) Then objFso.DeleteFile strFilename, True ' Force=True

Set objStream = CreateObject("ADODB.Stream")

objStream.Type = 1 ' adTypeBinary

objStream.Open

objStream.Write adoField.Value

objStream.SaveToFile strFilename, 1 ' Options=adSaveCreateNotExist=1

objStream.Close

Set objStream = Nothing

End Sub

Sub FileToBlob(adoField, strFilename)