Re: BLOBs, MySQL, hex. Oh my

FROM : Ben Einstein
DATE : Thu May 08 18:11:46 2008

Hi Serge,

It's really quite strange. MySQL does return an NSData object, but 
there's a byte level problem. Again, I really don't know much about 
this stuff but NSImage instances fail to be unarchived and zip files 
can't be unpacked. From what I understand, MySQL is giving us back 
hex. So I used your prepareBinaryData: method, but any time I got a 
query back with that data, it was no good unless I ran Hayden's hex 
conversion loop. My columns I'm writing to are of type LONGBLOB.

In regards to the Shark testing: I knew what loop was getting caught 
up, I never, ever knew that Shark could get so specific as I've now 
discovered. I shouldn't have dismissed that suggestion so soon. Most 
of my experience with optimization is with Instuments (and their pre-
leopard counterparts), which can't get that specific (again, as far as 
I know).

Ben

On May 8, 2008, at 9:01 AM, Serge Cohen wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello;
>
> One thing I don't understand, is that in MCPKit, you're supposed to 
> "prepare" (that is hexcode) the BLOB when you perform the INSERT, 
> but when using a query and fetch a row, the column containing a BLOB 
> should already be returned as NSData... This NSData should represent 
> the exact binary replicate of the INSERTED NSData (before it was hex-
> coded) ...
>
> In other words, to my knowledge one would need to use the -
> [MCPConnection prepareBinaryData:] method at the time of the insert, 
> but there should be NO need to any back conversion when getting the 
> result of a QUERY.
>
> Is there something I'm missing here ? (unless you are indeed storing 
> your BLOB as a TEXT, in which case MySQL would store the hex-coded 
> text rather than the binary represented by it, and in which case it 
> would return the TEXT which you would have to UN-hexcode 
> yourself ... if this is the case, then the problem lies in the table 
> definition, you "just" have to make sure to declare the column as 
> BLOB rather than TEXT, right ?)
>
> Serge.
>
>
> Le 8 mai 08 à 02:16, Ben Einstein a écrit :
>

>> Hi All,
>>
>> I have an enterprise DB application that once used DO to move some 
>> files around (images and zip files, mostly). After some serious 
>> testing and lots of reading, I decided to move this to a few 
>> different BLOB fields in the database. Despite major warnings from 
>> people, I've found that images work great (specifically, NSData/
>> NSImage). I can notice a very minor speed drop a few milliseconds, 
>> but being able to drop DO and it's woes is completely worth it.
>>
>> Unfortunately, I didn't quite test the zip files, figuring it would 
>> be the same. I'm using Serge Cohen's MCPKit (aka SMySQL), which has 
>> a nifty little function to convert NSData to a MySQL-legal 
>> NSString. On the other end, someone on the Apple list posted a few 
>> lines of code to bump returned data into it's original NSData 
>> object. With the zip files (no larger then 600kb) that method takes 
>> 30 - 40 seconds to run. On an image of a similar size, it takes 0.1 
>> seconds on a slow day.
>>
>> Does anyone know why this would be? Is there an easy way to get 
>> around this? I suppose my understating of hex is lacking, but I 
>> always thought a hex string was a hex string was a hex string; 
>> length was all that mattered.
>>
>> Thanks,
>> Ben Einstein

> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.8 (Darwin)
>
> iEYEARECAAYFAkgi+Y0ACgkQlz6UVQtc2uw04wCfR77KlokLWAk533hza8gF3WqQ
> X4EAoOMlMyytSQdFArPkOZ+uITE9OtI5
> =aw++
> -----END PGP SIGNATURE-----