Zeoslib Portal

Hey guys,

I did some internal changes [s]in my private testing-egonhugeist branch[/s]...

I want to inform you about my little changes and the new behavoir of Zeos7.

First: where to get the stuff?
Use TortoiseSVN with URL:
http://svn.code.sf.net/p/zeoslib/code-0 ... es/testing

Use the "how to" from MDaems on http://zeos.firmos.at/viewtopic.php?t=841

Russian description: http://zeos.firmos.at/viewtopic.php?p=15003#15003

second: sorry for my english.


1. TZConnection: I've implemented a property for the ClientCodePage (Connection-CharacterSet) which automaticaly rearanges the ZConnection.Properties and did not use Metadata-Informations. This sets also the Client-Side CharacterSet to the Server and informs Zeos7 about the encoding the server expects or sends.

2. TZConnection.PreprepareSQL: Boolean:
This is a Query-disassamble/reassamble-Step for you. Let me explain.
The purpose of this property:
If you connect with Delphi and choose a Unicode-CharacterSet (TZConnection.ClientCodePage: String) then you must encode all your string-values manually in your code.
An example:
For Delphi7-2007/FPC you have to use:
ZQuery1.SQL.Text := 'insert into table1 (Field1,..) values ('+AnsiToUTF8('öäüßÄÜÖ')+',...);
For Delphi2009-XE2 you have to use:
ZQuery1.SQL.Text := 'insert into table1 (Field1,..) values ('+UTF8Encode('öäüßÄÜÖ')+',...);

If TZConnection.PreprepareSQL = True then you do not need this any longer. ZQuery1.SQL.Text := 'insert into table1 (Field1,..) values ('öäüßÄÜÖ',...); works perfectly.

For Delphi7-2007 i'm able to detect if your string values are still encoded.
For Delphi2009-XE2 i've to pass. I don't know a 100% save way to detect the 1Byte encoded Strings in a UnicodeString. Here i've to say: be carefull with exsiting sources!
My actual state has ONE mess: If you use "CharecterSet/Collation"-special prefixes in your Statement's... Then trouble should be the result! This is the second reason why i left this PreprepareSQL-step optional. Now you can portate FPC-Projects to Ansi-Delphi-Compilers to Unicode-Delphi-Compilers. I see no real problems here....

[s]Also I've implemented a property called ClientCodePageOptions. [/s]

3. For new exported ZConnection functions:
function GetBinaryEscapeStringFromString(const BinaryString: AnsiString): String; overload;
function GetBinaryEscapeStringFromStream(const Stream: TStream): String; overload;
function GetBinaryEscapeStringFromFile(const FileName: String): String; overload;
function GetAnsiEscapeString(const Ansi: AnsiString): String;

Details: These function are a sign for my Query-preprepare-step to ignore further translations. Also you can read in binary-files to add them to your SQL-Statements. Your can sign your own strings, Streams or i prepare your File-Data as detectable string.

4. changed GetString-behavoir of the internal IZResultSets to:
function GetString/GetStringByName(const ColumnName: string): String; The DBC-Layer and Component-Layer have the same String-Types now. Whatever "String" is type from.
This i've changed because of some registered bugreports of peoples who uses UTF8-Connections and want to compare with SourceCode-constants or ansi-files for example. Now i do prepare the Plaindriver-given data in a ansi compareable format. Which should make somthing easyier for you. If you want to compare something in UTF8 you can use the same function and set the parameter CharEncoding to ceUTF8.. I'm hopfully this will save your developing time too. (definition in ZCompatibility.pas)

5. Property TZConnection.UTF8StringAsWideField: Boolean;
Only interesting for D7-D007/FPC without Lazarus and has dependencies to the choosen characterset.
TZConnection.UTF8StringAsWideField = False:
Now Zeos does't use TWideString-Fields for Unicode-Data. If you've UTF8 Controls like http://sourceforge.net/projects/utf8vcl/ or TNT then you can enjoy real unicode.
TZConnection.UTF8StringAsWideField = True:
Zeos assumes now TWideFields for Unicode-String values. Problem: the Ansi Delphi comilers boiling down all Widstring chars to Ansi-OS compatible characters because the delphi standart controls using only AnsiStrings. Which means for D7-2007 automatic data loss or some characters where displayed as'?'. This is no result of Zeos...

6. New Zeos.inc defines:
{$DEFINE WITH_CHAR_CONTROL} this enables the Char-Control-System. Actually disabled.

7. Also i created a new generic Object called "TAbstractCodePagedInterfacedObject":
It stores the actual choosen Connection-parameters. So you can open different connections with different ClientCodePages. This Object also exports 3 litte string-helper functions which are full of compiler-directives. Here i've unified all string handlings Zeos internal needs. Which meens there is no more need for compiler directives in the Zeos sources then in these functions concerning string-translations. To stabelize the behavior for these functions i also need the TZCharEncoding types. You can find them in the unit ZCompatibility.pas.

function ZDbcString(const Ansi: AnsiString; const Encoding: TZCharEncoding = ceDefault): String;
function ZPlainString(const AStr: String; const Encoding: TZCharEncoding = ceDefault): AnsiString;
function ZStringW(const ws: WideString; const Encoding: TZCharEncoding = ceDefault): String;
Remember a Full-Unicode-IDE handles a String as UnicodeString(a type like WideString).
These functions are now the central point internal to avoid data-loss. They are able to do the critical Ansi-to-(Unicode)String translations and revert dependend on the choosen Client-Side CharacterSet. Like this:

[align=center][/align]

9. Changed bytea-behavior of PG9+:
Since Postgre9x the encode/decode-bytea behavior has changed. I've include these step in my patch here. (Now also avialbe in the testing-branch.)

10. Virtual Characterset "ZUTF8AsAnsi for SQLite":
Available for D7-D2007 and FPC without Lazarus. For all other IDE's we don't need this.
Here i start from the premise that you SQLite database is only used for a non real Unicode database. This characterset makes it possible to use SQLite with your Standart-AnsiControls. You don't need TNT-Componets any longer. Easiely switch TZConnection.PreprepareSQL := True and you can use SQLite as Ansi-Database.

These steps and changed behavior is hopefully ready and proofed for SQLite, FireBird, PostgreSQL, MySQL,ASA12, MSSQL and Oracle.


To our Delphi users. All unicode-fieldtypes whery choosen on the selected Codepage. Which means choose Latin1 or a other Ansi-Codepage the string/stream-field-types are from type TString-/TMemoField. If you choose a Unicode-Codepage the string/stream field types are from TWideString-/WideMemoField...

To our D12UP users: All Statements should work now. It doesn't matter if we've Full-Unicode-IDE or not. Also you can use other Codepages then UTF8. My modifications should make this possible without problems. Also i'd a lot of work to avoid possible dataloss.. (Actually 60 compilerwarnings left)

To all Zeos7 users: I need testers, bugreporters, developers who are interested in reliable forward developing of Zeos7 and some votes for the changes!!!!!!!

Best regards

Egonhugeist

btw: Some ideas (Firebird) of the internal change are from marsupilami, so Kudos, Jan!

And much more bigger THANK YOU! Mark to spend your rare time to be my tester and "second pair of eyes"!

I attached my little patch. It shows the changes to the last testing-branch

What a roadmap zeosdbo ?

What working , and what not ?
What features will make ?
In general, what todo list for make release zeos 7.0 ?

ism,

you are right here. Maybe i've to say more about the changed behavior of Zeos7.

First we've to separate what we have before, the actual state of the "normal" testing-branch and my private testing branch "testing-egonhugeist".

Before&Actual testing-branch:
Zeos7 alpha was released. It was the stable state of Zeos6. But Zeos promisses support for a full Unicode-IDE like Delphi12_up.
The most internal processes to get the promissed Full-Unicode-Support for Delphi12_up where improved with some hard coded compiler-directives. The most things where ready for PG, SQLLite, and MySQL. In Firebird there was a big leak. Minimum requirement was the Zeos known codepage=Utf8. Remember we've a loooooooot of threads with almost the same Problems like for example: I write 'öüâß' but in my database was ''$&&%&$$§' postet.
Zeos is still a Alpha Version! And i'm sure Mark will never change this state until we've a stable Full-Unicode-IDE behavior!


And heer are my thoughts and my "sollution".

I internaly separate between 4(not all respected at the moment) TZCharEncoding-Types:
ceAnsi, ceUTF8, ceUTF16, ceUTF32 (the last two are for futher works and not supported!). How can i find out if we have a UTF8 or UTF16 or a Ansi-Connection? Which namespace expects/delivers which kind of data? Remember each DB-System has its own namespaces for charactersets and that one can change from version to version. Example of FireBird: version 1.5 uses the UNICODE_FSS as UTF8-CharacterSet and since 2.0 the CharacterSet UTF8 is also supported. Both are UTF8 compatible. Next example MySQL: UTF84MB vs. UTF8. SQLite supports open/compile/execute-16 functions which means we've to use UTF16 (This is not supported actually). So which namespace is Ansi and which not. In Firebird/SQLite we've to know the characterset-behavior before we connect. The other Databases are more flexible there and can change it on runtime. Each SQL-Databes has differences here. So where can we get the information before we can read the MetaData's? This was my First thougths. We've to be sure what Ansi-Format we send and receive.

Next problem: Which compiler supports which CharacterSet? Can FPC use a Ansi-(Latin for example) CharacterSet safely? Clearly no! Can Delphi<=12_UP use a Ansi or Unicode-CharacterSet? Yes but it was not really ready implemented (UTF8) for all PlainDrivers. And now what about a full Unicode-Compiler? Which behavoir we have now? What about Ansi support? And how to upgrade to such a new IDE.

So what have i done here? I'll split the answers into the three compiler-classes:

1. Hey FPC-Users i've did a lot of internal work an the right "Field-Type"-casting and corrected/solved reported bugs. They are still avialbe "normal"-testing branch since my last commits (not completly sure). So here i've to say: Sorry i've not more interisting news for you than the Exported functions i wrote before. the Zeos7+FPC behavior should be stable! And it brings up all reported bugfixes and user-patches which where reported in the Alpha7 Forum. End...

2. To our Delpi7-2007 users: I've implemented the Unicode-Support for all these Plaindrivers i wrote before. (PG was ready??, MySQL to buggy there). Now you have Unicode-Support if you doubt too. Also i've implemented a "Character"-Control-System which picks out server-unsupported chars from your updated rows quickly. The Server raises no more "Unsupported-Char's"-Exceptions. And the User sees the data-loss chars as "?" right now.
So tell me if you think this could be usefull or not?! I'm not sure about that. Maybe it's to much of all goodness.

3. To our Delphi2009 -> XE2 Users:
Uff how to explain all the work in a few words? Let's start. I'm hopefully we've a stable state now. On knowing wich Encoding we actully have to use i was able to seperate which field-type we've to use to stabize the D12_UP behavior. Which means if you open a Unicode-Connection the most VARCHAR etc.-String-Type are now TWideString-Fields. Here we're sure we've no data-loss. Also Blob-Text's where right encoded. I found soooo many possible data-loss leaks and bugs... Also the internal use of our know compiler directives was allways directed to UTF8Encode/Decode everything. Hmm is that right in realy all cases? What about endless known threads about Character-Problems and wrong search-results? I thougt there MUST be a end of it. Mostly for our Full-Unicode-Ide's i've implemented CharacterSet-Code-Pages. Now we can use also a non Unicode-Characterset. It's your choise what you want. If you want to use a Big5 or Latin Characterset what about the hard coded-UTF8Encoding/Decoding in this case? P.s. please vote me if you thing Ansi-Support is usefull for a Unicode-IDE or not! It's an open discussion! Hints/critics are welcome!

--------------------------------------------
How to stop FAQ's about wrong written chars?

I've implemented a step which automatically switches to a "Compiler-Safe"-CodePage or get's the Server-default one. This step uses the namespace the plaindriver returns. In this case he returns allways a unicode-supported one. Which means i'm hopefully we can see an end of thread's with themes like "you have to add codepage=UTF8 to the TZConnection-Properties". (And where was it implemented for FireBird?)

How to unify all these three "main"-compiler-classes? What about Upgrading Delphi <> FPC <> Delphi-Unicode? What about source-changes?

So what do you think about it? What's to much or where do you see problems? Do you think it's helpfull?

Do you have ideas TO GET A STABLE Zeos7 STATE?

A detailed roadmap follows.. (Is in work actually)

Best regards,

EgonHugeist

MySQL: I got for long strings following error message:

'Column length too big for column ''Name'' (max = 255); use BLOB or TEXT instead'

Column length is multiplied by factor 6

Can you you give me some code-snippets or table definitions? Your used compiler eventually. Some more details are helpfull to verify your post. Best way to do this write me a private message...

I know that we've a String-field limit in the sources. MySQL-supports veeeeerrrryyyyy long Varchar-Fields. So this was done to avoid a "out of memory"-Exception. Zeos allocates the memory for each string. which means long strings need to much memory here (1*SizeOf(Your_field_length) + the other fields) on a readonly-dataset. For a table/query this memory-allocation is x2 because of the internal cached-resultset..

But the column "name" makes me a little bit worried here. Is it once of your definition?

Best regards, EgonHugeist

Need a short description of the modules zeos, At least in the comments. (At least the purpose and overall structure)

ism,

this is a complex request. The Zeos structure is very complex. The most puposes of the API you can find in the sources. Here you can use the Doxygen-generator to generate a doxy-documentation. All (sometimes incomplete) descriptions and purposes are written in the sources.

Do you have expierences with UML (unified modeling language). Since D2005 we can easiely generate a Object model for units, there objects and there dependencies. On SourceForge.net are several openSource projects like StarUML which you can use to generate a visual object-model.

What do you plan, ism?

best regards

I dont know UML , i work in Lazarus.
Simply to modify zeos there is no documentation.
And sometimes, to remove a bug it it is necessary.

ism,

in this case use the doxygen generation. Or much better start to debugging which helps you to learn mor about the API. Read the knowlegebase articles like http://zeos.firmos.at/kb.php?mode=article&k=8 to get you first impressions.

Ony sticky tool are the test-suites of the ZeosDBODev packages. They are a great tool to check the functionalities. Here we can add always some more tests for user reported problems to find out if issues are solved or since which compiler version we've problems.
The testsuites aren't easy to setup i know that. So collect some expiriences and it becomes more and more logical..

Your/each help is allways welcome. We need some more coders who are interested in forward developing and want to spend some time to help.

Im testing various modes components of this branch. Found this bug with MySQL base:
If ZConnection.ClientCodepage=cp1251 and ZQuery doPreferPrepared is true, then string data not displayed by the data controls (empty), but update and insert working correct.
If ZConnection.ClientCodepage=utf8 and ZQuery doPreferPrepared is true, then data is dipslayed, updated, inserted correctly.
If doPreferPrepared=false, then setting ClientCodepage cp1251, utf8 working without problems.

With ZReadOnlyQuery string data displayed correctly by the data controls only with ClientCodepage UTF-8. With cp1251 displayed only latin letters of string data.

Revision 1132
MySQL 5.5.16 default codepage UTF8
DelphiXE

Thank you for your post.

Kosvalery wrote: If ZConnection.ClientCodepage=cp1251 and ZQuery doPrePrepared is true, then string data not displayed by the data controls (empty), but update and insert working correct.

With the ReadOnylQuery, eventually? Which DataSet-Component do you use here? (Actually i'm building a demo-program for all DataSet-Components and all plaindrivers). Can you tell me more about the sequence which occours this bug?

Kosvalery wrote: If ZConnection.ClientCodepage=utf8 and ZQuery doPreferPrepared is true, then data is dipslayed, updated, inserted correctly.
If doPreferPrepared=false, then setting ClientCodepage cp1251, utf8 working without problems.

Hmm that nice, but making me a little bit curious. The PreprepareSQL-step does not touch any display-fields. The purpose is only to suppress any UTF8Encode(...String..) for your external fired statements. It is a simplification which (hopefully) supresses further theads concerning 'select * from Atable where AField = '@€€ÜÖÄ' doesn't work corectly...

Kosvalery wrote: With ZReadOnlyQuery string data displayed correctly by the data controls only with ClientCodepage UTF-8. With cp1251 displayed only latin letters of string data.

This is what i've to find out. It seems there a some compilere directives fragments in the sources which making trouble. I promise to find out whats going wrong.

best regards,

EgonHugeist

EgonHugeist wrote: With the ReadOnylQuery, eventually? Which DataSet-Component do you use here? Can you tell me more about the sequence which occours this bug?
EgonHugeist

In all cases the same sequence of components:
MySQL->TZConnection->TZQuery or TZReadOnlyQuery or TZTable->TDataSource->TDBGid or/and TDBEdit

The PreprepareSQL-step does not touch any display-fields.

TZConnection.PreprepareSQL does not affect the appearance of this bug.

The purpose is only to suppress any UTF8Encode(...String..) for your external fired statements. It is a simplification which (hopefully) supresses further theads concerning 'select * from Atable where AField = '@€€ÜÖÄ' doesn't work corectly...

IMHO it is side effect of ZeosLib. As i understand, doPreferPrepared option needed for speed up the execution of the SQL quries which is repeated often. Btw, another bug related to doPreferPreparedResolver on TZQuery and TZTable components. When doPreferPreparedResolver=true and ClientCodepage=utf8, an attempt to post the edited string date causes exception "Unsupported data type". If ClientCodepage is cp1251, i.e. native for this base, then doPreferPreparedResolver=true working.

Patch for TZReadOnlyQuery done Rev. 1133.

MHO it is side effect of ZeosLib. As i understand, doPreferPrepared option needed for speed up the execution of the SQL quries which is repeated often.

No here you missunderstand me. I try to explain the functionality:

We start from the premise that you MySQL database uses UTF8_general_ci, which means the db is able to pick up all possible chars (chinese, russian, german).

now you've posted several rows with non OS-default codepages which is easiely with D12_UP and FPC (D7-2007 need Unicode-Components like TNT -> this possibility i actual haven't open yet).

Now you want to select several rows with some chinese letters like: ZQuery1.SQL.Text := 'select * from Table1 where Field1 = '汉字/漢字';
Without my Preprepare step this statement fails or success is randomly(if it's system-compatible it could succeed). The reason is that the PlainDrivers using 1Byte Chars and your IDE uses 2Byte WideChars. Swich my PreprepareSQL step on and the results are exaclty like expected. If PreprepareSQL = False; then your statement must be handled like: ZQuery1.SQL.Text := 'select * from Table1 where Field1 = '+UTF8Encode('汉字/漢字')+';'

Hopefully you understand now the pupose of this option.

The empty updates i've to check. Can you descibe this issue more detailed please?

Best regards,

EgonHugeist

We're talking about different options. I mean that's options:
TZQuery->Options->doPreferPrepared and doPreferPreparedResolver
They differ from your very useful option TZConnecton->PreprepareSQL.
As I said earlier, state of TZConnecton->PreprepareSQL option has no effect on the appearance of the described bugs.

Kosvalery,

i must admit i missunderstoud you. I never realized this options. [s]Can you please report this bug to our bugtracker? Look here: http://zeos.firmos.at/viewtopic.php?t=1014
So we don't forget bugs like this. Maybe i've time to check whats wrong later on. Or you can help us here. That would be fine.[/s]
Patch done Rev. 1134.

But actually i must admit, i want to go on my way and fix the ASA+MSSSQL(more complicated! Actually i only can access via ADO because the old DBLib driver isn't supported any more for my valid MSSQL-Server. So i think i've to write a new one using the FreeTDS driver) and Orale-Plaindrivers. I think this has a higher precedence to get a beta/stable-state (next thing is i don't know which way the project mangager MDeams determines to go..).

So can you confirm the functionality of the TZReadOnlyQuery and TZQuery.Options.doPreferPrepared and doPreferPreparedResolver? Everything like expected?

best regards,

EgonHugeist