[patch_done] Delphi 2010 + MySQL + UTF8

[EgonHugeist]

Dear jeremicm

To applay the Patch use a tool like TortoiseMerge from http://tortoisesvn.net.
First i made DIFF-files but ZEOS dosn't want to upload the DIFF's... So i choose the *.Patch files.

Follow the instuctions. If it does not work i can upload the changed files and you have to select the differences by your own.

[mdaems]

EgonHugeist,

I applied your patches (manually) and did get test suite errors for Delphi 12

4) TestPreparedStatement: ETestFailure
at
"mysql5/mysql-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"
5) TestPreparedStatement: ETestFailure
at
"mysqld5/mysqld-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"
6) TestPreparedStatement: ETestFailure
at
"mysql5p/mysql-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"
7) TestStoredResultSetUpdate: ETestFailure
at
"mysql5/mysql-5: Different stream size. Expected: 1024. Actual: 1451."
TestStoredResultSetUpdate: ETestFailure
at
"mysqld5/mysqld-5: Different stream size. Expected: 1024. Actual: 1451."
9) TestStoredResultSetUpdate: ETestFailure
at
"mysql5p/mysql-5: Different stream size. Expected: 1024. Actual: 1451."

and

1) TestQueryUpdate: ETestFailure
at
"mysql5/mysql-5: Different stream size. Expected: 1024. Actual: 1451."
2) TestQueryUpdate: ETestFailure
at
"mysqld5/mysqld-5: Different stream size. Expected: 1024. Actual: 1451."
3) TestQueryUpdate: ETestFailure
at
"mysql5p/mysql-5: Different stream size. Expected: 1024. Actual: 1451."
4) TestPreparedStatement: ETestFailure
at
"mysql5/mysql-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"
5) TestPreparedStatement: ETestFailure
at
"mysqld5/mysqld-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"

7) TestPreparedStatement: ETestFailure
at
"mysql5p/mysql-5: Different stream size. Expected: 1024. Actual: 1451.Binary Stream"

So, seems like your patch really messes up binary (BLOB) data.

I'm afraid we had this problem before. The reason is you transform one byte AnsiChars to two byte chars and back. Which probably works out well for characters, but messes up (eg. image) binary data.

Mark

BTW : what sourceforge user should I give commit rights?

[EgonHugeist]

Mark, sorry my fault...
I use Binary data in my projects too, but do it by Base64-En-/Decoding. So i did not check this Problem.

I solved the Problem in the TAbstractBlob.String-setter/getter so i'm hopefull it works correct now. I started your Testsuites and no error was shown.

it concerned the
http://zeos.firmos.at/viewtopic.php?t=3330 and

I send a complete Diff-Zip, hope you check them again and commit them(Attachment...). There are addititional *.Diff's which switch of some deprecated and other Compiler-Warnings concerning http://zeos.firmos.at/viewtopic.php?t=3315 and
http://zeos.firmos.at/viewtopic.php?p=13640#13640

Now i think Zeos is fully Unicode-ready for MySQL. I checked it with Chinies, Russian, and other Latin Characters from the Forum.
Otherwise it's an improvement, and no real solution to handle these Characters...

Later i'll check Firebird/Interbase.

BTW SourceForge-User: egonhugeist

I would be proud to get commit-rights.

[mdaems]

Egon,

You do have commit rights now.

But, please don't commit these changes just like this. I did try to apply these and my automated test suite started to produce errors again for the Delphi 12 test run. Mainly about BLOB/CLOB differences.
I admit, it's possibe this comes from the test suite itself. Maybe it's just not usable for D12+, but then we should fix the test suite too. Do we need to upgrade the DUnit version used? (in test/external/ directory)
Or maybe I didn't patch exactly the same way.
Can you:
* checkout a fresh working copy
* run a test suite and save the output (compile and test logs) as a 'ref log' for next steps
* apply the small 'compiler warning fixes', rerun test suite and check differences in logs
* commit these changes already
* apply all other changes needed for your problem
* make an svn diff file against your working copy (Using TortoiseSVN?)
* send it to me so I can test
?

Mark

[EgonHugeist]

Mark,

i'will
* checkout a fresh working copy
* run a test suite and save the output (compile and test logs) as a 'ref log' for next steps
* apply the small 'compiler warning fixes', rerun test suite and check differences in logs
* commit these changes already
* apply all other changes needed for your problem
* make an svn diff file against your working copy (Using TortoiseSVN?)
* send it to you so you can test

didn't know the test/external/Dunit Project since this moment.
I'd only run the Test*.Suites in the \delphi15\ZeosDboDev.groupproj and no error where shown by using my last postet Patches. Now i can see what else is possible and i have to configurate some files.. No fine way because in your TestSuites i'm an Newbe to and i've some Proplems yet. So it will spend some time and look what the Portal says about configurate the Testsuites. But it looks like a black hole or am i wrong?

Next day:

I configurated the test.properties (test_template.properties renamed) not easy(have to think in) if never used before and changed the install_*.bat. So the next idea was born: What du you think about an TestConfig-project for dummys or newbes like me?

Next:
Some Exceptions where raised on

procedure TZSupplementarySQLTestCase.ExecuteScripts(
FileNames: TStringDynArray; RaiseException: Boolean);
var
I: Integer;
ScriptPath: string;
begin
{ Sets the right error event handler. }
if RaiseException then
FSQLProcessor.OnError := RaiseError
else FSQLProcessor.OnError := SuppressError;

ScriptPath := TestConfig.ConfigFilePath;

for I := 0 to High(FileNames) do
begin
FSQLProcessor.Script.Clear;
try
FSQLProcessor.Script.LoadFromFile(ScriptPath + FileNames); -> raises an AccessViolation Exception

cause ZComponent*.bpl.ZSQLString.pas.TZSQLStrings.constructor Create; misses the inheritance! So the internal TStringList is not assigned!

so i patched it to

constructor TZSQLStrings.Create;
begin
inherited Create; {: added by EgonHugeist -> needed to run the TestSuite else Self.Methods fails}
FParams := TStringList.Create;
FParamCheck := True;
FStatements := TObjectList.Create;
FMultiStatements := True;
FParamChar :=':';
end;

Next Problem (MySQL5):
in database\create_mysql_bugreport.sql line 122:
insert into table799863 values (1024); aborts the script. Other tables where not created and testSuited fails.
commented it out /* insert into table799863 values (1024);*/ to go on.

Now i run the TestSuites with the GUIRunner DUnit....
->
every writline fails if not was predifined, but why?
If i do not use the DUnit it works well, any ideas?

And how to configure the performance test?
Next idea do a WriteLog to a *.txt file instead of writeln();

Now testsuite is running and what i've to say? You are right it realy messes up binary data, so i've removed this attachments. Here are my findings, Mark:

the PAnsiString or PAnsiChar can not handle some Turkish, Russian, Chines letters. Zeos does some UTF8Encoding/Decoding to handle this. BUT if you do an ZStatement.ExecuteQuery of unprepared Data it messes up the En/Decoding. the One-Byte Ansichar is the Problem there. I wonder why it was no theme at older versions or aren't they Unicode-ready?

It seems we have to prepare the query in case of it's used fields and do that Encoding/Decoding before executing all Query's(Update, insert, select) for D12Up or am i wrong? Otherwise it seems not to be handled..

Regards

[mdaems]

EgonHugeist,

Yes, the test suite isn't an easy thing to set up. I agree on that. If you find things that are just wrong with it and you can fix it... Just do so.
I'd advise you to run the test suite in batch mode. That way you can run them using a script and catch output in a log file easily.

Actually, I did the effort to learn to use the 'apache ant' scripts in the build directory. So I can just doubleclick build.cmd and all components and the test suite are build and then all test suite programs run spooling their log in the logs subdirectory. You should just make a build.properties config file from the svn build_template.properties file and make a build\TestEnv\binaries directory where the actual executables will be running from. (Here you can also put db dll's when you don't have them in your system path)

Concerning this threads problem, I also wrote an answer here : http://zeos.firmos.at/viewtopic.php?t=3367

It seems Zeos must preprepare the Query-Statements, check for Binary-Data, and send a pepared Query to the database...

That's exactly what should be done in an ideal world! Actually, this prepreparation should disassemble sql statements moving all literal strings to parameter-like fields and only at the very last moment (ie. right before passing to the database dll) the query should be re-assembled. Implying all sql should be passed around as a special TZSqlObject from the component layer through the dbc layer to the plaindriver layer.

But can we find a task group of 4 community members who want to design this change and implement it afterwards? And can this change be done keeping database drivers we can't really test ourselves?
Why the task group? Because it's a very complex thing and once you start coding the idea should be approved by some more 'pairs of eyes'.

Mark

[EgonHugeist]

EgonHugeist,


Concerning this threads problem, I also wrote an answer here : http://zeos.firmos.at/viewtopic.php?t=3367

It seems Zeos must preprepare the Query-Statements, check for Binary-Data, and send a pepared Query to the database...

That's exactly what should be done in an ideal world! Actually, this prepreparation should disassemble sql statements moving all literal strings to parameter-like fields and only at the very last moment (ie. right before passing to the database dll) the query should be re-assembled. Implying all sql should be passed around as a special TZSqlObject from the component layer through the dbc layer to the plaindriver layer.

But can we find a task group of 4 community members who want to design this change and implement it afterwards? And can this change be done keeping database drivers we can't really test ourselves?
Why the task group? Because it's a very complex thing and once you start coding the idea should be approved by some more 'pairs of eyes'.

Mark

Absolutly Mark,

I've scrutinised the whole dbc, parsesql and core functions to find out if there is a workaround possible. So it should be possible IF every PlainDriver uses the TZTokenizer/TZGenericStatementAnalyser and if we are able to predetect which used Field is binary and which not. There we can manipulete the ttWord(in case of an Parameter like UTF8ConvertNames: Boolean = False ), ttQuotedIdentifier-Tokens via UTF8Encode and everything should be fine or not?
We can minimze this process by using DetectUtf8 so we don't need to disassamble the Query if no Binary-Field is use. There we can do UTF8Encode(SQL) and send this to the Plain-Driver.

MySQL use the TZTokenizer/TZGenericStatementAnalyser and Firebird don't need it(Plaindriver.Prepares the Query and gets FieldInformations after Execute and Error-Checking from TSQLDA) for example. And so this did not solve it yet. But if we do it we can disassemble, manipulate and assemble the Query-Statement by using the existing Zeos-Functions and if we know which Tokenizer.ttQuoted is binary and which not. (Binary-Detect is not possible if using Unicode i think).

What we need is an additional MetadataType like mdBinaryFields which Results a TStringDynArray or better a Record(Catalog, Table, field). But i'm not sure if ADO is able for it..
We can call it on connecting and after every alter/drop table-Statements (why this: to scrimp time). Than add it to the IZConnection and Give it as a Paramter to the Analyser.

Do we need an additional ZObject/Interface or do we need some additional functions which to keep the Plaindriver structure ?

What do you think? Is there a taskforce which is working on it? I'll join...