Insert jpg files into SQL server

Question

Post reply

Insert jpg files into SQL server

adonetok

SSCrazy Eights

Points: 8395
More actions
February 11, 2013 at 8:46 am

#394158

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?

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

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!

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 2

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