Link to home
Start Free TrialLog in
Avatar of m9s999
m9s999

asked on

Need Help Extracting Images From A Database

We have a commercial image management system that we use to scan and store all of our documents. I am trying to write a visual basic program with a very simple interface to allow people unfamiliar with the system to search for and view documents. The system uses a MS SQL database. I have looked at the tables and found 2 locations that store binary data. I was able to extract an image from one of the locations using an ADO stream, but the image I get is only the thumbnail and very small. When I try to extract the binary data from the other location, I get an invalid file. I did some research on the Internet, and it looks like there are 2 ways to store images in a database: Binary data or a file pointer. If I look at the file structure on the Server, and I see that there actually .TIF files stored out there for each page. I looked through the database, but could not find a field that has a file location. The properties of the field that I think stores the image are:
DataType-Image
Length-16
Precision-0
Scale-0
Name-Data
Owner-dbo
ADODataType-205

Anyone have any ideas?
ASKER CERTIFIED SOLUTION
Avatar of MYLim
MYLim

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MYLim
MYLim

'*****************************************************************
'*Code below demo how to extract picture to directory you specify.
'*****************************************************************
'Sample of using ADO Stream object to access BLOB data from a MySQL database.
'==========================================================
'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
 
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=127.0.0.1;" _
            & "DATABASE=test;" _
            & "UID=testuser;" _
            & "PWD=12345;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 
conn.CursorLocation = adUseClient
conn.Open
 
'CREATE TABLE FOR SAMPLE CODE
conn.execute "CREATE TABLE files(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, file_name VARCHAR(64) NOT NULL, file_size MEDIUMINT UNSIGNED NOT NULL, file MEDIUMBLOB NOT NULL)"  

'OPEN RECORDSET FOR WRITING
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
 
mystream.Type = adTypeBinary
 
rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic
 
rs.AddNew
 
mystream.Open
mystream.LoadFromFile "c:\myimage.gif"
 
rs!file_name = "myimage.gif"
rs!file_size = mystream.size
rs!file = mystream.read
rs.Update
mystream.Close
rs.Close
 
'OPEN RECORDSET TO READ BLOB
rs.Open "Select * from files WHERE files.file_id = 1", conn  
mystream.Open
mystream.Write rs!File
mystream.SaveToFile "c:\newimage.gif", adSaveCreateOverWrite
mystream.close
rs.close  

'OPEN RECORDSET FOR UPDATE OF BLOB COLUMN
rs.Open "Select * from files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic
mystream.Open
mystream.LoadFromFile "c:\updateimage.gif"
rs!file = mystream.Read
rs.Update
 
mystream.Close
rs.Close
 
'OPEN RECORDSET TO READ UPDATED IMAGE
rs.Open "Select * from files WHERE files.file_id = 1", conn
mystream.Open
mystream.Write rs!file
mystream.SaveToFile "c:\newupdatedimage.gif", adSaveCreateOverWrite
 
mystream.Close
rs.Close
 
conn.execute "DROP TABLE files"
conn.Close
msgbox "Success! Check your C:\ directory for newimage.gif and newupdatedimage.gif"
Avatar of m9s999

ASKER

That code is basically what I used in my VB app. It worked fine for the one table, but not the other.
fieldtype must same with the table that you success retrived image...
normally fieldtype  = BLOB
Avatar of m9s999

ASKER

Would exportig a few of the records and posting them somewhere help? Also, is there some way to tell what format binary data is in?
"what format binary data is in"
what do you means above,fileType ?
Avatar of m9s999

ASKER

Yes. Can I tell what type of file it is.
it seem you have extract it from database 1st...i never try ...

'OPEN RECORDSET TO READ UPDATED IMAGE
rs.Open "Select * from files WHERE files.file_id = 1", conn
mystream.Open
mystream.Write rs!file
mystream.SaveToFile "c:\newupdatedimage.gif", adSaveCreateOverWrite