Some one can confirm this problem ?

When the FieldDefs.Size properties is defined and we are trying to save UTF8 characters with accented words, the Data is Truncated.

This affect all Database components, including third party components like ZeosDB

http://bugs.freepascal.org/view.php?id=24745

I?m using Lazarus from snapshot:
Lazarus-1.1-42056-fpc-2.7.1-20130711-win32.exe

?

[]s Daniel

Conhe?a o Projeto ACBr - Automa?ao Comercial Brasil DJSystem a Loja Patrocinadora do ACBr
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130714/f821aea8/attachment.html>

Daniel Simoes de Ameida wrote

When the FieldDefs.Size properties is defined and we are trying to save UTF8 characters with accented words, the Data is Truncated.

Yeah, I have experienced that too. There really should be a .Size and .DataSize property - or something similar.

Even setting the database to use Unicode text fields doesn't solve the problem either - in then messes with the .Size property value in SqlDB.

My work-around is to simply use VarChar(), and make sure you add some extra space for those Unicode fields. Then hold thumbs that the client apps don't hit that limit. eg: I might have a business object that says a field is 100 "characters" long. This limit gets applied to the user interface too. But the database field is actually defined as VarChar(150). This gives me 50 bytes extra for the occasional Unicode character. Definitely not an ideal solution though.

Regards,
- Graeme -

Daniel Simoes de Ameida wrote

When the FieldDefs.Size properties is defined and we are trying to save UTF8 characters with accented words, the Data is Truncated.

Could be because UTF8 with accented characters may result in multiple bytes per character.
If the size is too small, the number of bytes passed to the db won't fit.

The difference between specifying size in bytes and in characters still needs to be addressed.
AFAIR, there's already a bug open for this.

Workaround: make your field size as large as the maximum number of UTF8 bytes you expect.

Another workaround: use the appropriate codepage for storing strings in the database, so that all characters are single bytes. With the new (encoded) AnsiStrings this should be quite easy (automatic conversion).

DoDi

Wouldn't you run into trouble if you want to use a character outside the codepage? Presumably OP has enabled UTF8 on the db instead of some other codepage on purpose.

Hans-Peter Diettrich wrote

Another workaround: use the appropriate codepage for storing strings in the database, so that all characters are single bytes.

You should know by now that not all characters can be represented in a single byte. Also Unicode was developed to overcome the many code-page issues and standardise text storage. So I think it would be silly using anything other than one of the Unicode encodings (I would opt for UTF-8 or UTF-16) in this day and age.

The missing link is fixing SqlDB or Zeos etc, as it seems that the database servers themselves already support UTF-8 and UTF-16 text fields. [at least Firebird does]

My solution (work-around) for now is to not specify a charset for Firebird and always store text as UTF-8. My fields are defined as follows [in bytes size]: <desired size in characters> * 1.5 By business objects are coded to notify the user interface to only allow <desired size in characters> text input. 6 years on, and I haven't had a single client complain that text was truncated [maybe a little bit of luck has something to do with it too]. I guess I must also add that our products are mainly geared towards English, Afrikaans and Portuguese. So large quantities of multi-byte characters are at a minimum.

Regards,
- Graeme -

Then the choice of byte sized characters in the DB field is wonder how the DB or SQL would sort or compare
(LIKE) such strings?

When the DB doesn't support better string types, like true UTF-8 or UTF-16 strings, your suggestion is the next best, i.e. abuse fixed size STRINGs for storing UTF-8 encoded values, and stay away from any string handling in the DB. This means that e.g. a character limit must be handled in application code, with either a sufficiently large fixed or a variable field size in the DB.

DoDi

I know that, the question is whether the user and DB understand that, too.

Also Unicode was developed to overcome the many code-page issues and standardise text storage. So I think it would be silly using anything other than one of the Unicode encodings (I would opt for UTF-8 or UTF-16) in this day and age.

Depends on the requested DB/SQL operations. Sizing, searching and sorting of strings is fastest with SBCS of a specific encoding, Unicode requires much more code and computation power. Even then I wonder how strings of different languages will be sorted together, most probably a "raw" sort (by codepoints) is the only solution.

You see that Unicode introduces new problems. Even in UTF-32 the element count does not always equal the character count. Your calculation obviously is based on latin characters, while Unicode supports many more character sets or codepages. In your case I'd prefer an SBCS supporting all your languages, what's certainly feasable, even if it may not be an registered ISO/ANSI codepage.

DoDi

No it isn't. Why shouldn't a user be able to enter Chinese, Greek, Cyrillic and Latin characters?
As for sorting etc, there are various unicode collation standards.

Then such characters either must be understood properly by the DB, or the DB must not care about such data at all.

As for sorting etc, there are various unicode collation standards.

A DB implementing these standards has to offer Unicode fields in the first place.

DoDi

Reinier Olislagers wrote

No it isn't. Why shouldn't a user be able to enter Chinese, Greek, Cyrillic and Latin characters?

should one be allowed to place

[chinese glyph][greekglyph][cyrillic glyph][latin glyph]

all in the same "string"?

what codepage will that string then contain?
how would one display all those characters in a certain codepage? is it proper to display blank blocks in a "text only"* environment which does not contain those characters? which block characters?

* choose your codepage other than any UTF* pages

selects with where clauses or order by clauses

> As for sorting etc, there are various unicode collation standards. A DB implementing these standards has to offer Unicode fields in the first place.

That's not the problem. Can you show me one that is supported by sqldb that *doesn't*???

The point wasn't that dbs didn't support unicode; the point was that the FPC dataset concept was focusing on byte lengths instead of character lengths.

Hans-Peter Diettrich wrote

> As for sorting etc, there are various unicode collation standards. A DB implementing these standards has to offer Unicode fields in the first place.

Firebird does, and so does many other database servers.

http://www.destructor.de/firebird/charsets.htm

Graeme.

Of course, and that is exactly why the Unicode standard was developed.

But getting back to the point. SqlDB seems to always define strings in byte length, yet the DB-aware components use "character lengths". This is what is causing the trouble.

Other database components, as far as I remember, have .Size and .DataSize to differentiate between byte size and character length information.

Graeme.

Hans-Peter Diettrich wrote

I know that, the question is whether the user and DB understand that, too.

If you tell the database server (eg: Firebird) to use Unicode (UTF-8 for example), it will understand that. This will then affect storage size, up/lower case conversion, string comparisons, sorting etc.

http://www.destructor.de/firebird/charsets.htm

Depends on the requested DB/SQL operations. Sizing, searching and sorting of strings is fastest with SBCS of a specific encoding, Unicode requires much more code and computation power.

Obviously in requires more "power", because Unicode can handle ALL spoken and written languages. The other code-pages are limited to a specific set or characters.

strings of different languages will be sorted together, most probably a "raw" sort (by codepoints) is the only solution.

There are different rules defined in Unicode to handle that, but "raw" codepoint comparison is a last resort.

You see that Unicode introduces new problems. Even in UTF-32 the element count does not always equal the character count.

Correct, but sorting and comparison rules still exist ? as defined by the Unicode standard. Also it depends on how the text is normalised and stored. This is all explain in the Unicode documentation.

Regards,
G.

Graeme Geldenhuys wrote

But getting back to the point. SqlDB seems to always define strings in byte length, yet the DB-aware components use "character lengths". This is what is causing the trouble.

Exactly.

Other database components, as far as I remember, have .Size and .DataSize to differentiate between byte size and character length information.

AFAIR, sqldb has .Size and .DataSize as well, the difference has just not been implemented.
But my memory is not so reliable... ;)