mORMot Open Source

All Zeos internal Batches have been broken for a long time, because the batch ababilites haven't been determined correctly. Now you'r back on fast zeos batch bindings without the strings mORMot did create for you. Those are slow for FireBird and since FB exists they always recomment using parameters and native bindings.

Could you please update Zeos from SVN and test. R6380 see https://sourceforge.net/p/zeoslib/code-0/6380/.

@AB,

hi thanks. Good start. I'm not so familar with Git... Oldscool svn user.
I know about the forum rules so please consider: https://www.file-upload.net/download-13 … patch.html
this patch includes:
- real transaction handling for zeos
- adds possibility for parameters if they are added to the databasename (starting with '?') see TSQLDBZEOSConnectionProperties.URI
- changes TSQLDBZEOSStatement.ReleaseRows. IIUC is purpose to free memory in background. That's supported since 7.2 already
- fixes possible wrong MatchPatterns for MetaInformations.

I do understand yours POV. It's not that i'm against that. It just feels like wasting valuable manpower. From my POV it's not task of your project to embedd all server access technics. Usually this is a neverending story. The servers change behaviors, they add funtionality so it's a permantent maintaing job which will never end.

That's why i am so sensitive . Those guys are not uptodate what happend in background enbetween. They should open there mind and give zeos a fair chance.

thanks for the nice words, guys. We alse keep FPC support high.

Probably true. If you have applied the patch than you could add the commented code the the benchmark tests (see TSQLDBZEOSConnectionProperties.URI)
so you can compare your external SQLite with Zeos in all your modes and you have fair benchmarks.

you can simply turn ON/OFF the binary wire by Adding

'BinaryWireResultMode=true or false'

. See the stmt parameter section in SynDBCZeos.pas. Accordin the code size, yes i'm not happy about it too. It is huge because we keep all backward compatibility high. We have users how are using old MySQL3 or FB1 or PosgreSQL7. But as you pointed out there is no performance penalty..

Zeos is gangled by obscure tests. There are users who are doing things i would never do but it's not forbitten and should be support. An example for an SQL string:

    '/*?? do we find these ?question-marks? as parameter ? */'+
    'select * from people?2 where p_id > ?'+LineEnding+
    'and p?red = ?'+LineEnding+
    '/* ? and those marks? Are they ignored too?'+LineEnding+
    '? Are they ignored on a multi-line comment as well?*/'+LineEnding+
    '-- ? and those marks? Are they ignored too? On a single line comment?'

All servers allow such *beep*. Test your questionmark replacement. There are exacly two params to replace..

I'll remove the old ColumnToJSON overload soon. So thx for lately paches and the test do run again. Even if current SynDBPostgres dos not find libpg on my system and i have no PG-instance runinng on the default port. There is work left, Pavel.

It's not everything for performance. But i'll and if i can help i'll do it. Hope you guys too. ZeosLib hab bad critics according performance. Most of them from the TDataSet users, but from you as well. And they where right in the past. Inbetween also the TDataSet layer did speed up very nice. Everything which is faster on dbc is logically faster for the TDataSets as well. I've no other compents to compare but i would start from the premisse ZeosDBO has a nice comparable performance inbetween(And that's for free) even if all other components are optimized for the DS approach only.

Another reason for the binary wire was to suppress some issues PG has with the strings. F.e.:

insert into double_values values('-2.2250738585072014E-308'),('-1.7976931348623157E+308'),('2.2250738585072014E-308')....

you can send/retrieve them. In the past we had the Extended type in our code. I broke with the type since CPU64 doesn't have the REAL10 any more. So we can not read back the value with the string protocol with our native StrToFloat using double precision. There is exacty one digit to much before the exponent.

Nice job Pavel.

Well this might be true for you example. Nowaday those numbers do grow quickly. The more the pg date(4Byte)/time(8Byte)/timestamps(BByte)/doubles/numerics are samller than using strings..

that is true but the binary wire protocol is optional except for parameter bindings.  You mean this https://sourceforge.net/p/zeoslib/tickets/336/ propably.

The interfaces indeed had been a problem where we used virtual methods. The compiler did produce duplicate offset calcultations for thre vmt's. Inbetween zeos does no longer use the virtual methods any more in most relevant places. So the zeos access using interface has the same performance like using objects. Thus this argument does not count for me.

This parser is also no longer slow on 7.3. I've added pchar markers instead. No memallocs are done except to hold the markers in a list. The parser works perfectly to replace the question-marks for the oracle and postgres. Looking to ab's approch it's a nice nouvice state. Zeos is full of examples where his technick would fail propably.

Now you make me curious. To many strings? While you using the string result and binding techniks? What do you mean?

Yesterday i found the reason for the performance penalty. I've already commited the changes. So on my side SynDBZeos is slightly faster.

The more SynDBZeos is outdated. I send  AB several mails with fixes but all of them has been ignored last year.
Relevant changes of the my maintained SynDBZeos:
-StartTransaction(including nesteds) so the AutoCommit mode is not required to use any more.
-full outparm support for SP's without using the IZCallableStatement
-better ColumnsToJSON with several JSON serialization option (manly for the date values)
-correctly determine batch ababilities on connect so the define is obsolete.

@AB is https://synopse.info/fossil/info/9fe77d6f21 realy necessary? No SQL standard any more? Is that syntax supported by UniDac/FireDac? That broke all zeos compatibilty.

@mpv

i did look to your pull request. The Line

fDataBase.SetReadOnly(False)

is not required. It's Zeos default.

According the patch for 7.2-patches you have:
Open a ticket on https://sourceforge.net/p/zeoslib/tickets/
tag it as feature request and write purpose.
add a diff/patch file.

I'm willing to merge, if there is no other behavior change. That's it.

Accoding the performance: On 7.3 all the Interface calls to IZPLainDriver-Descendants do no longer exist(except dblib-protocol -> lack of time to do it)
So i would start from the premisse 7.3 is again a bit faster as 7.2 was.

Current Zeos 7.3 is nextgen ready btw..

Which dataset? Looking to that i do understand your point of view.
@AB
Where are these informations comming from? Do i miss a point in mORMot wonderland?

Resolved in SVN (Trunk/testing-7.3)

TestCase added, sorry for the inconsistency..

Everything *new* might be risky but without a testcase/ticket on the bugtracker or a post in the zeos forum you shouldn't expect help immidiately.
Have fun

?? thats a PG thing only and just an advantage for updates iirc. This has nothing todo with "modern". An index scan is getting slower here as a side effect.

Hi,

have a look @ https://firebirdsql.org/file/documentat … types.html
section Character Indexes.

Most DB's (except SQLite, Postgre) do limit indices to size of the variable character string. As documented the maximum length for an index equals one quarter of the page size.

your field nome is a unlimited blob subtype text. FireBird can't create an index here. The more fetching such data is slower than fetching a varchar(255) f.e. For all known dbs except (except SQLite, Postgre).

limit your fieldsize to a value you need in reality:

  property Nome:  RawUTF8 index 255 read fNome  write fNome stored AS_UNIQUE

to be clear: You need to re-create this table or do the altering manually. Just change the code as suggested would not resolve your problem.

Hi JD,

{$if defined(ZEOS73UP) and defined(USE_SYNCOMMONS)}
  fResultSet.ColumnsToJSON(WR);
  {$ELSE}
...

as Arnaud wrote: the IZResultSet.ColumnsToJSON is part of Zeos, defined in ZDbcIntfs.pas.
It skips the interface calling chain and brings best performance per driver to write the JSON contents.

I wonder about your regression. As the define shows, you can use the procedure only if both defines are enabled.
The ZEOS73UP define is located in Zeos.inc (included in SynDBZeos.pas) and available only in the 7.3 branches. 
Secondary define is your choice to define it in your Project.

Check old revisions of zeos on your computer. It looks to me like your mixing old files somewhere. Check the USE_SYNCOMMONS define too. 

@hnb
great news for Zeos, thank to all.

@DDGG

I don't wanna be rude, just want to help ... But

this IS strange to me. (;

Nope i don't think so. mORMot just needs the GetMoreResults logic to query all pending results. And as i said, it's a question of design..
Propose you flush the SP's and use native queries for the job, if it is acceptable for you.

Use the TZReadOnlyQuery in unidirectional mode.

Looking to the thrown Error .. Is it possible you do fetch to many rows? Use chunks instead.

Works like a charm! Thanks Arnaud.

Arnaud, i wouldn't bother you if there is a solution.
It is confusing becuase changing the TestCase to:

function ShowMyMissingName: RawJSON;
var
  Item, Arr: Variant;
begin
  Arr := SynCommons._Arr([]);
  Item := _JSON('{"ID": 1,"Notation": "ABC"}');
  Item.Add('Price', 10.10);
  Item.Add('CustomNotation', 'XYZ');
  Arr.Add(_Copy(Item)); // <------------------with _Copy()
  Item.Delete('Price');
  Arr.Add(Item);
  Result := VariantSaveJSON(Arr);
end;

doesn't help neither.

Ok second testcase 4 you (done everything double with _Copy() and _Unique() )

function ShowMyMissingName: RawJSON;
  Item, Item2, Item3, Arr: Variant;
begin
  Arr := SynCommons._Arr([]);
  Item := _JSON('{"ID": 1,"Notation": "ABC", "Price": 10.1, "CustomNotation": "XYZ"}');
  Arr.Add(Item);
  _Unique(Item); //as documented in [url]https://synopse.info/files/html/Synopse[/url] … l#TITLE_43
  Item.Delete('Price'); //this still effects element 0 in the array because you still refrence same VName array
  Item2 := _Copy(Item); //as you can see i do copy not by ref
  _Unique(Item2); //now i also make the item unique which is redundant because copy is doing this already
  Item2.ID := 2;
  Arr.Add(Item2); //i add second item to the array

  Item3 := _Copy(Arr._(1)); //copy second element of Arr into Item3 (currently not assigned)
  _Unique(Item3); //make item3 unique which is redundant too
  Item3.Delete('CustomNotation'); //after doing this step we'll have a json parse error for element[0] in the array
  Item3.ID := 3;
  Arr.Add(Item3); //add item3 to array as third element

  Result := VariantSaveJSON(Arr);
end;

Result is:

Done like documented, thought. But wat was happen with my array??

Third case 4 you:

 
function ShowMyMissingName: RawJSON;
begin
  Arr := SynCommons._Arr([_JSON('{"ID": 1,"Notation": "ABC", "Price": 10.1, "CustomNotation": "XYZ"}'), _JSON('{"ID": 2,"Notation": "XYZ", "Price": 9.1, "CustomNotation": "aaaa"}')]);
  Item := _Copy(arr._(0));
  _Unique(Item1); //make item1 unique which is redundant because of _Copy()
  Item.Delete('CustomNotation');
  Result := VariantSaveJSON(Arr);
end;

Result is:

Done like documented too. Is this realy expected?

I know this case isn't usual.
However what do you think about this:

function TDocVariantData.Delete(Index: integer): boolean;
const RefCountOffSet = SizeOf(SizeInt)+SizeOf(Longint); //<-  depends to compiler and target (just a proposal)
var N: TRawUTF8DynArray;
  I: Integer;
begin
  if cardinal(Index)>=cardinal(VCount) then
    result := false else begin
    dec(VCount);
    if VName<>nil then begin
      If PLongInt(NativeUInt(VName)-RefCountOffSet)^ > 1 then begin
        System.SetLength(N, Length(VName)); //<- make a unique dynarr
        for i := 0 to length(VName) do
          if (Pointer(VName[i]) <> nil) or ((I < High(VName)) and (Pointer(VName[i+1]) <> nil)) then //two empty names lead to parsing errors 
            N[i] := VName[i] else
            Break;
        VName := N;
      end; 
      VName[Index] := '';
    end;
    VarClear(VValue[Index]);
    if Index<VCount then begin
      if VName<>nil then begin
        MoveFast(VName[Index+1],VName[Index],(VCount-Index)*sizeof(pointer));
        PtrUInt(VName[VCount]) := 0; // avoid GPF
      end;
      MoveFast(VValue[Index+1],VValue[Index],(VCount-Index)*sizeof(variant));
      TVarData(VValue[VCount]).VType := varEmpty; // avoid GPF
    end;
    result := true;
  end;
end;

This would reduce the memallocs to minimum but there are the offsets and type of RefCount ... which may differ for compiler + target..

else ....
The only code to work with would be something like:

Item := _JSON(VariantSaveJSON(arr._(0))

Should i go this route?

@Alfred

consider ALL Lazarus/CodeTyphoon IDE's currently use the {$codepage utf8} define, because of the wrong premisse every string is ut8 encoded.
From my personal POV you'll never be able to get a stable behavior running except your tracking the define which than breaks our tests.. The FPC DefaultSystemCodePage is always set to UTF8. So the failing test regression tests need to be UTF8Encoded !!IF!! this define was set. Same trouble i've on Zeos side.. MyTests just make it possible to run in 3 environements + some subsettings (unrealted here): CP_ACP, CP_UTF8 and CP_UTF16. Zeos is able to make a "floating" behavior ... up until latest FPC3 (just for my test > Zeos it's selves can what i want!) So we'll tag current 7.2-testing as a RC-candidate next days.

Regards, Michael

AFAR did Zeos run in trouble on WinCE because FPC uses the $UNICODE define to indicate a Unicode plattform instead of a Unicode-IDE.

No idea if this issue still remains or if MarcoVV replaced the defines.

@sevo did you try to comile Zeos on WinCE?

A good point!

Since FPC 2.7 the official codepage agnostic has been introduced.

Looking @ SynCommons all (RawUnicode, RawUTF8, WinAnsiString, RawByteString) are wrongly declared as AnsiString for all non-Unicode targets inlcuding FPC2.7+

Instead of Setting the codepages Arnaud should check FPC-Version or add a global property based define like in Synopse.inc

@AB

i agree with Daniel.

In addition consider a connection should never expire (and erased on server side) between TimeOutMinutes range if the connection was bussy.
From my POV a Stmt should allways reset the fProperties.fConnectionTimeOutTicks if a prepare/executeprepared was called. Because the server has activity and there is no reason to close the connection.

Daniel's second issue looks like the connection is closed while a query was executed. Result is the posted error. If your stmts reset the Connection inactivity tickcount that issue shouldn't happen any more.

What do you think?

@AB

stop thinking your framework is runing into an issue.. Nope! NOO multithreading problem...

@itSDS

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
please read documantions. All of us are trying to support you.

According using Zeos: add your expected TimeOut props to the Zeos connection strings see:
http://dev.mysql.com/doc/refman/5.0/en/ … tions.html

!!--->>> MYSQL_OPT_READ_TIMEOUT/MYSQL_OPT_WRITE_TIMEOUT

like 'MYSQL_OPT_WRITE_TIMEOUT=WhatEverYouWant_Seconds'
Have a look at ZDbcMySQL.pas -> procedure TZMySQLConnection.Open

Such props need to be set before a connection is opened. No idea how FireDac/UniDac is doing the job. Docs?

Hope it helps. Have fun comparing access rates of FireDac/UniDac vs. Zeos7.2+

@hnb

From my POV there is no reason to change the CCS. Libpg.dll perfektly converts Latin2 to UTF8 and vice versa.

Note: We did some performance changes on the String getters: since mORMot is a UTF8 based framework we (Arnaud & Me ) did decide to change the IZResultSet-Getters to GetPAnsiChar getters for some RDBM like PG, Oracle, SQLite, MySQL.
So what you're trying to do may leads to pain on composing the JSON contents.
You should also change the Getter to GetUTF8String() for the PG-Protocol which than kills the performance(First alloc mem for a String, move Data from libpg to String, move content to JSONWriter, free mem) which is skipped by ZDBC using the Pointer-Getters...

My advice: Clean bad encoded strings once in your DB and keep the mORMot/ZDBC code as is..

Cheers, Michael
ZeosDevTeam

Close the Connection if NOBODY is connected..

We all have rare free time, a family and a day to day job. The job you're asking for isn't done in 5 minutes. Not for Arnaud neither for me. FB is a complex API, even if it's ready(execpt ARRAY DESC TYPES) on Zeos, but changing the whole TA behavior infacts ALL drivers not FB only -> the reason why such request are in job queue but not done yet.

Be patient, help by your selves via coding and constribute your findings or ... $$$?

Offtopic:

Arnaud that's one of the reason for the design/model of Zeos. The Results and Statements are different. Zeos can reuse the Parameters as a Prepared stmt. For some drivers like ADO,MSSQL,SYBASE,MYSQL i can NOT reuse the IZResultSets. Initial issue is i don't know for sure if GetMoreResults(ZEOS) can retrieve another ResultSet or not(except i'm starting a huge SQL-parsing which leads to pain and performance loss-> shouldn't be task of Zeos from my POV). For drivers like SQLite,FireBird,PostgreSQL and Oracle i was able to reuse the IZResultSets (Something similar like your SeekFirst option, which avoids rebuilding the column-informations, buffer-allocations etc.).

Oftenly i was thinking about changing and simplifiying the design of Zeos(IZStatement+IZResultSet in one Interface) to something similar like your TSQLRequest model, but exactly these (and some other Zeos internal automations like the IZCachedResolver) cases have been stopping my ideas...

So let's see how you resolve this case... I was a bit surpriced why this issue never did popup here. I'll stay tuned!

Some notes:

The behavior i wrote previously is also setting-dependend.
Getting the UpdateCound/RowCount as first resultset is optional. Have to study my MSSQL book again.. Back hole in my head now. Can't remember the option yet. Would this be interesting?

Great news, Arnaud.

Accordingly the dyn-array params... Maybe i'm wrong but this little christmas gift could be related: http://bugs.freepascal.org/view.php?id=27206
See the rttichanges.path patch.

@ttomas

Thanks for follow my advice. Results are what i expect and i wrote several times.

Of course having no open TA would be nice but for what exactly with FB?. Again: This is a documented behavior. You'll never ever be able to execute a stmt in any kind with FB and there is TA opend: FB/IB-Docs page 317 ... 336+ NO transaction handle -> raise an error. NO SELECT, simply NOTHING. That's FB/IB.

Common Access API by using interfaces, I assume that's why Zeos behaves like it currently does, but i don't know that for sure. From my POV "alltime code". After reading your logs: case closed for 7.2! It might change if minior version increases... Behavior is locked on 7.2.x All TA's started/Leveled/commited or rolled back as I expect it (:

Thank you for the IB-trace idea.

BTW:
@Daniel
what about the Dead-Lock issue? A week is over! Are you happy now?

Michael.