Clark

Blob size issue

12 posts in this topic

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

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

@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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

That's all a bit tricky really. :ermm:

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

Share this post


Link to post
Share on other sites

Well, there you go.

I changed UDFs and everything is fine now.

Thanks all for the help.

Clark

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Thanks AlanKam58

Looks like more than one way to skin a cat! :)

Thanks for the update.

Clark

Share this post


Link to post
Share on other sites

#11 ·  Posted

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.

Share this post


Link to post
Share on other sites

#12 ·  Posted

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


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

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