I am trying to put an image into a MySQL BLOB field.
I have tried :
mystream.LoadFromFile "C:\Temp\JClIRA.jpg"
rs!Image = mystream.Read
and I have tried AppendChunk.
All the code executes without an error, but the field remains {null}
If I debug.print rs("Image") I see a large chunk of data.
If I debug.print Len(rs("Image")) I get something like 24654
But the field *stll* ends up {null}
If I use TOAD and Load Fom File into the field, it works, and I can then read the record and save it to a file, and that works.
Dim file_num As String
Dim file_length As Long
Dim bytes() As Byte
Dim num_blocks As Long
Dim left_over As Long
Dim block_num As Long
Dim sFileName As String
Dim rs As New ADODB.Recordset
Const BLOCK_SIZE = 16384
rs.Open "SELECT Image FROM tblDRSImages WHERE ID = 0", cnCP, adOpenDynamic, adLockOptimistic
rs.AddNew
sFileName = "C:\Temp\JClIRA.jpg"
file_num = FreeFile
Open sFileName For Binary Access Read As #file_num
file_length = LOF(file_num)
If file_length > 0 Then
num_blocks = file_length / BLOCK_SIZE
left_over = file_length Mod BLOCK_SIZE
ReDim bytes(BLOCK_SIZE)
For block_num = 1 To num_blocks
Get #file_num, , bytes()
rs("Image").AppendChunk bytes()
Next block_num
If left_over > 0 Then
ReDim bytes(left_over)
Get #file_num, , bytes()
rs("Image").AppendChunk bytes()
End If
Close #file_num
End If
rs.Update
rs.Close
In both cases the result is a {Null} value in the field.
If I do DEbug.print rs("Image!) I see a large block of data,
If I do Debug.Print LEN(rs("Image")) I get 24356
But the resultant field is still {Null}
Using TOAD I can edit the record and Load From File and it populates the field correctly, and the following code works perfectly:
rs.Open "SELECT Image FROM tblDRSImages WHERE ID = 5", cnCP
I see that you're initially selecting no records, then adding a new one before setting the image field.... what about ID... what about the rest of the fields in the row? I'm guessing that ID is the PKey, right? That's probably the problem right there... You're not giving it the info it needs in order to update the field properly. Even if ID is an autoincrement field, it still needs to be part of the select so thaat the recordset has it. At least that's the way I've always done it.
Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID
The opened the record and both methods *apparently* work and the record does contain data.
*but* -
The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.
rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP
Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID
The opened the record and both methods *apparently* work and the record does contain data.
*but* -
The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.
rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP
Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID
The opened the record and both methods *apparently* work and the record does contain data.
*but* -
The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.
rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP
Yeah, now that you mentioned it, I seem to remember now that's how we ended up doing it with SQL Server & Sprocs... we'd insert the record first, using a dummy marker for the BLOB &H011x ... or something like that... then make a second pass with the stream/byte array and do an UPDATE to set the BLOB field. (gads, that was like some nearly 20+ years ago for me, where has the time gone!? I'm surprised I even remembered that.) It was a real pain in the tookas. What was weird - if I remember right - was that if we inserted the binary data straight through an insert in a SQL script, it would be fine... it was only ever an issue through ADO ... I don't know that we ever found out what the reason for it was. Getting the data was far easier. Go figure.
Not sure what else to suggest. Working fine here creating and populating a new Jet 4.0 MDB.
Code:
Private Function PopulateDb() As Boolean
'Returns True on failure.
Dim InsertRS As ADODB.Recordset
Dim Fields As Variant
Dim PicStream As ADODB.Stream
Dim DirItem As String
Dim DotPos As Long
Dim FileName As String
Dim FileExt As String
On Error Resume Next
GetAttr PICFOLDER
If Err = 0 Then
On Error GoTo 0
Set InsertRS = New ADODB.Recordset
InsertRS.Open "Pictures", Conn, adOpenForwardOnly, adLockOptimistic, adCmdTable
Fields = Array(1, 2, 3)
Set PicStream = New ADODB.Stream
PicStream.Type = adTypeBinary
DirItem = Dir$(PICFOLDER & "\*.*", vbNormal)
Do While Len(DirItem) > 0
If (GetAttr(PICFOLDER & "\" & DirItem) And vbDirectory) = 0 Then
DotPos = InStrRev(DirItem, ".")
If DotPos > 0 Then
FileName = Left$(DirItem, DotPos - 1)
FileExt = Mid$(DirItem, DotPos + 1)
If InStr(SUPPORTED_EXTENSIONS, "$" & LCase$(FileExt) & "$") Then
With PicStream
.Open
.LoadFromFile PICFOLDER & "\" & DirItem
InsertRS.AddNew Fields, _
Array(FileName, FileExt, PicStream.Read(adReadAll))
.Close
End With
Else
InsertRS.AddNew Fields, Array(FileName, FileExt, Null)
End If
End If
End If
DirItem = Dir$()
Loop
InsertRS.Close
Else
On Error GoTo 0
MsgBox "No source folder """ & PICFOLDER & """"
PopulateDb = True
End If
End Function
It is probably cleaner though to just use VB6's native I/O to do a binary read into a Byte array and call it good. Use of an ADO Stream here is really a fallback technique for more the limited VBScript language as used in old ASP pages.
Sad to see so many people still copy/pasting stale old ASP VBScript snippets as VB when VB is so much more powerful.
Most of the attachment size is due to sample picture files.
You're opening up the whole table there... so when you then add a row, it has all the pkey info... the way the OP was going about it, ADO didn't have all of the PKey info as far as I could tell... he was only selecting his image field, returning a dummy recordset, adding a row.. so it had no context with which to add the pkey info when he then updated the database. My thought (since I didn't have a way to test it) was to at least include the ID field in the select so the RS had the PKey schema info and could then insert correctly... On the other hand... I do remember having similar issues back in the day when we were doing something similar, and having to do a two-step process, in which we'd insert the record first, get the ID, then do an update to get the binary data in. But that was some time ago, things may have (and probably have) changed since then, and it's possible I'm not remmbering something right, so take it with a grian of salt (or two grains).
As far as reading in the file, I agree... I've always read everything into a byte array, then used appendChunk to get it into the database. Never once did that fail me.
Field.AppendChunk is another overused ADO feature originally intended mainly for ASP scripts. It may have been necessary way back in ADO 2.0 and before when ASP scripts were the main ADO client. You can still use it in low-memory scenarios but it hasn't been a preferred approach in two decades. AppendChunk of the entire BLOB is just plain hilarious.
I don't think my open type has anything to do with his problem. For Jet 4.0 opening the table directly would be better but I'm not sure his MySQL connector supports that. The original version of the program I posted above use a Jet stored procedure to insert new rows and worked just fine:
Code:
CREATE PROC [InsertPic] (NewName TEXT(255), NewExt TEXT(12), NewPic IMAGE) AS
INSERT INTO [Pictures] ([FileName], [FileExt], [FilePic])
VALUES (NewName, NewExt, NewPic)
Any of those are preferable to a crude technique like opening an updatable query and adding to that. But even the convoluted approach of an updatable query should still be aware of the table's keys including the primary key.
As far as I can tell he didn't say records failed to update. His problem seems to be data corruption writing the BLOB column of the table. I still think the Stream object is being used incorrectly in his code.