February 11, 2013 at 8:46 am
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?
February 11, 2013 at 8:54 am
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
February 11, 2013 at 8:28 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy