Jump to content
poila

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

Recommended Posts

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 by poila

Share this post


Link to post
Share on other sites
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 by poila
  • Like 1

Share this post


Link to post
Share on other sites
bullmoose20

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

Share this post


Link to post
Share on other sites

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

  • Similar Content

    • Case85
      By Case85
      Hi for all!
       
      After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server.
      I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script.
      I plan to use the MySQL connector heavily and continuously on long time.
      The developed connector must be have a small memory footprint.
      My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details.
      Thank you for all.
    • AnonymousX
      By AnonymousX
      Hello,
      I need to pull data from company intranet website. I created a script that I can give a list of project numbers and it will open up the the related webpage for each project, save the html comments for that project, then move on to the next. 
      However my problem comes in that each time I open up a project it locks it for other users, and if you just exit the page it keeps it locked, you have to actually press the "close" button on the page. So I'm not sure how to deal with this as I end up just locking every project in my name when I run my program as I don't understand the nature of how the website is built. I don't understand how to close with just using my automation code without manually having to press the stupid button. 
      My Function:
      Func Getscript($ProjectAddress) ;getting the page source code and storing it into text file for easy reading it $file = FileOpen(@ScriptDir & "\source.txt", 10) $IE = _IECreate($ProjectAddress & ".html", 0, 0) $source = _IEDocReadHTML($IE) FileWrite($file, $source) $target_source = _StringBetween($source, "<BODY>", "</BODY>") ; only take content between the specified tags _IEQuit($IE) FileClose($file) return $target_source[0] EndFunc ;==>Getscript  
      Source Code I got from website that I think is relevant:
      function closeForm(){ if(topButtonEnabled("closeButton")){ parent.mainFrame.closeForm(); } } <button id="closeButton" class="saveCloseButton" style="LEFT: 77px;" onclick=closeForm()>&nbsp;Close&nbsp;</button>  
      Any Ideas of how to tell website to either activate the closeform function or just give it the command to close?
       
      Thanks
    • Jibberish
      By Jibberish
      Junior Programmer here... 
      Not much experience with opening, changing and closing files.
      I am trying to replace strings in a Text file except StringReplace does not actually replace the text.
      Here is a sample of my code...
      #include <File.au3> #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> Local $iStrReturn = 0 Local $hFile Local $sText Local $sNewText ; Location of File to be read $sFileName = "C:\Temp\MyPlayer.exe.config" ; The default is FALSE. We want to change this to TRUE $bLoopChecked = True CheckBox() Func Checkbox() $hFile = FileOpen($sFileName,$FO_READ) ; Open file in read mode to get text If $hFile = -1 Then MsgBox($MB_SYSTEMMODAL, "", "An error occurred when Opening the file.") Exit EndIf FileSetPos($hFile, 0, 0) ; No idea if I need to do this, grasping at straws $sText = FileRead($hFile) ; Read the file into $sText If $sText = 1 Then MsgBox($MB_SYSTEMMODAL, "", "An error occurred when reading/writing the file.") Exit Else FileClose($hFile) ; Finished reading the file into $sText, so close the file. FileFlush($hFile) ; Manual says to use FileFlush between File Close and Open so here it is EndIf MsgBox(0,"Before Replacement",$sText) ; Displays the text read from the file to make sure something is there. ; Loop Check If $bLoopChecked = True Then ; Find the string return > 0 for success $iStrReturn = StringInStr('"<add key="LoopCheckbox" value=""False" />"', "False") ;MsgBox(0,"", "LoopCheckBox is " & $iStrReturn) If $iStrReturn > 0 Then ; If StringInStr returned > 0 the it found the string! ; The Meat of the code. This is where we have to replace "False" with "True" $sNewText = StringReplace($sText, '"<add key="LoopCheckbox" value="False" />"', '"<add key="LoopCheckbox" value="True" />"') MsgBox(0,"After Replacement",$sNewText) ; Display the text to see if it worked. $hFile = FileOpen($sFileName,$FO_OVERWRITE) ; Reopen the file to write to it, overwriting everything. FileWrite($hFile,$sNewText) ; Write the text to the file FileClose($hFile) ; Close the file EndIf EndIf EndFunc This is the file I am reading...
      <?xml version="1.0" encoding="utf-8"?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6"/> </startup> <appSettings> <add key="LoopCheckbox" value="false"/> </appSettings> </configuration> I tried opening the file with $FO_UTF8 and $FO_UTF8_NOBOM but got errors opening the file.
      The MsgBox "After Replacement" shows the value is still false.
    • wimhek
      By wimhek
      Is it possible , and how can I read and write txt files from Icloud (apple service) ?
      Let me try to explain my application.
      On my Ipad and Iphone I create txt files. On my windows computer it is possible to read and modify these files manually, by logging in on www.icloud.com.
      What I want to make is an auto-it script who reads the txt file and create an new txt file on www.icloud.com,  so I can acces these on my ipad and/or phone.
       
      Thank you.
×