Sunday, November 26, 2006

How to use BLOB field in VB 6 with Firebird

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.
Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.
You'll notice that basically the only different thing is the connection string!

  1. 'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
  2. Dim conn As ADODB.Connection
  3. Set conn = New ADODB.Connection
  4. conn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_2_0\examples\empbuild\test.fdb;UID=SYSDBA;PW D=masterkey"
  5. conn.CursorLocation = adUseClient
  6. conn.Open
  7. 'OPEN RECORDSET FOR WRITING
  8. Dim rs As ADODB.Recordset
  9. Set rs = New ADODB.Recordset
  10. Dim mystream As ADODB.Stream
  11. Set mystream = New ADODB.Stream
  12. mystream.Type = adTypeBinary
  13. rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic
  14. rs.AddNew
  15. mystream.Open
  16. mystream.LoadFromFile "c:\adaptor.jpg"
  17. rs!file_id = 1
  18. rs!file_name = "adaptor.jpg"
  19. rs!file_size = mystream.Size
  20. rs!file_blob = mystream.Read
  21. rs.Update
  22. mystream.Close
  23. rs.Close
  24. 'OPEN RECORDSET TO READ BLOB
  25. rs.Open "Select * from files WHERE files.file_id = 1", conn
  26. mystream.Open
  27. mystream.Write rs!file_blob
  28. mystream.SaveToFile "c:\newimage.jpg", adSaveCreateOverWrite
  29. mystream.Close
  30. rs.Close
  31. 'OPEN RECORDSET FOR UPDATE OF BLOB COLUMN
  32. rs.Open "Select * from files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic
  33. mystream.Open
  34. mystream.LoadFromFile "c:\adaptor.jpg"
  35. rs!file_blob = mystream.Read
  36. rs.Update
  37. mystream.Close
  38. rs.Close
  39. 'OPEN RECORDSET TO READ UPDATED IMAGE
  40. rs.Open "Select * from files WHERE files.file_id = 1", conn
  41. mystream.Open
  42. mystream.Write rs!file_blob
  43. mystream.SaveToFile "c:\newupdatedimage.jpg", adSaveCreateOverWrite
  44. mystream.Close
  45. rs.Close
  46. conn.Close
  47. MsgBox "Success! Check your C:\ directory for newimage.jpg and newupdatedimage.jpg"

No comments: