How do I read file contents and upload as BLOB into MySQL Server?

[poila]

Hi to all,

I am writing a feature to an existing internal AutoIt program, based on a client's request.

The requested feature would be to grab the file via a button, which reads the file's path.

Below the file browsing button would be a button to upload the file into a MySQL server.

Initially, my method would be to read the contents of the file provided in the filepath, then convert the contents of the file into binary data, and then store it into a column of a table as a BLOB.

I am not sure whether if this is the best approach to storing text/Word/PDF files into the MySQL Server in this way, if the program used is an AutoIt program.

So far, I am only able to generate Excel files, but only because there was already an existing BLOB column in an existing table.

Sample code:

$sTemplateName = "SearchTemplate"
$sFilePath = @ScriptDir & "\temp\" & $sTemplateName & ".xls"
$iFileExists = FileExists($sFilePath)

; If template file not found, create it.
If (Not $iFileExists) Then
generateExcelFile($sTemplateName, $sFilePath)
EndIf

$iFileExists = FileExists($sFilePath)

If $iFileExists = 1 Then
$oExcel = _ExcelBookOpen($sFilePath, 0)
_ExcelWriteCell($oExcel, "Search conducted on " & _NowCalc() & " by " & $username & "", 2, 1)

$counter = 0
While Not $recordSet.EOF
_ExcelWriteCell($oExcel, 1 + $counter, 6 + $counter, 1)
_ExcelWriteCell($oExcel, getName("User", $recordSet.Fields("UserID").value), 6 + $counter, 2)
_ExcelWriteCell($oExcel, $recordSet.Fields("InternalName").value, 6 + $counter, 3)
_ExcelWriteCell($oExcel, getName("Product", $recordSet.Fields("ProductID").value), 6 + $counter, 4)
_ExcelWriteCell($oExcel, $recordSet.Fields("Serial").value, 6 + $counter, 5)
_ExcelWriteCell($oExcel, getName("Location", $recordSet.Fields("LocationID").value), 6 + $counter, 6)
_ExcelWriteCell($oExcel, $recordSet.Fields("Remarks").value, 6 + $counter, 7)
_ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("LastModified").value, 1), 6 + $counter, 8)
_ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("CreationDate").value, 1), 6 + $counter, 9)

$recordSet.MoveNext
$counter = $counter + 1
WEnd
_ExcelBookSaveAs($oExcel, $exportDir & "\" & $username & "_Search_Results_" & @YEAR & @MON & @MDAY & "_" & @HOUR & @MIN & @SEC, "xls")
_ExcelBookClose($oExcel, 1, 0)
EndIf

Edited September 3, 2014 by poila

[poila]

Apparently, there was no one replying to this thread, but somehow after digging *a lot* deeper, I found some gems:

Read and write binary file: '?do=embed' frameborder='0' data-embedContent>>

Getting file extension: '?do=embed' frameborder='0' data-embedContent>>

Opening a file (automatically) with its default corresponding application: '?do=embed' frameborder='0' data-embedContent>>

Adapting some user's existing code to suit my customized needs: '?do=embed' frameborder='0' data-embedContent>>

Final sample:

#cs
   Function name:   uploadFileToServer
   Description:     Inserts binary file into BLOB column of file-storing table
   Parameter(s):    $sFilePath - String pinpointing to location of file
                    $iUserID - Integer containing the current user's ID
                    $iFileID - Integer containing the FileID referenced from FileRecord table
   Return Value(s): On success, returns 1. On failure, returns 0.
#ce
Func uploadFileToServer($sFilePath, $iUserID, $iFileID)
   ; Get the file extension
   Local $sTempFileExt = getFileExtension($sFilePath)

   ; Open the file in Binary Mode
   Local $hTempFile = FileOpen($sFilePath, 16)
   Local $iFileSize = FileGetSize($sFilePath)
   Local $sBinaryChars = FileRead($hTempFile,$iFileSize)
   FileClose($hTempFile)

   ; Create SQL statement for insertion of file
   Local $sSQLstatement_insertFile = "INSERT INTO filestorage (FileID,UserID,BinaryData,FileExtension) VALUES (" & $iFileID & "," & $iUserID & "," & $sBinaryChars & ",'" & $sTempFileExt & "')"
   Local $setFileBinaryData = _Query($global_SQLInstance, $sSQLstatement_insertFile)

   If $setFileBinaryData <> 0 Then
      Return 1
   Else
      Return 0
   EndIf
EndFunc   ;==>uploadFileToServer

Pre-requisites:

- You must have the MySQL.au3 file from user cdkid (this can be found in the AutoIt Tutorials Wiki)

- getFileExtension() is a user-defined function, not native to AutoIt. Feel free to write your own UDF to get a file extension

- The table must have a LONGBLOB column

EDIT: This does not check the actual file size *before* the upload process.

If you wish to contribute on limiting user's file size before calling my written function, please feel free to reply. Thanks.

Edited September 4, 2014 by poila

[bullmoose20]

how about a way to read back the blob data from the MySQL server?