Full Unicode/Ansi-Support in /testing branch

[Sorien]

it depends on TimeSeparator and DateSeparator settings

Example for broken time token detection:

1. first check what is your TimeSeparator ZGenericSqlToken.pas TZGenericSQLQuoteState.NextToken line 187

Code: Select all

if ReadChar = {$IFDEF WITH_FORMATSETTINGS}FormatSettings.{$ENDIF}TimeSeparator then

mine is ':'

2.

Code: Select all

CREATE TABLE NEW_TABLE (
  ID INTEGER,
  NEW_TABLE_FLD VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8);

and run query

Code: Select all

str:= 'INSERT INTO NEW_TABLE VALUES(1, '+QuotedStr('žľžťtest:aa: bb')+')';
ZConnection1.ExecuteDirect(str);

if you have another separator use it in query

[EgonHugeist]

Thank you that's something i can work with...

[s]I'll change this post if the Bug is gone.[/s]
Patch done Rev.1173

Can you confirm this?

Thanks, Michael

[Sorien]

I'll able to test it during a Monday

i was thinking a bit about it and my and your solution seems to be wrong because we are trying to match it with system format not with proper database format which is wrong

at second when i create query like INSERT INTO FOO VALUES('1.1.2012') tokenizer should return '1.1.2012' as ttquoted or ttstring or something like this, because system don't know if I'm trying to write it to Date field or varchar/char db field from my point of view there is no need to match it as ttdate its still just quoted string

but when i create query like INSERT INTO FOO VALUES(1.1.2012) [without quotes] and have preprepare query set to true i'd expect from library to match it to ttunknown or ttdate(if it matches system format) and during execution it should be able to normalize it to proper db definition -> INSERT INTO FOO VALUES('1/1/2012') - maybe a low priority feature request

[EgonHugeist]

I must admit you're right again. MDeams and me fixed the ttDate.. states withe checking for system format used chars.

And i think it's not a minior bug then before. But much more difficult to get the right seperators here. Because they are different between some providers..

Let's see what we can do..

[mdaems]

Sorien,
When changing ZGenericSqlToken.pas you mainly change the behaviour of the filter feature of the datasets. And those should respond to system settings.
The database specific Tokenizers decide themselves if they use the generic (system dependent) behaviour or not.
So maybe we better provide some protected Tokenizer properties that contain the date/time separator chacters, so we can set them depending on the used environment?
You feel like preparing and testing such a patch?

Mark

[Sorien]

Sorien,
When changing ZGenericSqlToken.pas you mainly change the behaviour of the filter feature of the datasets. And those should respond to system settings.
The database specific Tokenizers decide themselves if they use the generic (system dependent) behaviour or not.
So maybe we better provide some protected Tokenizer properties that contain the date/time separator chacters, so we can set them depending on the used environment?
You feel like preparing and testing such a patch?

Mark

ok, i understand, I'll try to look at it and test in my free time

@EgonHugeist
i have new bug if you set field to UTF8 with size exceeding ~4500 chars (VARCHAR 4500) you are not able execute query

ZdbcChache - constructor TZRowAccessor.Create(ColumnsInfo: TObjectList;

Code: Select all

    if FColumnsSize > 32767 then
    begin
      raise EZSQLException.Create(SRowBufferWidthExceeded);
    end;

[s]few lines upper GetColumnSize(Current); returns 36000[bytes] (18000 * 2 + 2)
as i understand 18000 is 4 * 1500 (max unicode char size * field defined size) ... but why *2?[/s]

[EgonHugeist]

Sorien,

Mark is right here. We had a long discussion concerning this date/time/DateTime-Token detection. This detection realy is only used for the ZExpressions, where the detected values have been true date/+time values. For me and Mark is this case is closed. The patch was 100% right done. So thank you for reporting...

Now i saw your strikeout. I think the reason of * 2(SizeOf(WideChar)) is clear....

Next point is that you report me RowBufferWidthExceed exception is a bug.
Hmm is this a bug?

Code: Select all

    Inc(FColumnsSize, FColumnLengths[I] + 1);
    // 32768 is the length of a TByteArray. (HeidiSQL patch)
    if FColumnsSize > 32767 then
    begin
      raise EZSQLException.Create(SRowBufferWidthExceeded);
    end;

The reason why this exception was raised is also selfexplenatory by reading the comment above, or not? Zeos reserves max 32kb/row memory * RecordCount * 2(if Cached like TZTable/TZQuery). Don't you think such memory-waster field definitions are usefull? Do you really need such big/count of varchar-fields? I know MySQL for example, allows veeeerrryyy long varchar fields. But here we normaly raise an exception for "use blob fields instead".
Which points me more to the possibility that you're using a table with many varchar-fields. Adding the sizes = SizeOf(RowBuffer). Now you can see that the TByteArray has the max length of 32768 bytes. If the incomming columns of your selected rows becomes bigger than this TByteArray the exception was raised. For me personally this is the right way.

What we've to think about changing this could be realy a loads of work. Proposals?

What if you easiely use two Query's with a master-detail relation and just select parts of your table? This way works fine and you're out of limmits.

The question for ME is: Do we have a Bug or is this a feature request for bigger Rows? And is it realy usefull to change this behavior of the TZRowAccessor?

Michael

[mrLion]

EgonHugeist,

Do you really need such big/count of varchar-fields?

hi-hi
you can not even imagine how sometimes subverted. For all that the use of the cursor with long fields does not mean that these fields are stored in one table. Just the customer believes that it is necessary that way! And have to create a view ...
Also, pay attention pascal-programmers that have used the system in DOS memory model LARGE.
and, incidentally, as I mentioned earlier, in C + + is a data type that uses a segment of 65535 bytes. So why use the "stub" in the 32K?
in Pascal, you can also create a simple array or a wrapper class for a size of 65 kilobytes, or just for each field to dynamically allocate the necessary memory, which, between you and me will be much more correct!
Incidentally, when I'm using PostgreSQL, I just comment those lines, without any consequences for the stability of the program.

5 min later.....

Oh, my God! Just looked at the FAQ XE2, finally ... FINALLY the developers have corrected the definition!

Code: Select all

Delphi 

type TByteArray = array [0..65535] of Byte;

C++ 

typedef System::StaticArray<System::Byte, 65536> TByteArray;

In C + + is a good function sizeof (type), probably in Pascal is anything like that. It may cost you to replace the constant?

oops... sory. this definition in module Vcl.Imaging.pngimage.

however, to define the type and use it - a piece of cake. but it is better to dynamic memory allocation. Indeed, the structure of the cursor line is always known in advance.

[EgonHugeist]

mrLion,

it's an open discussion. Not everybody has an XE compiler which supports now 65kb/TByteDynArray. So replacing the constant isn't the finest solution. Asking for SizeOf(TByteArray); is also pascal valid but static compiler dependend. No real good way to manage the issue which we where pointed here.

I'm thinking about it. We can introduce the TByteDynArray instead of TByteArray and allocate the memory dynamicaly. Also i see possibilities to have a TZQuery.RowBufferSize: Cardinal/Integer = 0(default 32KB) property. This one i don't like for non Zeos trained users.

These are new thoughts. I know there are other threads which report the same issue and has no direct relations to my branch because this is all times Zeos-Code.
However changing this must be tested carefully, because i don't know how the RowAccessor behave afterwards. What do you think? Are you able to test this?

I see no big Problems to introduce this "new" dynamc behavior. Just replace the byteArray with a TByteDynArray, use SetLength(Length(ThisByteArray)+ColumnSize..) on cretation. I'm sure you know what i mean..

MY question was more directed to the point of BUG. Do you think that this is a bug too?

Michael

[Sorien]

from my point of view its a bug couse its i bit weird that you can have such a big field in db but you can't retrieve data in zeos, dynamic allocated buffers would be fine solution

[mrLion]

EgonHugeist, this is not the XE! You can just define the array size is not in the 32 kb and 64 kb.
I do not know English so well, I'm sorry, so do not quite understand what you're offering me, probably, to participate in the testing of its branches?
if so, to report that I tried your version found a bug, which lies in the fact that the transfer of an SQL query with inserts from the national (Russian) alphabet immediately received an error. Sorry, just cause I can not, because the Internet at home and the project is at work computer. may be later. In short, the error associated with the coding pass-through queries on the server side.
I using PostgreSQL 9.3. Parameters - code_page = UTF8. on the standard version of the library works as ... (hell, auto translator is unable to cope with the Russian idiom) ... excellent in general.
because the project is in, then test the new bugs me no time.

[EgonHugeist]

Sorien,

the BIG team will discuss what we're doing. I agree and think a dynamic handling should be the best. We can safe memory, and we can grow up the memory like we need it.

So let's see what we can do the next days.

mrLion,

don't worry about your english. Mine isn't very good too.
Now to your issue:
Did you use !!! TZConnection-PreprepareSQL = True !!! on sending this Statement with russion characters? If not then the query is sendet like you wrote it as native 1Byte AnisString. If this mode is True i transform your Query to an UTF8 String and send this to the Server. Which way decides my branch on the choosen ClientCharacterSet encoding.

This is a problem of UnicodeStrings. There is no 100% safe way to detect still encoded data in them. With the old AnsiDelphi's we havent this problem. And PosgreSQL is very sensible for strings encodings.

Btw. If you use the PreprepareSQL-Mode then be sure your whole project is cleaned from SQL := UTF8Encode('.....'); Or switch this mode on or off at runtime.

Can you confirm this?

Michael

[mrLion]

Did you use !!! TZConnection-PreprepareSQL = True !!!

no. hmmm... i`m try it. later.

Btw. If you use the PreprepareSQL-Mode then be sure your whole project is cleaned from SQL := UTF8Encode('.....'); Or switch this mode on or off at runtime.

I do not understand.

1 - I have to fill in all the SQL queries using this design?
2 - or use PrepareSQL = true?

answer briefly 1 or 2?

Ivan Rog

[EgonHugeist]

Ivan,

Your config: PostgreSQL, ClientCodePage=UTF8+CBuilderXE.., right?

you try to use a query like: ZQuery1.SQL.Text = 'insert into table1 values 'да не стесьняйся, присоединяйся!';' right?

if PreprepareSQL = False; then you have to use ZQuery1.SQL.Text = UTF8Encode('insert into table1 values 'да не стесьняйся, присоединяйся!';');

but if this mode is true then your query is valid, because Zeos-EgonHugeist is now able to do this automaticaly for you. It safes you development time.

Next premise: PreprepareSQL = True;
if you use now ZQuery1.SQL.Text = UTF8Encode('insert into table1 values 'да не стесьняйся, присоединяйся!';'); then this statement will be double encoded and wrong witten in your database but is valid too!

Do you understand me, Ivan? Just be carefull with existing project parts if this mode = True.

Michael

[mrLion]

Michael
short, intelligible and understandable explanation. Thanks, I'll try.
Ivan