Re: [Mysql-cocoa-users] Re: SMySQL framework

[John G. <jo...@jl...>]

>
Hi Lorenz,

I've been using the Framwork for a while, and I have used BLOBs a=20
little bit so I can offer you some overviews that may help you.

Over the last year I have used BLOBS in two ways, first I have embedded=20=

word documents in a BLOB, and retrieved them using a cgi application I=20=

wrote in Cocoa, everything worked properly. Secondly I have used a BLOB=20=

to contain a LONGTEXT as in the older version  of the Framework I am=20
using it couldn't support large text columns.

comments below:


>> For the last question I'm not sure to get it right. Let me try to=20
>> rephrase it (and tell if I'm wrong):
>> When you retrieve a BLOB (or TEXT) from the DB it comes as a NSData=20=

>> object. You want this NSData object to be (eventualy) edited as text=20=

>> by the user. The problem you are facing is that if you transform this=20=

>> NSData to a NSString and convert it back to a NSData later the two=20
>> NSData are different (so if your NSData correspond to a binary=20
>> format, the data gets corrupted).
> It is exactly like this. The string I get from the NSData is for=20
> example like this:
> GIF89a=03=01 plus a lot of strange characters
> After writing it to the db, there is only the GIF89a=03=01 without the=20=

> rest of the string (although I escaped the string using the method=20
> prepareString:). I think the method I use to extract the string=20
> ([[NSString alloc] initWithData:theValue encoding:[mySQLConnection=20
> encoding]]) is wrong.
> Maybe the problem is that the string contains characters which=20
> "terminate" the string, for example if I insert the string in a text=20=

> file, I don't see anymore the cursor or the end of the file. The same=20=

> thing happens with the dump files my app writes; there I use the=20
> method prepareBinaryData: with the original NSData objects. Reading=20
> the dump afterwards works fine, but when I open the file using=20
> TextEdit, it displays only the beginning of the file.
>

Basically a BLOB gives you an NSData object (Binary data basically) if=20=

at any point you convert this to some form of string, unless it is=20
escaped somehow you will lose some part of the binary data and=20
therefore can't get back the original.

When I was putting Binary data (word docs) INTO the database I had to=20
use prepareBinaryData to escape the data in my NSData object so I could=20=

include it in the SQL INSERT statement. However when I retrieved the=20
data from the database it came out as an NSData object and needed no=20
further modification.

When I was storing Long Text data in the BLOB, I did not need to modify=20=

my source data when inserting it into the DB (I do actually=20
prepareStringData since it is user supplied and if they include certain=20=

characters it corrupts the SQL), however when I retrieve the data from=20=

the database I need to convert my NSData object back to an NSString=20
object using a suitable dataEncoding.


>>
>> If this is the problem you are facing... I'll have a look to see what=20=

>> can we do about it. The main problem I'm facing with TEXT and BLOB=20
>> column in a table is that one can not discriminate between the two=20
>> (or at least I don't know how to do it) without issuing a 'DESCRIBE'=20=

>> command on the table (they give the same type in the MYSQL_RES field=20=

>> types).
>> Indeed after seeing the framework again I think the normal solution=20=

>> would be not to use prepareBinaryData: but prepareString: instead. ie=20=

>> once your NSData has been transformed into a string (By the way you=20=

>> can use the method stringWithText: for that), you don't want to use=20=

>> prepareBinaryData on it anymore.
> I'm using the method prepareString:, but it doesn't work. What is the=20=

> method stringWithText:? I haven't found it in the documentation.
>
> Anyway, this isn't very important because it works well with "normal"=20=

> (non binary) blob fields and some binary blob fields too; and it=20
> doesn't make much sense to edit a binary data as a string. Dumping the=20=

> table or editing a row containing this blob fields works well too.
>

my understanding is prepareBinaryData does the SQL text encoding to=20
represent binary data in an SQL string (it applies all sorts of escape=20=

sequences)

prepareStringData escapes the information in the string so that it is=20
safe to include in an SQL statement, for example it escapes characters=20=

like ' " / etc.

going back to your problem, if you are writing a program that needs to=20=

make sense of what is returned by SQL statements, you need some "local=20=

knowledge" of what to expect. If you are building an application like I=20=

am, I control what is in the DB, and therefore what i get out. If on=20
the other hand you are building a generic appilcation, like I think you=20=

are with Cocoa-mysql, then you should use DESCRIBE to extract you local=20=

knowledge (and cache it of course)

anyway, hope some of my experience helps out

JOhn