Jump to content

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


Go to solution Solved by poila,

Recommended Posts

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)

$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)

$counter = $counter + 1
_ExcelBookSaveAs($oExcel, $exportDir & "\" & $username & "_Search_Results_" & @YEAR & @MON & @MDAY & "_" & @HOUR & @MIN & @SEC, "xls")
_ExcelBookClose($oExcel, 1, 0)
Edited by poila
Link to comment
Share on other sites

  • Solution

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:

   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.
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)

   ; 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
      Return 0
EndFunc   ;==>uploadFileToServer


- 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 by poila
Link to comment
Share on other sites

  • 2 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...