Sign in to follow this  
Followers 0
sambuddy

Storing a graphic into MySQL using BLOB

14 posts in this topic

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)

EndIf

exit(0)

Share this post


Link to post
Share on other sites



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]

Share this post


Link to post
Share on other sites

That is part of the problem :P

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 db

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

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 :P

Thanks

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

ok

I 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 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]

Share this post


Link to post
Share on other sites

Hello b08ster,

I think i may be onto something :P

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 picture

ok

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

Hello Bo8ster,

My problem has been solved :P

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

:unsure:

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

#13 ·  Posted

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]

Share this post


Link to post
Share on other sites

#14 ·  Posted

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]

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
Sign in to follow this  
Followers 0