mORMot Open Source

@marcinb

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

@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

@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.

@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.

@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.

@xwg
applied to 7.2-patches. Thank you. See: https://sourceforge.net/p/zeoslib/code-0/7344/
Regards, Michael

ping, ping, ping..

@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

as been introduced by PostgreSQL, thougth.

Ping, Ping..

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..

@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:

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.

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

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.

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:

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

@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 ).

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

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

@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

@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".

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

  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

Thanks for confirmation.

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.

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...

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...

@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..

@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