Blob size issue

[Clark]

Hello all

I have a tricky problem that I have been wrestling with for a few days now, but cannot seem to make any headway. I'm not sure if it is an AutoIT related issue, or a mySQL issue, so I will try here first.

The Mission

Read in a file, store it in a database blob field, retrieve said file from database, and present to user. Files will generally be Word or Excel or jpgs.

So, to some code. I'm going to leave out the boring stuff like starting mySQL, var declarations and the like, to get to the nub of the matter.

global $chars,$sSQL,$name="test.xlsx",$type="Excel",$iRval,$hDBHandle,$aResult

global $file = FileOpen($name, 16)

global $size = FileGetSize($name)

So here we just open the file in binary mode, and get the size. The size reported of the file above is 10253 bytes, which corresponds to the Windows directory listing of 11k. All good so far.

Next, I read the entire file into a variable and then close the input file:

$chars = FileRead($file,$size)

FileClose($file)

Just to check the integrity of the contents of the $chars variable, I write the whole thing back out to another file (this is just for testing purposes, to get my head around what is happening):

$file = FileOpen("testoutput.xlsx",2)

Filewrite($file,$chars)

FileClose($file)

I think do a checksum on both the input and output files. They are identical as you would expect.

Now comes the part where it starts to go pear shaped. I write the $chars out to the database. (The "content" field below is defined as a mediumblob.)

$sSQL = "INSERT INTO `test`.`upload2` (`name`, `type`, `size`, `content` ) " _

& "VALUES (""" & $name & """, """ _

& $type & """, """ & $size & """, """ & binary($chars) & """);"

$iRval = _SQL_Execute($hDBHandle,$sSQL)

if $iRval = $SQL_ERROR Then

msgbox(0,"Error","Error inserting row !")

Else

msgbox(0,"Success","Record saved")

EndIf

The record saves fine, and a quick peek into the database shows the blob is stored in the "content" field. (BTW, the "binary" function in the above sql statement was just a test. It made no difference using it or not.)

Now at this stage I can either retrieve the same row using SQL or go directly to the database using MySQL Workbench and retrieve the blob data. It doesn't matter which I use the result is the same.

The result is that the blob is now exactly twice the size of the input ($chars) plus two bytes. And I have no idea why. And it is driving me crazy.

I am pretty sure mySQL is not at fault, as I can manually input a file into the blob field above, and then retrieve it, and input and output will be identical.

I'm at a road block on this and need some help kind people.

I would even look at an alternative solution. Something whereby users can drop attachments into my application, and then (other users) can view them at a later stage.

Thanks in advance

Clark

[alankam58]

hi Clark,

have you solve this issues?

i found some problem in your insert command for MySQL.

should it be like below:-

$sSQL = "INSERT INTO `test`.`upload2` (`name`, `type`, `size`, `content` ) " _

& "VALUES ("&"'" & $name & "'", "'" _

& $type & "'", "'" & $size & "'", "'" & binary($chars) & "'")

i m a beginner and trying to get and image in mysql database too, so sorry if it a mistake.

[alankam58]

Hi Clark,

i try some script of my own and it work. i strore image and file the same way.

$name = "D:ProjectsMySQL Databasetest.PNG"

$sSQL = "INSERT INTO `test`.`pic` (`caption`, `content`) " _

& "VALUES (" & "'" & $name & "','" & $name & "')"

$SQLInstance.execute($sSQL)

[Clark]

Hi Alankam

DId you also try retrieving the image and see that it is the same size as when you wrote it to the database?

regards

Clark

[alankam58]

Hi clark

i try it with a .txt file. but for excel 2007 .xlsx. is not going to work because the fileopen function don't work for microsoft document type.

for picture u can use the attach menthod but for txt file u need to use fileopen, fileread

Global $file = FileOpen($name, 0)

global $size = FileGetSize($name)

$chars = FileRead($file,$size)

FileClose($file)

$sSQL = "INSERT INTO `test`.`pic` (`caption`, `img`) " _

& "VALUES (" & "'" & $name & "','" & $chars & "')"

let u know when i get the excel to work.

is there anyone else can help us?

Your help would be very must appreciated

[jchd]

@Clark,

When you concatenate your binary data into the SQL statement string, the binary data is converted to a string (obviously!) of hex digits (hence twice the size) preceeded by the string '0x' (hence the +2).

I don't know what MySQL syntax uses to denote blobs but you should look in this direction. For instance SQLite would use X'binary_data_as_string_of_hex_digits_without_the_0x_prefix'

like this:

insert into mytable (myblob) values (x'A3C709E4F65B43172A4') where ...;

Edit: fixed SQLite blob syntax.

@alankam58,

What exactly are you saying?

Edited January 11, 2013 by jchd

[Clark]

That's all a bit tricky really.

When I retrieve from the blob, even text files are double the size, which concurs with jchd's assessment. I can only assume alankam58 is using different UDF's, as the one's I am using are not mysql specific, viz:

#include <_sql.au3>

#include <_MySQLConnect.au3>

I will try changing to MySQL.au3 and see if that makes a difference, but at the very least I am unclear as to alankam58's statements regarding fileopen not working with Excel documents, and use of the "attach method".

jchd, given your comments above, I though that adding the below statement would convert the stored (and then retrieved) hex string to a binary, but this doesn't seem to be the case:

local $bn = Binary($aResult[4])

($aResult[4]) being the blob field.)

Seeing this doesn't (seem to) work, is there a function that can be written to convert the read in blob field back to it's original binary form before I write it out to the file system?

Thanks in advance for any help

Clark

[Clark]

Well, there you go.

I changed UDFs and everything is fine now.

Thanks all for the help.

Clark

[alankam58]

hi Clark,

i found another way that can store any file system (docx, xlsx..)

i use MySQL.au3 UDFs btw.

use this:-

$name = "D:Improvement ProjectsAOI DatabaseMySQL Datatype.docx"

$sSQL = "INSERT INTO `test`.`pic` (`caption`, `img`) " _

& "VALUES ('MySQL Datatype.docx', LOAD_FILE('"&$name&"'))"

$SQLInstance.execute($sSQL)

$SQLCode = "SELECT * FROM "& $testTableDB

$TableContents = _Query ($SQLInstance, $SQLCode)

With $TableContents

While Not .EOF

If StringCompare(.Fields ("idpic").value,"16") = 0 Then

$bin = (.Fields ("img").value)

ExitLoop

EndIf

.MoveNext

WEnd

EndWith

$testwrite = fileopen (@ScriptDir & "MySQL Datatype.docx" , 2)

Filewrite ($testwrite , $bin)

[Clark]

Thanks AlanKam58

Looks like more than one way to skin a cat!

Thanks for the update.

Clark

[bullmoose20]

so I am still looking for the code that will read back the stored blob data to present to the user. I have the insert command working, but I dont know how to read the blob data, determine the filetype(.jpg, gif,png) and present the image back in an autoit gui. I am looking for pointers please. This post only seems to show the insert.

[mLipok]

When you store blob you should also store information about file extension in a separate column.