#1 Re: mORMot Framework » Firebird transactions » 2021-05-06 05:11:52

@vs

that's a missing implementation in the FireDac implemenation of AB. Arnaud's implementation has no AutoCommit mode: http://docwiki.embarcadero.com/RADStudi … _(FireDAC). You can do same with Zeos8 using a non AutoCommit connection. The 'hard_commit' property simply is ignored for Firebird-ISC_TPB_AUTOCOMMIT transactions because of the retaining mode. I don't see a problem in Zeos but using FireDAC according it's documentation.

@AB,
iv'e noticed several issues using your performance FireBird tests without embedded mode(easy to reproduce):
Some resultsets are not release properly after the data was fetched. Some like 'select count(*) from sample_record' or 'select MAX(ID) from sample_record', or TORM.GetOne. The point is Zeos can't know if more rows are in queue, thus the orphan TXN is hold by the opened cursor and is waiting for getting no more rows to close the txn. It would be nice if the ORM classes free the resouces if the result was consumed. i.e. IZResultSet.ResetCursor.

All tabular streamed servers (except MySQL/PosgtreSQL which are caching the result in the client-lib) keep an open cursor too. (Sybase, MSSQL Server) f.e.

#2 Re: mORMot Framework » MSSQL Server in mORMot2 » 2021-05-06 04:40:11

Hi sakura,

that's an example how you can connect to SQLServer with Zeos8:

OleDB:

TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI('OleDB[mssql]', '',''),
              Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=zeoslib;Data Source=K95VM-Egon\SQLEXPRESS2012;MarsConn=Yes;Initial File Name="";Server SPN="", DBUser, DBPassword)

or
ODBC_W:

TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI('odbc_w[mssql]', '',''),
              'DRIVER=SQL Server Native Client 11.0;Server=(localdb)\ProjectsV13;DataBase=zeoslib;Trusted_Connection=Yes;MARS_Connection=yes', '', '')

Note the protcol-name prefix. I'll change that later, propably -> users can specify the server and choose the protocol. Like 'mssql[FreeTDS]' or mssql[OleDB] etc.
We've also been testing the SynOLEDB implementation. AB's implementation has no native Batch bindings and no (IIRC) server side prepared statements. Thus we've noticed the Zeoslib implementation is loads faster in practice. Btw. Both (Syn/Zeos) ODBC implementation are almost much slower than OleDB in practice. OleDB should be first choice if you use a windows service using SQLServer.

Make you own tests and share your findings..

#3 Re: mORMot Framework » Non ASCII characters in TSQLDBStatement.RowDocVariant » 2021-04-20 03:55:25

@marcinb

what's the database characterset? If it's characterset "none"? If so you'll propably need an extra advice.
Michael

#4 Re: mORMot Framework » Firebird transactions » 2021-04-20 03:51:25

Hi vs,
If you have such "old" transactions than you have open cursors somewhere propably. Take care you read all results until no more row is returned. Another cause of many ransactions might be nested transactions with multiple connections. Note a second call to IZConnection.StartTransaction creates a savepoint. First call to IZConnection.Commit/Rollback just releases/rollback the savepoint but not the transaction you wanna close.

According the "hard_commit" option: If you are adding that option after creating a transaction then the option is a NOOP.
I can't see a zeos regression. Hope you use minimum version v7.2.10 or v8.0 from trunk.

Hope that helps, Michael

#5 Re: mORMot Framework » External Zeos MySQL tables with underscore ( _ ) cannot be registered. » 2021-04-12 14:20:10

@AB

change SynDBZeos.pas method TSQLDBZEOSConnectionProperties.GetFields
lines

    
    sTableName := meta.AddEscapeCharToWildcards(sTableName); //do not use "like" search patterns ['_','%'] so they need to be escaped
    res := meta.GetColumns('',sSchema,sTableName,'');

To

  res := meta.GetColumns('',sSchema, meta.AddEscapeCharToWildcards(sTableName),'');

IZMetadata.GetIndexInfo() simply does not support a pattern. That should help flydev.
Regards, Michael

#6 Re: mORMot Framework » mormot_base.lpk + zeos » 2021-03-05 05:48:10

Hi,
i never did test the mORMot packages. That's new for me. OT: What are the advantages having them?
Just a side node: Zeos8 from trunk has a designtime package: zcomponentdesign.lpk. IMHO you guys should use this for the LCL-IDE instead. The zcomponent.lpk package is just a runtime package inbetween. Maybe that helps?

#7 Re: mORMot Framework » mORMot2: List index out of bounds in ZDbcResultSetMetadata.pas » 2021-03-02 06:29:13

@L_VV
I moved the define. I can't say why "SYSTEMINLINE" should be out of scope.

@Arnaud,

you are right! ENABLE_SQLANY/ZEOS_DISABLE_SQLANY added see: https://sourceforge.net/p/zeoslib/code-0/7357/
Btw. That's the reason for the ugly "duplicate Enable/Disable" check.

So plz add

  {$if defined(ENABLE_SQLANY) and not defined(ZEOS_DISABLE_SQLANY)}
  ZDbcSQLAnywhere,
  {$ifend}

To both units. Thank's for the 1.18 Merge.

#8 Re: mORMot Framework » mORMot2: List index out of bounds in ZDbcResultSetMetadata.pas » 2021-03-01 05:48:42

@Arnaud
Nice! Well two small things should be worth it todo:

Index: mormot.db.sql.zeos.pas
===================================================================
--- mormot.db.sql.zeos.pas	(revision 1078)
+++ mormot.db.sql.zeos.pas	(working copy)
@@ -64,7 +64,8 @@
   ZDbcOracle,
   {$ifend}
   {$if defined(ENABLE_ASA) and not defined(ZEOS_DISABLE_ASA)}
-  ZDbcASA,
+  ZDbcASA, //protocol name = ASA
+  ZDbcSQLAnywhere, //protocol name = asa_capi
   {$ifend}
   {$if defined(ENABLE_POOLED) and not defined(ZEOS_DISABLE_POOLED)}
   ZDbcPooled,
@@ -79,7 +80,9 @@
   // main ZDBC units
   ZCompatibility,
   ZVariant,
+  {$ifndef ZEOS80UP}
   ZURL,
+  {$endfif}
   ZDbcIntfs,
   ZDbcResultSet,
   ZDbcMetadata,
@@ -681,7 +684,7 @@
     sSchema := Utf8ToString(Schema);
     { mormot does not create the Tables casesensitive but gives mixed cased strings as tablename
       so we normalize the identifiers to database defaults : }
-    sTableName := meta.GetIdentifierConvertor.ExtractQuote(Utf8ToString(TableName));
+    sTableName := meta.{$ifdef ZEOS80UP}GetIdentifierConverter{$else}GetIdentifierConvertor{$ifend}.ExtractQuote(Utf8ToString(TableName));
     sTableName := meta.AddEscapeCharToWildcards(sTableName);
     //do not use "like" search patterns ['_','%'] so they need to be escaped
     res := meta.GetColumns('', sSchema, sTableName, '');

Summary:
the ZURL unit is empty inbetween, the TZURL class is moved to global connectivity unit ZDbcIntfs. This also fixes a dreprecated hint for the "GetIdentifierConvertor" -> replaced by "GetIdentifierConverter".

Finally, please merge the changes to 1.18

@L_VV
thank you too. According the WITH_INLINE define: It's defined in ZeosLazarus.Inc for FPC see:

{$IFDEF SYSTEMINLINE} //global FPC define
  {$DEFINE WITH_INLINE}           // compiler supports inline methodes
{$ENDIF}

seems the global define proposed by FPC-core seems to be unavailable? Can you confirm it?
According the units: https://sourceforge.net/p/zeoslib/code-0/7351/

If there are still changes required, make a diff/patch. Thank your for your support.

#9 Re: mORMot Framework » mORMot2: List index out of bounds in ZDbcResultSetMetadata.pas » 2021-02-28 06:08:15

@L_VV

interesting. On my side the code dit compile because the units

ZDbcFireBird, ZDbcODBCCon, ZDbcSQLAnywhere

are not part of SynDBZeos/mormot.db.sql.zeos.
Should be fixed by https://sourceforge.net/p/zeoslib/code-0/7348/

According the GENERIC_INDEX case: See my update last post. I dd change that some months ago. If Zeos8, which is stable, is released (just the releaes notes are not complete) i'll remove the define. Means all versions >V8.0 have the zero based index then.
According the defines:
You can NOT mix the USE_SYNCOMMONS and the MORMOT2 define. Just an approach to handle such ideas: https://sourceforge.net/p/zeoslib/code-0/7349/
USE_SYNCOMMONS uses the 1.18 filenames and MORMOT2 .. self explainatory, i guess, am i wrong?.

@AB
please consider my last post and:

  {$if defined(ENABLE_ADO) and not defined(ZEOS_DISABLE_ADO)}
  ZDbcAdo,
  {$ifend}
  {$if defined(ENABLE_DBLIB) and not defined(ZEOS_DISABLE_DBLIB)}
  ZDbcDbLib,
  {$ifend}
  {$if defined(ENABLE_MYSQL) and not defined(ZEOS_DISABLE_MYSQL)}
  ZDbcMySql,
  {$ifend}
  {$if defined(ENABLE_POSTGRESQL) and not defined(ZEOS_DISABLE_POSTGRESQL)}
  ZDbcPostgreSql,
  {$ifend}
  {$if defined(ENABLE_INTERBASE) and not defined(ZEOS_DISABLE_INTERBASE)}
  ZDbcInterbase6,
  {$ifend}
  {$if defined(ENABLE_FIREBIRD) and not defined(ZEOS_DISABLE_FIREBIRD)}
  ZDbcFirebird,
  {$ifend}
  {$if defined(ENABLE_SQLITE) and not defined(ZEOS_DISABLE_SQLITE)}
  ZDbcSqLite,
  {$ifend}
  {$if defined(ENABLE_ORACLE) and not defined(ZEOS_DISABLE_ORACLE)}
  ZDbcOracle,
  {$ifend}
  {$if defined(ENABLE_ASA) and not defined(ZEOS_DISABLE_ASA)}
  ZDbcASA,
  ZDbcSQLAnywhere,
  {$ifend}
  {$if defined(ENABLE_POOLED) and not defined(ZEOS_DISABLE_POOLED)}
  ZDbcPooled,
  {$ifend}
  {$if defined(ENABLE_OLEDB) and not defined(ZEOS_DISABLE_OLEDB)}
  ZDbcOleDB,
  {$ifend}
  {$if defined(ENABLE_ODBC) and not defined(ZEOS_DISABLE_ODBC)}
  ZDbcODBCCon,
  {$ifend}

or should i make a pullrequest?

BTW: What is ZDbcZeosCon? This unit is not known to me.

#10 Re: mORMot Framework » mORMot2 TRestServerDB for external DB » 2021-02-27 18:07:03

Not a problem. OpenSource rocks!
Leave some work 4u: Mormot v2 should not use the USE_SYNCOMMONS define any more. Replace it with MORMOT2. If you or others think the define is "dangerous" to use give me a hint for a better name and i'll replace it ASAP.
Michael

#11 Re: mORMot Framework » mORMot2: List index out of bounds in ZDbcResultSetMetadata.pas » 2021-02-27 18:01:35

@ab,
propably all is correct except SynDBZeos/mormot.db.sql.zeos

there is a missing define:

{$IFNDEF ZEOS72UP} //<------ that's missing
const
  FirstDbcIndex = 1;
  TableNameIndex = 3;
  ColumnNameIndex = 4;
  TableColColumnTypeIndex = 5;
  TableColColumnTypeNameIndex = 6;
  TableColColumnSizeIndex = 7;
  TableColColumnDecimalDigitsIndex = 9;
  IndexInfoColColumnNameIndex = 9;
{$ELSE}
uses ZDbcMetadata;
{$ENDIF}

the constants are required for earlier versions than 7.2. propose to omit them, 7.1 is a dinosour, and 7.2 is old inbetween.

#12 Re: mORMot Framework » mORMot2 TRestServerDB for external DB » 2021-02-27 10:34:24

@Vitaly,
got 8.0 running with mORMot 2. See https://sourceforge.net/p/zeoslib/code-0/7347/ also on Linux 64Bit.

Note the "USE_SYNCOMMONS" define is left for 1.18. To be able to distinguish between the unit names i use a new define "MORMOT2" yet. We can change the name if somebody has objections..?
Regards, Michael

#14 Re: mORMot Framework » Delphi 10.4, Zeos 7.2.8 Stable and MySQL with Mormot 2020-11-09 » 2020-11-13 17:59:31

@Erick
Note i don't have a XE10.4 by hand because of the missing community edition. As you pointed out: It's compiler related. Yet i can't help.
But i propose you switch to Zeos8 on https://sourceforge.net/p/zeoslib/code- … ree/trunk/
7.2 is old, some in the team hang on 7.2, i don't do..
ps: It can't be sooooo complicated to catch the little bugga with the debugger. I would be happy if you could resolve it and raise an ticket against https://sourceforge.net/p/zeoslib/tickets/

#16 Re: mORMot Framework » Performance tests are not comparable » 2020-09-15 18:24:55

mpv wrote:

Michael, the problem is only with TDateTime=0 ?

Yet, yes for sure. The test (yet) don't pass and get's abortet, except i apply my patch as provided. Propaply Armaud has a nother change in mind, but i would be lucky to get his test running somehow on different instances and with the current expected values..

mpv wrote:

BTW by default postgres error messages language depends on lc_locale parameter in postgres.conf (sets to OS locale during posrgres setup) Also can be changed for connection using
SET lc_messages TO 'en_US.UTF-8';

Yup i know that, however, that's not my main goal here, just try to run AB's tests.

#17 Re: mORMot Framework » UniDAC vs. ZEOS » 2020-09-15 18:17:37

@Pavel, did you ever test it? Maybay it's a lack of my knowledge, but i'm not able to.. Thus using DatabaseName := '127.0.0.1:5438' fails same as using '//localhost:5438' as you suggest. That's way

PQconnectdb

as been introduced by PostgreSQL, thougth.

#18 Re: mORMot Framework » UniDAC vs. ZEOS » 2020-09-13 14:57:56

ab wrote:

Thanks for the fresh numbers!
big_smile

Just a question: why didn't you include the SynOracle, SynPostgresql, SynOleDB and SynODBC numbers? It could be interesting to include the info with SynDB direct access units.

SynPostgresql can not be tested, it permanently fails: Performance tests are not comparable and only the default Port is supported..

#20 Re: mORMot Framework » Performance tests are not comparable » 2020-07-31 16:52:08

You're fast! I lke that! :kiss:
According the toppic: the returned and character fixes Postgres error is (German):

"Name    Wert
errMsg    'FEHLER:  Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »0000-01-01 00:00:00«'#$A

Sadly SynDbPostgres is'nt able to transalate the Exception to a native OS errer. However (i'm feeling like users who are asking me!) the reported issue remains, Arnaud. Plz consider using you own tests including Pavel's SynDbPostgres. Btw. repeating the "read all rows"[virtual/native] reading test,  10x, making an avg of 7... who will win the performance challange?
Regards, Michael

#21 Re: mORMot Framework » Performance tests are not comparable » 2020-07-31 15:56:45

After updating to latest trunk, i have an internal Compiler error: URW1136 in SynDB.pas now. Current compiler is an old Delphi XE (code is better than 10.3 yet). Ideas about? Do you support Delphi in future? I'll check which commit did kill the compiler next days. Sadly there is just a useless git hash and no revision number available..

#22 Re: mORMot Framework » Performance tests are not comparable » 2020-07-30 16:07:51

@ab thanks for the affort. What can i say, i give up.
Nope it still doesn't work. Still the same issue i reported about postgresql. Did you ever test the patch using your own performance tests?

PS:

function TTimeLogBits.FullText(Expanded: boolean; FirstTimeChar,QuotedChar: AnsiChar): RawUTF8;
var tmp: array[0..31] of AnsiChar;
begin
  if Value=0 then
    if QuotedChar<>#0 then begin
      FastSetString(result,nil,2);
      result[1] := QuotedChar;
      result[2] := QuotedChar;
    end else
      result := '' else
    FastSetString(result,@tmp,FullText(tmp,Expanded,FirstTimeChar,QuotedChar)-@tmp);
end;

still returs an empty quoted string for a zero value whereas the pascal integral date is always: Year: 1899; Month: 12; Day: 30. It may be different to the SQL servers you're connection against- JFYI.

#23 Re: mORMot Framework » Performance tests are not comparable » 2020-07-30 07:58:13

@ab see attached patch above.

The proplem is how BoundArrayToJSONArray works with the timestamps. Postgres simply raises and error if no full timestamp string is send.
As i said BoundArrayToJSONArray simply works wrong for postgres:

EgonHugeist wrote:

It's about the the behavior of DateTimeToIso8601Text, sometimes the datepart is not written (if Trunc() returns zero) and sometimes the timepart is not written (if Frac() returns a zero value). Postgres expects consistent values (just the second fractional part might be vary withing microsecond precision range). And of course the 'T' delimiter is desturbing pg too.

the more if the value is zero just the quotes are written. It's about this inconsistency. I never did understand why DateTimeToIso8601Text is working that way, propably because your framework is not able to differentiate Time/Date/DateTime types, is guess.

#25 Re: mORMot Framework » ORM, SynDBZEOS, Firebird Datetime params [SOLVED] » 2020-07-30 04:27:46

moctes wrote:

Well I'm here to learn smile in fact your snippet is better I mainly use mORMot as a kind of proxy usually the data is coming from another api, I'll follow your advice and give it a try

Do you know if PROTOCOL can be somehow changed to firebird3up on TSQLDBZEOSConnectionProperties ?  I suppose it would be more appropriate

The "firebird3up" protocol is the legacy api replacement of fbclient.dll using the object interfaces. See https://firebirdsql.org/file/documentat … s-api.html,  i added some month's ago. The reason for the extra protocol name is: some in the Zeos team would like to test/use firebird 3 with the new interface api and with the old legacy api of course.
We'll leave the protocol by now... Feel free to test. It's possible we'll remove the protocol name again by implementing a preloading mechanism of the library and decide wich ZDBC objects are used for the connection. I just wrote the driver, the new name wasn't on >my< whishlist smile

The new API is supposed ( by the FB devels )to be faster. I didn't make performance tests yet, due the fact i need to patch SynDBZeos again to get multiple protocols running using the same SQLDefinition enum SynDB requires for..


Update
I've removed the protocol name now! It depends on the client lib which API is used. Using firebird3+ with the legacy API can be improved by choosing the "interbase" protocol name. Thus the name was superfluous. It will be mentioned in the rease notes.

#26 Re: mORMot Framework » ORM, SynDBZEOS, Firebird Datetime params [SOLVED] » 2020-07-29 17:43:02

moctes wrote:

I want to apologize, there was a wrong config setting from my side (somewhere along the testing I did this ), so the queries made with TZQuery were executed against a test database and (ouch!) the SynDB where being executed against a dev database, so the advice from @tbo about DateTimeToIso8601 was the definitive answer for getting this running with parameters sorry if i wasted your time, it is already working as it should.

Lorbs, looking what you're doing ... why the hell.. You convert a valid TDateTime value to a Raw-String?
Isn't

fDbConnection.Execute( 'select field1, field2 from mytable where mytable.UPDATED_AT >= ?', [aTimeStamp] ); 

exactly what you want? Or does it not work?
OTH the 'T' delimiter is desturbing most sql db's. MSSQL prefers the packed format without any delimiters, oracle as well. Firebird, MySQL, PostgreSQL, ASE, ASA simply don't understand this format it's just for SQLite and MongoDB, and the JSON's usefull, thought.

#27 Re: mORMot Framework » Error with MSSQL, zeos connection » 2020-07-23 17:11:20

Hi cregonat,

Zeos is using a different binding technique in comparison to Syn[OleDB/ODBC]. We're using direct bindings -> if possible (alloc a buffer once and don't use any latebindings). That's why the Syn-access layers are slower than using ZeosLib for ODBC/OleDB. The more using the SynDB logic, the optimized plan for the request is determined per execution (use the MS-SQL profiler to see what happens), whereas the plan is pretty fix using real prepareds. For me it's a know'n issue you've been running into. ZeosLib is trying to get parameter-descriptions from the SQL-Server by using an immediately prepare + obtaining the descriptions. If done we're allocating the required parameter buffer once, and do all neccessary conversions to prevent reallocations where ever we can. Sadly the expensive MS products are failing sometimes(with obscure error codes), especially using parameters in sub-selects as you did. Also are selects with a "group by" clause dangerous to use while in explizit transaction. There are several MS-QC reports available but they are more than 10 years old inbetween, not one resolved. So know the product you're using.
To cirucmvent you issue you just need to add the paramter of ZDbcProperties.pas:

Properties.Values[DSProps_DeferPrepare]='True'

to your Statement-Properties. Zeos will fallback to the slower syndb compareable performance.
JFYI, Michael

#28 Re: mORMot Framework » Performance tests are not comparable » 2020-06-09 04:17:04

@Arnaud

nope that's not working with your tests. If i do it like this:

Index: SynDBPostgres.pas
===================================================================
--- SynDBPostgres.pas	(revision 9453)
+++ SynDBPostgres.pas	(working copy)
@@ -779,9 +779,10 @@
 
 procedure TSQLDBPostgresStatement.ExecutePrepared;
 var
-  i: PtrInt;
+  i, N: PtrInt;
   p: PSQLDBParam;
   c: TSQLDBPostgresConnection;
+  pStart, pEnd: PUTF8Char;
 begin
   SQLLogBegin(sllSQL);
   if fParsedSQL = '' then
@@ -799,10 +800,36 @@
     p := @fParams[i];
     if p^.VArray <> nil then
     begin
-      if not (p^.VType in [ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8]) then
-        raise ESQLDBPostgres.CreateUTF8('%.ExecutePrepared: Invalid array type % ' +
-          'on bound parameter #%', [Self, ToText(p^.VType)^, i]);
-      p^.VData := BoundArrayToJSONArray(p^.VArray);
+      if p^.VType = ftDate then begin
+        N := Length(p^.VArray);
+        SetLength(p^.VData, N*26+1);
+        PStart := Pointer(p^.VData);
+        PStart^ := '{';
+        PEnd := PStart + 1;
+        for N := 0 to N-1 do
+          if P^.VArray[N] = 'null' then begin
+            PCardinal(PEnd)^ := PCardinal(P^.VArray[N])^;
+            (PEnd+4)^ := ',';
+            Inc(PEnd, 5);
+          end else begin
+            PEnd^ := '"';
+            PDateTime(@p^.VInt64)^ := Iso8601ToDateTimePUTF8Char(
+                    PUTF8Char(pointer(P^.VArray[N]))+1, Length(P^.VArray[N])-2);
+            DateToIso8601PChar(PDateTime(@p^.VInt64)^,PEnd+1,true);
+            inc(PEnd,11);
+            TimeToIso8601PChar(PDateTime(@p^.VInt64)^,PEnd,true,' ',True);
+            (PEnd+13)^ := '"';
+            (PEnd+14)^ := ',';
+            Inc(PEnd, 15);
+          end;
+        (PEnd-1)^ := '}'; //replace last comma and close the json obj
+        SetLength(p^.VData, PEnd-PStart);
+      end else begin
+        if not (p^.VType in [ftInt64, ftDouble, ftCurrency, ftUTF8]) then
+          raise ESQLDBPostgres.CreateUTF8('%.ExecutePrepared: Invalid array type % ' +
+            'on bound parameter #%', [Self, ToText(p^.VType)^, i]);
+        p^.VData := BoundArrayToJSONArray(p^.VArray);
+      end;
     end
     else
     begin

then it works. Means i convert the inconsistend array items into it's TDateTime value, and write a full timestamp string..
@pavel feel free to test or implement it different.. (plz think about time/date oid's the patch would break compatibility for such oid's)

#29 Re: mORMot Framework » Performance tests are not comparable » 2020-05-30 04:22:22

@Arnaud, yup the oracle tests are passing now, thank you.
Hope you find time to fix the postgres batches too? You just need to use full timestamp strings using space instead of the 'T' delimiter. That would fix the batches for timestamp fields (but it would not work for time or date fields sad ).

#31 Re: mORMot Framework » Performance tests are not comparable » 2020-05-29 15:48:16

Hi Arnaud,
ping.., could you please have a look to my reported regressions? IIRC the oci issue did start with introducing the new float-conversion.
btw: 32-Bit delphi is used, dosn't matter which one. All of them do fail on my side. Just get it running would be nice, it dosn't matter about results.
Michael

#32 Re: mORMot Framework » Hope to add the NativeDB driver support for Sybase SQL Anywhere » 2020-05-29 15:42:30

Just a note from me: I've updated the Native ASA driver of zeos for V17. Also did i add a new driver with protocol name "asa_capi" for the recommended sybase api.

#33 Re: mORMot Framework » a trouble with TSQLRecord.FillOne/FillRow and FB TIME field » 2020-05-29 15:39:13

Hello vitaly,

i'm sorry for that regression. I just didn't count the 'T' delimiter in my mind. Other drivers had the same mistake(copy+paste issue), so i fixed them all. Plz update from SVN. Regression gone?
Michael

#34 Re: mORMot Framework » Performance tests are not comparable » 2020-05-18 04:52:17

@Pavel,

i'm using Synopse\SQLite3\Samples\15 - External DB performance\PerfMain.pas from trunk.
According oracle: I get

Oracle read failure: Value.Amount=(i+1)*0.01 0<>0.02  (D:\Synopse\SQLite3\Samples\15 - External DB performance\PerfMain.pas, Line 513)

According postgres: It's about the the behavior of DateTimeToIso8601Text, sometimes the datepart is not written (if Trunc() returns zero) and sometimes the timepart is not written (if Frac() returns a zero value). Postgres expects consistent values (just the second fractional part might be vary withing microsecond precision range). And of course the 'T' delimiter is desturbing pg too.

#35 mORMot Framework » Performance tests are not comparable » 2020-05-17 15:30:04

EgonHugeist
Replies: 23

Hello Arnaud, just a note from me:
In current state of mORMot am not able to compare SynDBOracle vs. Zeos, same to SynDBPostgreSQL vs. Zeos on my system (Delphi(D7..XE13.3+Windows).
SynDBOracle returns fails,  SynDBPostgreSQL crashs with the batch inserts of timestamp fields. Could plz check it?

Michael

#36 Re: mORMot Framework » Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478 » 2020-05-01 07:14:14

Hello JD, your regressions are not reproducable for me. Hower i trust in AB's look at my ASM approaches and did comment the code until it's resolved properly.

@AB according the FPCX64 abi i did add the asmmode switch for intel asm. (i don't like that ATT quirk) so what's wrong there?
IIRC are you using volatile values. I made the two methods as pointers only because i've to swap Single and Double values also.
So my idea was have two(i had a two byte version using

rol [register], 8

too) methods which are doing the swaps on the addressed values so it's always are reference.
Hope you understand my POV. Any hint or help would be welcome smile

#37 Re: mORMot Framework » Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478 » 2020-04-29 05:50:39

@JD
Which complier are you using? And which compile target(platform)?

Try to comment line

{$DEFINE INTEL_ASM} //allow intel syntax assembler

in \src\Zeos.inc. I didn't change many things lately in Postgres except switching procedure Revers4Bytes and Reverse8Bytes to asm. Maybe this fails. I'm not a asm guru.. It perfectyl works on Windows even with 64Bit..

Regards, Michael

#39 Re: mORMot Framework » Hope to add the NativeDB driver support for Sybase SQL Anywhere » 2020-04-20 15:09:01

@Flashcqxg
if updated the zeos api of the "ASA" protocol for SQLAnywhere17.

I've to revert my words about "old" API. The api still works perfectly and is continued to be supported by SAP. It's just the documentation of Sybase/SAP which is missing(some purposes of parameted are unknown and we send dummy values). See http://codeverge.com/sybase.sqlanywhere … ocs/841759 others did run into same issue.
So Zeos !HAS! a native driver for SQLAnywhere. The Zeos protocol is named "ASA".

EgonHugeist wrote:

However iirc, the user can't access the protocol because of the mORMot URI limitations thought.

means you need to patch SynDBZeos because the enumeration AB uses is just a limitation in this area.

I've moved my old Sybase API files to 7.3-testing. It's not complete and optimized, compilation might fail, so it's not officaly added to the zeos packages. It's a lookup nothing else. Other DB-vendors have higher priority for me. I've a pending native access drivers for DB2,Informix,FB3up in queue. Means you have a driver to work with which propably is faster than FireDAC or UniDAC or something else which is using the TDataSet's. 

Regards, Michael

#40 Re: mORMot Framework » Hope to add the NativeDB driver support for Sybase SQL Anywhere » 2020-04-15 09:16:24

Hi, Zeos has a native acess driver for Sybase SQL Anywhere. But it's not using FreeTDS lib.

The protocol name is 'ASA'. To be honest the access api used in Zeos is very old, still supported by Sybase but there is no documentation available anymore. I've a "new" driver started two years ago but i never had the time to finalize it. The current "DBLIB" implementation is quiet slow becsause a teammember added a precaching technic for the obsolate DBLIB drivers ['mssql','sybase']. The reason is/was the DBLIB api has many limitations. The types a undefined just as binary/text returned(signed/unsigned ordinals are quiet uncleare too). Jan did force a full load of data and ask metainformations afterwards(just a nice technic for TDataSet users). That's why is don't use it anymore.

Instead it's woth it adding a CTLIB api but FreeTDS is not ready supporting it.(the Sybse Adaptive server already did deprecate DataBase-LIB and suggest to us C-Type-LIB API instead)

However iirc, the user can't access the protocol because of the mORMot URI limitations thought.
Regards Michael

#41 Re: mORMot Framework » Fix SynDBOracle for work under linux » 2020-04-04 17:18:34

  Result := OCI.EnvNlsCreate(fEnv,EnvironmentInitializationMode,
    a,b,c,d,0,@dummyusermem,OCI_UTF8,OCI_UTF8);

Just a hint from me: characterset "utf-8" of Oracle and MySQL are just CESU-8 -> 3-Byte encoding. The name is wrong and did expire 2003. Zeos silently hooks it to AL32UTF8 which is real UTF8. You shold not use this charset, Arnaud.
See: https://community.oracle.com/thread/351 … 0&tstart=0

#42 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-31 09:25:42

Vitaly wrote:

Sorry, this part I couldn't understand even after reading it several times. Could you explain it to me a bit more, please? Why it should be cyrillic encoded? Maybe I'm wrong, but I think that ISO8859_1 (or Latin-1) doesn't support cyrillic characters, and it doesn't make sense in fact: NO_NO collation is for Norwegian language. I'm a bit confused here...

My bad, sorry, i just read you're from Russia ):

According the conversions: Seems you're a lucky man in your case. Others may see questionmarks for some chars only.

Btw. keep the USE_SYNCOMMONS define. That make the ColumnsToJSON process faster.

I've no more advices by now... Happy coding

ps.
Just commited a small performance improvement for your situation: https://sourceforge.net/p/zeoslib/code-0/6393/ Hope it helps a bit wink

#43 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-31 06:27:12

Vitaly wrote:

Yes, all works fine (with and without USE_SYNCOMMONS)! Zeos 6392, and I've updated mORMot to the latest, but I don't think that it mattered here. Great! smile

Thanks for confirmation.

Vitaly wrote:

Concerning UTF8 codepage setting in Zeos properties in my case (for ISO8859_1 FB DB):
All works, but until it tries to insert/update with the value, containing some non-ISO8859_1 character.

Here we go:
That doesn't make sence 4me. Why is it possible that users inserting data which your database is NOT able to store. Avoiding this exception by using ISO8859_1 instead of UTF-8 means silent charcter loss. Means all unicode characters which can not be converted silently turn to '?'-questeion-marks. Is that what you want and the user who sends the data expects?

The more, zeos is doing duplicate A2W2A conversions for all character getters and setters. Ok the server usally is doing that (if not UTF8). So in your case the client needs the CPU ticks. That's neither fast nor a good design.

Vitaly wrote:

I'd love to switch to UTF8, but we have such a restriction currently because of the legacy software.

I don't know anything about the software. So i understand your POV. 
However i strongly recommend not using  ISO8859_1 for your if you can't guarantee all characters are cyrillic encoded. Instead of, create your DB in with UTF-8 default charcterset. Just use ISO8859_1 collations if you need them.

OR just give the user a note / how an failure to notify him such characters are not supported, as FB shows you...

#44 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-30 15:05:48

Hope i got it. I found a missing ColumnCodePage determination for LOB SUBTYPE TEXT in the ResultSetMetainformation. I did a bit more also for the CharacterSet NONE.
Please update + test...

#45 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-30 08:15:50

No idea yet. Which compiler are you using?
Note mORMot is optimized for UTF8 only. And expects utf8 in all areas. 
So please add

 ZeosURL.Properties.Values['codepage'] := 'UTF8'

Much better would be recreation your DB with default characterset UTF8.

It would be nice if you could pinpoint the problem...

#46 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-30 06:59:29

Great. Now please test the USE_SYNCOMMONS define again. Does it work?

#47 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-30 06:14:41

@Vitaly
before writing something about charactersets + mORMot let's see if the characterset can be found now: https://sourceforge.net/p/zeoslib/code-0/6387/
The regression happens only if there is a spezial collation for the field. Is there such one?
please update + test..

#48 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-29 17:39:12

Still not able to reproduce it. I did some commit which suppress the reported AV, but i have no clue where the regression should come from yet... the Args in the function should never be empty.
Any news on your side?

#49 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-29 12:19:56

@Vitaly
added junked reads for utf8 clobs + firebird. But i think that would not help for the "invalid BLOB ID". Plz update.
Again: check your code for commit/rollback. Do you have such while reading the results?

@ab thank's for the quick commit

#50 Re: mORMot Framework » AV at BatchSend with Zeos/FB » 2020-03-29 10:50:24

Then you propably have a commit or rollback in your code while reading from the resultset. Is it possible? FB thows all blob id's away if you do that...

Board footer

Powered by FluxBB