[Mysql-cocoa-users] Re: SMySQL framework

[Lorenz T. <lo...@te...>]

Hi John,

Thank you very much for helping me with my problem. It makes developing=20=

of open-source applications possible!

comments below:

On Monday, March 10, 2003, at 10:55 PM, John Guy wrote:

>>
> 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=20
> embedded word documents in a BLOB, and retrieved them using a cgi=20
> application I wrote in Cocoa, everything worked properly. Secondly I=20=

> have used a BLOB to contain a LONGTEXT as in the older version  of the=20=

> Framework I am 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=20
>>> this NSData to a NSString and convert it back to a NSData later the=20=

>>> two 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.
In the meantime I understand that it doesn't make much sense to=20
represent binary data as a string (as long as it is not escaped for=20
using in MySQL queries).

> 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=20
> could include it in the SQL INSERT statement. However when I retrieved=20=

> the data from the database it came out as an NSData object and needed=20=

> no further modification.
Writing the data returned by the framework as a NSData object back to=20
the database works fine when using the method prepareBinaryData:.

> When I was storing Long Text data in the BLOB, I did not need to=20
> modify my source data when inserting it into the DB (I do actually=20
> prepareStringData since it is user supplied and if they include=20
> certain characters it corrupts the SQL), however when I retrieve the=20=

> data from the database I need to convert my NSData object back to an=20=

> NSString object using a suitable dataEncoding.
I do it exactly like this. It works fine when inserting "normal"=20
strings and I can display every type of binary data too (using the=20
NSString method initWithData:encoding:). Only certain binary data get=20
corrupted when trying to write it back to the database. But as written=20=

above it works always fine when using the method prepareBinaryData: to=20=

get the string out of the NSData object.

>
>>>
>>> If this is the problem you are facing... I'll have a look to see=20
>>> what can we do about it. The main problem I'm facing with TEXT and=20=

>>> BLOB column in a table is that one can not discriminate between the=20=

>>> two (or at least I don't know how to do it) without issuing a=20
>>> 'DESCRIBE' command on the table (they give the same type in the=20
>>> MYSQL_RES field types).
>>> Indeed after seeing the framework again I think the normal solution=20=

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

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

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

>>> use 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=20
>> the 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=20=

> I 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=20
> you are with Cocoa-mysql, then you should use DESCRIBE to extract you=20=

> local knowledge (and cache it of course)
It is a generic application and I don't really know what type of data=20
is stored in the blob field (binary data such as images or only text).=20=

So I will make it like you have written: Check if the field is of type=20=

blob (using a DESCRIBE command), and if it is a blob using the method=20
prepareBinaryData: to get an escaped string which I can edit and write=20=

back to the database.

Thanks again for your help. I know now how to get this thing working in=20=

the next version.

Best Regards,
Lorenz

--=20
lorenz textor
** sh **
eigerstrasse 21
8200 schaffhausen
tf 052 - 624 27 91
** ticino **
via delle scuole 41
6963 pregassona
tf 091 - 940 20 57
**
mobile 076 - 531 71 74
email lo...@te...