We help IT Professionals succeed at work.

Store and Retreive images with MS SQL Server and VB

cquarles
cquarles asked
on
I have a need to store and retrieve images with MS SQL Server 7.0 and Visual Basic.  Any ideas?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
IN SQL 7 (and SQL 6.x and 2000), the data type IMAGE should work, even if it's not recommended to use databases for large image repositories. Rather keep the filesystem for this tasks...

In VB, you can store the image in a binary format (read the file in binary mode, and store the data as binary chain).
In SQL Server, there is a command line tool which can be used for that, see my next post as example.

CHeers
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
CREATE PROC SaveFileToTable
     @Database      VARCHAR(50),
     @Table      VARCHAR(50),
     @Column      VARCHAR(50),
     @WhereClause      VARCHAR(200),
     @FilePath      VARCHAR(500),
     @Server      VARCHAR(50) = NULL,
     @User        VARCHAR(50) = NULL,
     @Password      VARCHAR(50) = ''
AS
------------------------------------------------------------------------------------------------------------------------------------------------------------
-- This procedure can save the content file to column of IMAGE or TEXT data type
-- Please use this tool (textcopy) with great caution, as it can easily hang the server if parameters
are missing
------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL AS VARCHAR(2000)

SET  @Server = COALESCE ( @Server, @@ServerName )
SET  @User = COALESCE ( @User, system_user )

SET  @SQL = 'UPDATE [%s]..[%s] SET [%s] = "" %s'
EXEC master..xp_sprintf @SQL OUTPUT, @SQL, @Database, @Table, @Column, @WhereClause
EXEC (@SQL)

SET  @SQL = 'EXEC master..xp_cmdshell "textcopy /S %s /U %s /P %s /D %s /T %s /C %s /W ""%s"" /F ""%s""
/I"'
EXEC master..xp_sprintf @SQL OUTPUT, @SQL, @Server, @User, @Password, @Database, @Table, @Column, @WhereClause,
@FilePath
EXEC (@SQL)
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
CREATE PROC SaveTableToFile
     @Database      VARCHAR(50),
     @Table      VARCHAR(50),
     @Column      VARCHAR(50),
     @WhereClause      VARCHAR(200),
     @FilePath      VARCHAR(500),
     @Server      VARCHAR(50) = NULL,
     @User        VARCHAR(50) = NULL,
     @Password      VARCHAR(50) = ''
------------------------------------------------------------------------------------------------------------------------------------------------------------
-- This procedure can save the content of IMAGE or TEXT column to a file
-- Please use this tool (textcopy) with great caution, as it can easily hang the server if parameters
are missing
------------------------------------------------------------------------------------------------------------------------------------------------------------
AS
DECLARE @SQL AS VARCHAR(2000)

SET  @Server = COALESCE ( @Server, @@ServerName )
SET  @User = COALESCE ( @User, system_user )

SET  @SQL = 'EXEC master..xp_cmdshell "textcopy /S %s /U %s /P %s /D %s /T %s /C %s /W ""%s"" /F ""%s""
/O"'
EXEC master..xp_sprintf @SQL OUTPUT, @SQL, @Server, @User, @Password, @Database, @Table, @Column, @WhereClause,
@FilePath
EXEC (@SQL)
BRONZE EXPERT
Top Expert 2012
Commented:
If you are using ADO 2.5 and above you can use the Stream object.  Assuming you have created a column called "Image" of type Image, then something like the following from a previous question should do the trick.:

Sub AddImage(rs As ADODB.Recordset, ByVal FileName As String)
Dim stm As ADODB.Stream

Set stm = New ADODB.Stream
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile FileName
 
  'Insert the binary object into the table.
  rs.AddNew
  rs.Fields("Image").Value = .Read
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub

To display the image just reverse the operation:

Sub ShowImage(rs As ADODB.Recordset)
Const InitDir = "C:\temp\" 'Some temporary folder
Dim stm As ADODB.Stream
Dim TempFile As String

If Not IsNull(rs.Fields("Image").Value) Then
  Set stm = New ADODB.Stream
  With stm
     .Type = adTypeBinary
     .Open
     .Write rs.Fields("Image").Value
     TempFile = InitDir & "Temp.jpg"
     .SaveToFile TempFile, adSaveCreateOverWrite
     .Close
  End With
  Set stm = Nothing
  Set Picture1.Picture = LoadPicture(TempFile)
Else
  Set Picture1.Picture = LoadPicture
End If

End Sub

This may need some minor debugging and error handling, but is the general idea.

Anthony

Commented:
I know this is not an answer but just me 2 cents :

You may want to think twice about storing the image right in the database -- do you really need to do this ? Would it be better if you just stored a "pointer" or OS path to your image (i.e. c:\images\myimage.gif)  If you store the image in the db, you'll most likely need to pull it out and binary write it to a file before you use it anyway !

Anyway, that's just conventional thought, and there are some scenarios where they have to be put in the db, but it is quite a pain...  Good luck !



wqw

Commented:
well, the simplest way to do it in VB is through a property bag:

    Dim oBag        As PropertyBag
    Dim oPicture    As StdPicture

    '--- store
    Set oBag = New PropertyBag
    oBag.WriteProperty "Pic", oPicture
    rs!Image.Value = oBag.Contents
   
    '--- read
    Set oBag = New PropertyBag
    oBag.Contents = rs!Image.Value
    Set oPicture = oBag.ReadProperty("Pic")


the PROPER way to do it is here: http://www.domaindlx.com/e_morcillo/scripts/showtip.asp?tip=pcarr

this is the way Access 2k does it (i suppose) and is more economical on GIF/JPG formats than the property bag solution.

HTH,

</wqw>
cquarles:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.