Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to insert images or pictures in sql 2005 table Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 8:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:52 AM
Points: 20, Visits: 31
I'm working with a database of mechanical components, need to add the pictures (.tiff) of these components, someone could help me with this ...
Post #1467710
Posted Wednesday, June 26, 2013 8:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:04 AM
Points: 83, Visits: 174
rigonzalez.fuentes (6/26/2013)
I'm working with a database of mechanical components, need to add the pictures (.tiff) of these components, someone could help me with this ...


I'm guessing that the tiff file is less than 10 MB. You can add varbinary column to the table. Convert the image into byte stream and insert it into table.
Post #1467711
Posted Wednesday, June 26, 2013 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
the absolute best way is via a programming language.

you'd need to first establish some sort of relationship to the data...that is, you know that C:\AutoCad\drawing_424.tif
is related to a specific record in your database.


here's a vb.net example i often post, but you'd need to provide very specific details for us to give you more than a skeleton framework for this kind of issue.
 Private Sub BlobFilesToDatabase(ByVal TheFolderPath As String)
'this is a recursive function, that, after processing all the files in the current directory, digs deeper into each subfolder in the current directory.
If Directory.Exists(TheFolderPath) Then
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()

'assuming a table like CREATE TABLE MyImages(ImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,ImageFileName varchar(50),BlobData varbinary(max) )

Dim sql As String = "INSERT INTO MyImages VALUES(@ImageFileName,@BlobData)"
Dim MySqlCommand As New SqlCommand(sql, MyConn)
MySqlCommand.Parameters.Add("@ImageFileName", SqlDbType.VarChar)
MySqlCommand.Parameters.Add("@BlobData", SqlDbType.Image)

For Each TheFilename As String In Directory.GetFiles(TheFolderPath)
If TheFilename.EndsWith("jpg", StringComparison.CurrentCulture) OrElse TheFilename.EndsWith("ico", StringComparison.CurrentCulture) Then
If File.Exists(TheFilename) Then
Dim oFile As System.IO.FileInfo
oFile = New System.IO.FileInfo(TheFilename)

Dim oFileStream As System.IO.FileStream = oFile.OpenRead
Dim lBytes As Long = oFileStream.Length
Dim DiskBlob(lBytes - 1) As Byte
' Read the file into a byte array
oFileStream.Read(DiskBlob, 0, lBytes)
MySqlCommand.Parameters(0).Value = oFile.Name
MySqlCommand.Parameters(1).Value = DiskBlob
MySqlCommand.ExecuteNonQuery()
End If

End If
Next

'now process any sub folders.
For Each TheSubDirectory As String In Directory.GetDirectories(TheFolderPath)
BlobFilesToDatabase(TheSubDirectory)
Next
End If


End Sub


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467716
Posted Wednesday, June 26, 2013 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
You could use a SQLCLR to pull a file from any file system available to the SQL Server.

https://nclsqlclrfile.codeplex.com/

If you have any questions about it let me know..

CEWII
Post #1467724
Posted Wednesday, June 26, 2013 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:52 AM
Points: 20, Visits: 31
Most of the tiff files are less than 100 kb, few reach the 700 kb. First use this instruction but insert the data without image

INSERT INTO Diseños (Id, Partnum, Diseno)
SELECT 1, '3301643-06', BulkColumn
FROM Openrowset( Bulk 'C:\3301643.TIFF', Single_Blob) as EmployeePicture


This would show it in vb.net and excel in some cases


  Post Attachments 
tabla.png (4 views, 6.83 KB)
Post #1467735
Posted Tuesday, March 18, 2014 10:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 2:18 AM
Points: 6, Visits: 11
Rick Gzz (6/26/2013)
Most of the tiff files are less than 100 kb, few reach the 700 kb. First use this instruction but insert the data without image

INSERT INTO Diseños (Id, Partnum, Diseno)
SELECT 1, '3301643-06', BulkColumn
FROM Openrowset( Bulk 'C:\3301643.TIFF', Single_Blob) as EmployeePicture


This would show it in vb.net and excel in some cases



You can see whether this old post help you a little. Sorry for unable to extract some information cuz there is too much information in that post.

http://www.sqlservercentral.com/Forums/Topic364779-9-1.aspx
Post #1552453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse