image files

Question

image files

Kristin Stariha

Ten Centuries

Points: 1012
More actions
July 7, 2004 at 11:25 am

#162913

HELP! I need to save an image file - SOMEHOW - to a column in the database. I am using Visual Basic as the interface program, and I have no clue how to go about this. I have been warned that it is bet NOT to save images to the database but is this true that it isn't a good idea? I tried this...found it on the microsoft support web site, but I get a compile error of "user-defined type not defined" on the "Dim mstream As ADODB.Stream"

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"
Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite
rs.Close
cn.Close

any ideas anyone??
Kristin

Kristin

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

Best advice - do not store the images in the database! We have an imaging system in my company and all they do is store the location in the database. The images are kept externally to the database.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

neder SSC Eights! Points: 888 More actions · Answer 2

try this link http://www.codeguru.com/forum/showthread.php?threadid=29373

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 3

so the path is listed in its place in the column?

Kristin

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 4

Yes. What happens is that you then set the Picture source of the image to the path and either refresh the form or open a new one (depending on what works best for you). Are you also scanning the images in? You'll need a way to pick the location and store them. I also suggest using URL's where possible as opposed to fixed drive letters. That way you can move to different servers if need be in the future. And preferably have 2 columns. One for the path and one for the filename. That way if you want to burn some images to CD/DVD (say former employees) then you can update the path based on a resign date not null to point to the DVD drive.

I also suggest jpg format for the images. Just smaller size.

Just some thoughts.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Nilssond Hall of Fame Points: 3285 More actions · Answer 5

Check the reference to Microsoft ActiveX Data Objects and make certain you are using a library version which supports the stream object ( 2.5 or higher )

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 6

I'm not scanning the images in, but they are coming in off of a fax server. And I'm trying to make this application EASY EASY for the girls here in the office to use. So I have to figure out a way to save it and store it in the database easily for them. None of this is web based, the company is against it. (we are federally regulated and it gets a little messy with the requirements) Good idea though burning the old stuff onto cd's. Going to plan on that one. And what do you thing about tif images? too big?

thnks for the input

Kristin

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 7

A lot of our images are tif. Just avoid bitmap. Lots of wasted space.

If the fax server saves the file in one location and you are moving them to another location for long-term storage and annotation/rename try looking at this. The following routine was written in MS Access to find what files were FTP'd to us overnight. I then put the list up on a form for files to be selected and processed.

----------------------------
Public Function Find_FTP_Files()

Dim PathName As String
Dim FileName As String
Dim FileDate As Date
Dim FileTime As Date
Dim FileSize As Long
Dim ScanTime As Date

Dim DB As database
Dim FTPRS As Recordset

Dim IndexExist As Boolean
Dim i As Integer
Dim Counter As Integer

IndexExist = True

Set DB = CurrentDb()
If DB.TableDefs("FTP_Files").Indexes.Count = 0 Then
    IndexExist = False
ElseIf DB.TableDefs("FTP_Files").Indexes.Count > 0 Then
    Counter = 0
    IndexExist = False
    i = DB.TableDefs("FTP_Files").Indexes.Count
    Do Until Counter = i
        If CStr(DB.TableDefs("FTP_Files").Indexes(Counter).name) = "FileNameIndex" Then
            IndexExist = True
            Exit Do
        End If
        Counter = Counter + 1
    Loop
End If
If IndexExist = False Then
    DB.Execute "CREATE INDEX FileNameIndex ON FTP_Files (File_Name);"
End If

Set FTPRS = DB.OpenRecordset("FTP_Files")
With FTPRS
    .Index = "FileNameIndex"
End With

If FTPRS.EOF = False Then
    FTPRS.MoveFirst

    If DateDiff("n", FTPRS!Last_Scan, Now()) < 1 Then
        FTPRS.Close
        Exit Function
    End If
End If
'If FTPRS.EOF = True Then Exit Function

ScanTime = Now()

PathName = "R:\MSNAUT\"

If Right(PathName, 1)  "\" Then PathName = PathName & "\"

FileName = Dir(PathName & "*.*")
Do Until FileName = ""
    FileDate = DateValue(FileDateTime(PathName & FileName))
    FileTime = TimeValue(FileDateTime(PathName & FileName))
    FileSize = FileLen(PathName & FileName)
    With FTPRS
        If .RecordCount > 0 Then .MoveFirst
        .Seek "=", FileName
        If .NoMatch Then
            .AddNew
            !File_Name = FileName
            !File_Date = FileDate
            !File_Time = FileTime
            !File_Size = FileSize
            !Last_Scan = ScanTime
            !New_File = True
            !Uploaded = False
            .Update
        Else
            If !File_Time = FileTime And !File_Date = FileDate And !File_Size = FileSize Then
                .Edit
                !New_File = False
                
                !Last_Scan = ScanTime
                .Update
            Else
                .Edit
                !File_Date = FileDate
                !File_Time = FileTime
                !File_Size = FileSize
                !Last_Scan = ScanTime
                !New_File = True
                !Uploaded = False
                .Update
            End If
        End If
    End With
    FileName = Dir
Loop

FTPRS.MoveFirst

Do Until FTPRS.EOF
    If Dir(PathName & FTPRS!File_Name) = "" Then FTPRS.Delete
    FTPRS.MoveNext
Loop

FTPRS.Close

End Function
----------------------------

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

You might want to have a look at this:

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

and decide then which way to go.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Prabhash Nandan Kumar Grasshopper Points: 19 More actions · Answer 9

You can store the images in the SQL Column which has a data type of image. But it is always advisable not to store the image in the database, as it makes tha database heavier and thus performance can decrease. Any how If you want to store the images in the database then this code will certainly help you.

Dim cn As New ADODB.Connection

Dim rs As ADODB.Recordset

Dim DataFile As Integer, Fl As Long, Chunks As Integer

Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String

Const ChunkSize As Integer = 16384

Const conChunkSize = 100

Dim str1 As String

Private Sub SavePicture(strFileNm As String)

If cn.State = adStateOpen Then cn.Close

cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<USERNAME>;Password=<PASSWORD>;Initial Catalog=<DATABASE NAME>;Data Source=<SQL SERVER NAME>"

Dim strsql As String

Set rs = New Recordset

strsql = "SELECT * FROM <TABLE NAME>"

rs.Open strsql, cn, adOpenDynamic, adLockOptimistic

If Not rs.EOF Then

DataFile = 1

Open strFileNm For Binary Access Read As DataFile

Fl = LOF(DataFile)

If Fl = 0 Then Close DataFile: Exit Sub

Chunks = Fl \ ChunkSize

Fragment = Fl Mod ChunkSize

ReDim Chunk(Fragment)

Get DataFile, , Chunk()

rs!<FEILD NAME>.AppendChunk Chunk()

ReDim Chunk(ChunkSize)

For i = 1 To Chunks

Get DataFile, , Chunk()

rs!<FEILD NAME>.AppendChunk Chunk()

Next i

Close DataFile

END IF

End Sub

In the above mentioned code the <> tag is to be replaced by the approriate namd. You have to be ensure that the column (feild) you are using to hold the image data should be of Image Datatype.

You can retrieve this picture with the help of the below mentioned function

Private Sub ShowPic()

DataFile = 1

Open "pictemp" For Binary Access Write As DataFile

Fl = rs!<FEILD NAME>.ActualSize

If Fl = 0 Then Close DataFile: Exit Sub

Chunks = Fl \ ChunkSize

Fragment = Fl Mod ChunkSize

ReDim Chunk(Fragment)

Chunk() = rs!<FEILD NAME>.GetChunk(Fragment)

Put DataFile, , Chunk()

For i = 1 To Chunks

ReDim Buffer(ChunkSize)

Chunk() = rs!<FEILD NAME>.GetChunk(ChunkSize)

Put DataFile, , Chunk()

Next i

Close DataFile

FileName = "pictemp"

Picture1.Picture = LoadPicture(FileName)

End Sub

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 10

Hmmmm, ok, but where am I when I'm saving this file? I need to create a vb interface to save the file to the table. do I hook this code to a command on my form? Where does the image actually tie in? Should I save it first to a file on my hard drive - or on the server?

Kristin

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 11

HAH! oh my god, this is way over my head Isn't there a simple way to do this? We get like 300 faxes in a day that have to be saved to the database somehow. As a link or whatever, and I have to have "push button" access to any/all of them for the people that work here....

what do you think?

Kristin

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 12

Kristin,

Send me an e-mail at " jimpen at go2netmail .com " and I'll e-mail you an Access database that does some file handling and an easy interface for users.

You'll have to do some mods to point at the DB server and file movements, but it will give you the general idea.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 13

you're my hero going to shoot you an email right now...

kristin

Kristin

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 14

So not letting me send you an email..... here is mine.

kstariha72 at msn.com

Kristin