sambuddy Posted March 10, 2009 Share Posted March 10, 2009 Hello All, I am in the process of submitting data to an SQL database using autoit. I am able to submit all text data, but when i try to submit a graphic, i get no errors and i don't get the new entry into the database. I am using the MySQL UDF's from Prog@ndy . I have posted my code below. I think the problem has got to do with how i am passing the contents of the file to the database (i have highlighted these lines in red), but i am not sure how else i should deal with it.Thanks for your help#cs ---------------------------------------------------------------------------- AutoIt Version: 3.2.8.1 (beta) Author: Prog@ndy Script Function: MySQL-Plugin Demo Script #ce ----------------------------------------------------------------------------#include <array.au3>#include "mysql.au3"_MySQL_InitLibrary()If @error Then Exit MsgBox(0, '', "")MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())$MysqlConn = _MySQL_Init()$connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","holden","music")If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)Endif $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "holden", "music")If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))$file=FileOpen("C:\1.bmp",16)if @error <> 0 Then ConsoleWrite("file open error " & @error & @CRLF)Else ConsoleWrite("file " & $file & @CRLF)EndIf$contents=FileRead($file,FileGetSize("c:\1.bmp"))if @error <> 0 Then ConsoleWrite("file read error " & @error & @CRLF)EndIf$query="INSERT INTO album_artwork(artworkID, artwork) values(7," & $contents & ")"ConsoleWrite("file size = " & FileGetSize("c:\1.bmp") & @CRLF)_MySQL_Real_Query($MysqlConn, $query)if @error <> 0 Then ConsoleWrite("SQL statement failed error =" & @error & @CRLF)EndIfexit(0) Link to comment Share on other sites More sharing options...
bo8ster Posted March 10, 2009 Share Posted March 10, 2009 Can you please show the error msg that you get. That will provide clues as to what is going on. Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
sambuddy Posted March 10, 2009 Author Share Posted March 10, 2009 That is part of the problem I don't get any error from the commands i run. It all executes as though it all worked, but there is no graphic in the DB.If i change the following red lines into the following green lines, the text "c:\\1.bmp" gets stored in the dbso it would seem that my problem is more related to the fact that i am trying to submit binary data to the db rather that text data, but i am not sure how i should get around this $query="INSERT INTO album_artwork(artworkID, artwork) values(7," & $contents & ")"_MySQL_Real_Query($MysqlConn, $query,FileGetSize("c:\1.bmp"))$query="INSERT INTO album_artwork(artworkID, artwork) values(7,'c:\\1.bmp')"_MySQL_Real_Query($MysqlConn, $query)Can you please show the error msg that you get. That will provide clues as to what is going on. Link to comment Share on other sites More sharing options...
bo8ster Posted March 10, 2009 Share Posted March 10, 2009 Have you tried checking the mysql log file. There should a reason why the transaction was aborted. Just to be sure, I would make sure that the "artwork" column can take binary data. It is hard to tell without knowing why the transaction is getting rejected. Do your commands work on the commandline or a GUI tool? Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
sambuddy Posted March 10, 2009 Author Share Posted March 10, 2009 Have you tried checking the mysql log file. There should a reason why the transaction was aborted. Just to be sure, I would make sure that the "artwork" column can take binary data.It is hard to tell without knowing why the transaction is getting rejected. Do your commands work on the commandline or a GUI tool?Hello Bo8ster, All of my commands work on the command line EXCEPT for the one where i try to send something to the BLOB. When i issue that, the text string "c:\1.bmp" gets stored in the db instead of the actual file. I am currently looking into how to enable all the logs. I have switched everything on in the admin gui but am still not getting much out of it Thanks Link to comment Share on other sites More sharing options...
bo8ster Posted March 10, 2009 Share Posted March 10, 2009 (edited) okI suggest you look for examples where ppl are saving data files to the db. Once you can get it working on the commandline then you can think about moving it to AutoIt.Its been a while since I last used a BLOB but I remember its relative straight forward as long as the db knows about it.Good luck sambuddy!Edit:I like to use MySQL Query Browser, you can download it free from http://dev.mysql.com/downloads/gui-tools/5.0.html or as a package on lunix. Edited March 10, 2009 by bo8ster Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
sambuddy Posted March 10, 2009 Author Share Posted March 10, 2009 Hello b08ster, I think i may be onto something I have just reduced the size of the graphic from being 1.5 meg down to 9kb by changing it to b/w and making it tiny. But, it worked through autoit that way. It seems to be related with the size of the pictureokI suggest you look for examples where ppl are saving data files to the db. Once you can get it working on the commandline then you can think about moving it to AutoIt.Its been a while since I last used a BLOB but I remember its relative straight forward as long as the db knows about it.Good luck sambuddy!Edit:I like to use MySQL Query Browser, you can download it free from http://dev.mysql.com/downloads/gui-tools/5.0.html or as a package on lunix. Link to comment Share on other sites More sharing options...
bo8ster Posted March 10, 2009 Share Posted March 10, 2009 What is the datatype of the column? From memory (I was using postgres) BLOB is a datatype. Have a look at the schema to see what the db expects. Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
sambuddy Posted March 10, 2009 Author Share Posted March 10, 2009 I am using BLOB (i have also tried LONGBLOB)What is the datatype of the column? From memory (I was using postgres) BLOB is a datatype. Have a look at the schema to see what the db expects. Link to comment Share on other sites More sharing options...
bo8ster Posted March 10, 2009 Share Posted March 10, 2009 See http://dev.mysql.com/doc/refman/5.0/en/blob.html and http://dev.mysql.com/doc/refman/5.0/en/sto...quirements.html. Else try and find some examples. Not sure what else to suggest sorry. I would have suggest LONGBLOG. Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
sambuddy Posted March 11, 2009 Author Share Posted March 11, 2009 Hello Bo8ster, My problem has been solved The following code works as long as you set a parameter on the SQL server that limits the "Maximum Packet Size" It was set to something in the KB range, when i increased it to 10MB, everything started working as it should. Thanks for your patience and help expandcollapse popup#include <array.au3> #include "mysql.au3" dim $file="c:\1.bmp" dim $f_handle _MySQL_InitLibrary() $MysqlConn = _MySQL_Init() $connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","holden","music") If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Endif $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "holden", "music") If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn)) $f_handle=FileOpen($file,16) if @error <> 0 Then ConsoleWrite("file open error " & @error & @CRLF) EndIf $contents=FileRead($f_handle,FileGetSize($file)) if @error <> 0 Then ConsoleWrite("file read error " & @error & @CRLF) EndIf $bmp=String($contents) $query="INSERT INTO album_artwork(artwork) values(" & $bmp & ")" _MySQL_Real_Query($MysqlConn, $query) if @error <> 0 Then ConsoleWrite("SQL statement failed error =" & @error & @CRLF) EndIf _MySQL_Close($MysqlConn) _MySQL_EndLibrary() exit(0) Link to comment Share on other sites More sharing options...
bo8ster Posted March 11, 2009 Share Posted March 11, 2009 No probs, glad you got it working. Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
anandchakru Posted May 4, 2009 Share Posted May 4, 2009 Thank you all... was very helpful, but how are we reading the image back to a ... say file please help, thanks [font="Book Antiqua"]Thanks`A[/font] Link to comment Share on other sites More sharing options...
bo8ster Posted May 4, 2009 Share Posted May 4, 2009 Can you expand on that, what are you trying to do? This was about putting a blob into a db and getting it back - nothing to do with files. Post your code because code says more then your words can. SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y. Use Opt("MustDeclareVars", 1)[topic="84960"]Brett F's Learning To Script with AutoIt V3[/topic][topic="21048"]Valuater's AutoIt 1-2-3, Class... is now in Session[/topic]Contribution: [topic="87994"]Get SVN Rev Number[/topic], [topic="93527"]Control Handle under mouse[/topic], [topic="91966"]A Presentation using AutoIt[/topic], [topic="112756"]Log ConsoleWrite output in Scite[/topic] Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now