Insert jpg files into SQL server

  • There is a table store a few hundreds jpg file path. Real jpg files were stored at c:\myjpg\ folder.

    IMAGEPATH (varchar(50)), IMAGE (varbinary(max))

    IMAGEPATH --------IMAGE

    -------------------------------

    c:\myjpg\0001.jpg

    c:\myjpg\0002.jpg

    ...

    c:\myjpg\1500.jpg

    How to code to insert jpg files from c:\myjpg\ into SQL as varbinary?

  • 99% of the time, you do this kind of image inserts/updates in a programming language.

    TSQL does not have a native, built in way to do image to disk operations....

    but it's possible to do it via TSQL with a work around via OPENROWSET(BULK:

    INSERT INTO img_item(img_data)

    SELECT * FROM OPENROWSET(BULK N'C:\myfile.jpg', SINGLE_BLOB) AS img_data

    In a .NET application a binary file can be loaded into a byte array, then inserted.

    ' VB.NET example

    ' Load file into a byte array

    Dim fi As New System.IO.FileInfo("c:\myfile.jpg")

    Dim fs As System.IO.FileStream = fi.OpenRead

    Dim lBytes As Long = fs.Length

    Dim myImage(lBytes) As Byte

    fs.Read(myImage, 0, lBytes)

    fs.Close()

    ' Insert binary data into database

    Using myConn as New Data.SqlClient.SqlConnection(My.Settings.myConnectionString)

    Dim myCommand as new Data.SqlClient.SqlCommand("INSERT INTO img_item VALUES (@img_data)", myConn)

    myCommand.Parameters.AddWithValue("@img_data",myImage)

    myConn.open()

    myCommand.ExecuteNonQuery()

    myConn.Close()

    End Using

    finally, using a CLR like this one :

    http://nclsqlclrfile.codeplex.com/

    --MFGetFileImage

    -- Parameters: @FilePath, @FileName

    -- purpose: given a path and filename, return the varbinary of the file to store in the database.

    -- usage:

    CREATE TABLE myImages(id int,filename varchar(200),rawimage varbinary(max) )

    INSERT INTO myImages(id,filename,rawimage)

    SELECT 1,'fedora_spinner.gif',dbo.MFGetFileImage('C:\Data\','fedora_spinner.gif' )

    --MSPSaveFileImage

    -- Parameters: @FilePath,@FileName,@FileBytes

    -- purpose: given an varbinary image column in a table, write that image to disk

    -- usage:

    * --assumes table and the file from the example above for dbo.MFGetFileImage exists already.

    declare @myfile varbinary(max)

    SELECT @myfile = rawimage FROM myImages WHERE id = 1

    EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I would probably use SSIS for this. A ForEach Loop Container containing a Data Flow that uses the Import Column Transformation is a very powerful combination.

    Here is a step-by-step that instead of using a ForEach Loop to gather the list of files uses a flat file that contains the list of the images to import. Either route would work fine.

    Import multiple images to SQL Server using SSIS[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 3 (of 3 total)

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