Post reply

Uploading Tiff files using SQL 2005 and not using any application

  • 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.

  • December 5, 2007 at 11:42 am

    #757411

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 6, 2007 at 5:01 am

    #757605

    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.

  • December 6, 2007 at 7:49 am

    #757673

    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

  • December 8, 2007 at 12:35 am

    #758275

    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

  • December 9, 2007 at 8:22 pm

    #758438

    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.

  • December 10, 2007 at 7:25 am

    #758587

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 11, 2007 at 12:57 am

    #758856

    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)

    Dim objStream

    If Not objFso.FileExists(strFilename) Then Exit Sub

    Set objStream = CreateObject("ADODB.Stream")

    objStream.Type = 1 ' adTypeBinary

    objStream.Open

    objStream.LoadFromFile strFilename

    adoField.Value = objStream.Read

    objStream.Close

    Set objStream = Nothing

    End Sub

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

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