Solved upload file to blob field in mysql BE

[zazar]

Hi everyone,
I create a simple database wich works great with few users. Recently we needed to move the back-end to MySql and just after this they aks me to allow users to upload file to the database. Moving the db to MySql weren't so difficult (i read a lot of thread). But now in the table "report" I created a blob field (mediumblob field actually, since each file wouldn't exceed 5Mo) and I have a form with select and upload button, and another one to download back the file later, the only thing that missing is the code behind those 2 last buttons. I read many and many of post, as i did to build the app, but didn't find the solution. (I'm on it since sunday, trying many thing out).
I found some code to select and upload file into local table but nothing help me to upload file to MySql back-end and download it back when there is need.

I already have this :
Private sub btnSelect_Click () Dim f As Object Dim strFile As String Dim strFolder As String Dim varItem As Variant Set f = Application.FileDialog(3) With f With .Filters .Clear .Add "", "*.pdf", 1 End With .InitialFileName = "" .AllowMultiSelect = False If f.Show Then For Each varItem In f.SelectedItems strFile = Dir(varItem) strFolder = Left(varItem, Len(varItem) - Len(strFile)) MsgBox "Folder: " & strFolder & vbCrLf & _ "File: " & strFile Me.filepath.Value = strFolder & strFile Next End If Set f = Nothing End With End Sub

Now i need this :

Private sub btnUpload_Click () 'here the code to upload it End sub

and :

Private sub btnDownload_Click () 'here the code to download the file from MySql to local disk (the targer folder could be like "C:\downloads") End Sub

I post this here and not in a MySql space because I think all the code will be on the access side.
Already thanks to everyone who can help me with this.

 

[cheekybuddha]

Hi,

Are you using linked tables?

I dug these old functions I wrote out of my code library:

'***************************************************************************************
' Function  : fDownloadFile
' DateTime  : 01-08-2005 16:26
' Author    : dm
' Purpose   :
'***************************************************************************************
'
Public Function fDownloadFile(strFileNamePath As String, _
                              objFld As Object) As Boolean
On Error GoTo Err_fDownloadFile

  Dim blRet As Boolean
  Dim bBuffer() As Byte
  Dim iFileNo  As Integer

  iFileNo = FreeFile
  Open strFileNamePath For Binary As iFileNo
  ReDim bBuffer(objFld.ActualSize)
  bBuffer = objFld.GetChunk(objFld.ActualSize)
  Put iFileNo, , bBuffer()
  Close iFileNo
  blRet = True
  
Exit_fDownloadFile:
  fDownloadFile = blRet
  Exit Function

Err_fDownloadFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fDownloadFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basLoadFile", , "Error: " & Err.Number
  End Select
  Resume Exit_fDownloadFile

End Function

'***************************************************************************************
' Function  : fLoadFileToTable
' DateTime  : 01-08-2005 16:26
' Author    : dm
' Purpose   :
'***************************************************************************************
'
Public Function fLoadFileToTable(strFileNamePath As String, _
                                 objFldToFill As Object) As Boolean
On Error GoTo Err_fLoadFileToTable

  Dim blRet As Boolean
  Dim bBuffer() As Byte
  Dim iFileNo  As Integer

  iFileNo = FreeFile
  Open strFileNamePath For Binary As iFileNo
  ReDim bBuffer(LOF(iFileNo))
  Get iFileNo, , bBuffer
  objFldToFill.AppendChunk bBuffer
  Close iFileNo
  blRet = True
  
Exit_fLoadFileToTable:
  fLoadFileToTable = blRet
  Exit Function

Err_fLoadFileToTable:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fLoadFileToTable" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basLoadFile", , "Error: " & Err.Number
  End Select
  Resume Exit_fLoadFileToTable

End Function

IIRC(!), You can pass either a DAO or ADODB Recordset.Field object, so if you are using linked tables you can open a recordset based on your table and pass the appropriate path and field object as you iterate through.

hth,

d

 

[cheekybuddha]

You can also use ADODB.Stream too.

See here or here

 

[zazar]

Yes i'm using linking table and thank you, hope I get through this issue with your code and links.

 

[cheekybuddha]

Keep us posted with your progress.

d

 

[zazar]

Hi guys, I got it worked.
cheekybuddha I followed yours links and from one of those pages to another one, I found the code below :

'Function: BlobToFile - Extracts the data in a binary field to a disk file. 'Parameter: strFile - Full path and filename of the destination file. 'Parameter: Field - The field containing the blob. 'Return: The length of the data extracted. Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long On Error GoTo BlobToFileError Dim nFileNum As Integer Dim abytData() As Byte BlobToFile = 0 nFileNum = FreeFile Open strFile For Binary Access Write As nFileNum abytData = Field Put #nFileNum, , abytData BlobToFile = LOF(nFileNum) BlobToFileExit: If nFileNum > 0 Then Close nFileNum Exit Function BlobToFileError: MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _ "Error writing file in BlobToFile" BlobToFile = 0 Resume BlobToFileExit End Function 'Function: FileToBlob - Loads a file into a binary field. 'Parameter: strFile - Full path and filename of the source file. 'Parameter: Field - The binary field into which the file is to be loaded. Public Function FileToBlob(strFile As String, ByRef Field As Object) On Error GoTo FileToBlobError If Len(Dir(strFile)) > 0 Then Dim nFileNum As Integer Dim byteData() As Byte nFileNum = FreeFile() Open strFile For Binary Access Read As nFileNum If LOF(nFileNum) > 0 Then ReDim byteData(1 To LOF(nFileNum)) Get #nFileNum, , byteData Field = byteData End If Else MsgBox "Error: File not found", vbCritical, _ "Error reading file in FileToBlob" End If FileToBlobExit: If nFileNum > 0 Then Close nFileNum Exit Function FileToBlobError: MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _ "Error reading file in FileToBlob" Resume FileToBlobExit End Function

I successfully integrated it to my project.

Thanks you all for the help.

 

[freuzo]

Hi,
I was trying to do something like that. Your code helped me a lot.
Thanks.

 

[cheekybuddha]

I'm glad we could help!