Introducing FireDAC / AnyDAC support for mORMot

ab · 2013-06-14 14:55:17

Our SynDB classes feature now FireDAC / AnyDAC access, with full speed!
See http://synopse.info/fossil/info/f0763f38de

FireDAC is an unique set of Universal Data Access Components for developing cross platform database applications on Delphi. This was in fact a third-party component set, bought by Embarcadero to DA-SOFT Technologies (formerly known as AnyDAC), and included with several editions of Delphi XE3 and up. This is the new official platform for high-speed database development in Delphi, in favor to the now deprecated DBExpress.

Our integration within SynDB.pas units and the mORMot persistence layer has been tuned. For instance, you can have direct access to high-speed FireDAC Array DML feature, via the ORM batch process, via so-called array binding.

See the SAD pdf for benchmarking and information.

Stay tuned!

warleyalex · 2013-06-28 00:55:05

I think there's a compatitility problem with FireDAC_7.0.1.3119 and mORMot.
________________________________________________________________
I realized that after I installed FireDAC_7.0.1.3119 with /IDE:D7 switch in Delphi 7, and after it was introduced Firedac support for mORMot, I'm getting AV on project testsql3 see post http://synopse.info/forum/viewtopic.php?id=1310

If remove the package FD, everything works fine!

ab · 2013-06-28 07:16:19

I installed FireDAC_8.0.1.3279 with Delphi 7.

Performance tests work just fine, at high speed, with USEFIREDAY conditional.

No AV on testsql3 with Delphi 7 + FireDAC.

So I guess there is something wrong either with the FireDAC 7.0.1, either with your environment.

Please provide additional information, and do step debugging to find out the problem.
Do you use our enhanced RTL for Delphi 7?

warleyalex · 2013-06-28 20:45:16

ab wrote:

So I guess there is something wrong either with the FireDAC 7.0.1, either with your environment.

I'm awfully sorry about this, the problem is neither FireDAC nor mORMot. I don't know why but, I've discovered that
if my environment PATH is setting to path=e:\Delphi7\Projects\Bpl --> it occurs AV

IF PATH = E:\Delphi7\projects\Bpl then
   TestSQL.exe fail AV
ELSE
   TestSQL.exe success

Thank you.

chapa · 2013-07-01 10:47:35

Is it possible, to use PostgreSql as database backend for mORMot REST server using FireDac?

ab · 2013-07-01 11:07:35

We did not integrate PostgreSQL SQl dialect yet, since we did not use it yet in our projects.

But it is perfectly feasible to add support for it, with very little changes.

Then you can use FireDAC, Zeos/ZDBC or our direct ODBC layer to access it.

If you have some knowledge of this DB, and are willing to do some testing, we can help you and integrate its support in the trunk.

chapa · 2013-07-01 11:24:12

Hi ab,

As you know PostgreSQL is open source enterprise MVCC database, and I think it will be great addition to mORMot.
Yes, I will be glad if can be in any help coding/testing/donating. Simple guide(code examples) through such process will be needed.
Have several years experience using PostgreSQL on production.

Thanks.

ab · 2013-07-01 12:16:36

You are right: PostgreSQL does make sense for us.

I've added dPostgreSQL kind of database in TSQLDBDefinition enumerate, including associated SQL requests to retrieve metadata.
See http://synopse.info/fossil/info/e13a2baa9b

We better need the database to be created in UTF8.
I forced the client to use UTF8 charset, in all cases.

It should work with FireDAC, Zeos/ZDBC, UniDAC and ODBC.

I did not tested on production yet: your feedback is needed!
Zeos/ZDBC and ODBC are certainly worth testing. ZDBC will make it fully open-source, and ready for FPC/Linux server side.

chapa · 2013-07-01 12:47:08

Thanks a lot

Just starting to migrate a real-life server-side application using mORMot with FireDAC/PostgreSQL, as SQLite is becoming a bottleneck even in test environment.
This should be some kind of test.
Will let you know when problems faced.

About UTF8 initialization of new databases, it make sense, since it is highly recommended by postgresql community.
UTF8 client connection should be suitable for most of the cases.

Thanks again.

chapa · 2013-07-04 17:52:32

Haven't dig a lot, but seems there is an issue using utf8 with FireDAC.
Trying to run PerfTest it does not insert properly Name value.

This cannot be changed, as that may break backward compatibility.
If you need to assign a "real" Unicode string, then use AsWideString.

Check here: http://www.da-soft.com/forums/anydac-ge … icode.html

ab · 2013-07-04 17:57:50

Which version of Delphi are you using?
On pre-2009 versions, you can force the text to be transmitted as WideStrings in SynDBDataset.

chapa · 2013-07-04 18:14:36

Delphi XE4, FireDAC 8.0.3.

Just changed some TADParam assignments in SynDBFireDAC.pas (in case of UNICODE defined)

P.AsString => P.AsWideString
P.AsStrings => P.AsWideStrings;

In case of .AsString assignment, FireDAC at some point issue VarAsType(<UTF8 encoded value>, varString) which seem converts the string in default code page, AFAIK.
After the changes UTF8 insertions seem correct, but other assert occur at PerfTest ValueCheck, but this may be due to postgresql specific field type/content handling.

I do not think this change is right, but just a quick fix in order to go forward.

Last edited by chapa (2013-07-04 18:50:25)

chapa · 2013-07-04 20:05:48

BCD handling is also not well (in case of currency represented as numeric(19,4) and value of 1)
json for the retrieved row contains: '"amount":01', which on behalf breaks TSQLRecord.FillFrom at Value := GetJSONField(P,P,@wasString) returning nil.

ab · 2013-07-05 08:12:11

Could you try http://synopse.info/fossil/info/e91c6108b6

And/or change again line 240 of SynDBFireDac.pas from:

 dFirebird, dMySQL: begin

into

 dFirebird, dMySQL, dPostgreSQL: begin

In fact, setting ForceUseWideString := true will force to use AsWideString(s) instead of AsString(s).

chapa · 2013-07-05 09:09:32

same problem:

Executing PerfTest.exe tuned for PostgresSQL gives:
'FireDAC PostgreSQL read failure: Value.FirstName=ValueFirstName[ i ] "Name 2"<>"Namé 2"'

In fact, same error with FireDAC's SQLite3:
'FireDAC SQlite3 read failure: Value.FirstName=ValueFirstName[ i ] "Name 2"<>"Namé 2"'

chapa · 2013-07-05 09:10:49

"ForceUseWideString := true" is taken into force only if UNICODE is not defined, as far as I see.

ab · 2013-07-05 09:27:46

chapa wrote:

"ForceUseWideString := true" is taken into force only if UNICODE is not defined, as far as I see.

Yes, it does not make sense to use it with Delphi 2009+, since string = UnicodeString in this case.

chapa wrote:

In fact, same error with FireDAC's SQLite3

In fact, I do not have any error with SQLite3 and FireDAC.
The following test works very well in my case:

    Test(TSQLDBFireDACConnectionProperties,FIREDAC_PROVIDER[dSQLite],'','','',' SQlite3',true,smFull);

So I guess there is something not good in your case.

Which version of Delphi are you using?
Are you using a particular charset in your system?

chapa · 2013-07-05 09:31:44

chapa wrote:

Delphi XE4, FireDAC 8.0.3.

Building Win32

chapa · 2013-07-05 09:36:18

Windows system locale set to "Bulgarian (Bulgaria)"

Exactly

Test(TSQLDBFireDACConnectionProperties,FIREDAC_PROVIDER[dSQLite],'','','',' SQlite3',true,smFull);

is what breaks

chapa · 2013-07-05 11:13:35

Needed changes to run PerfTests at my environment are:

SynDBFireDAC:

498:              P.AsWideStrings[i] := UTF8ToString(tmp);
515:            P.AsWideString := UTF8ToString(VData);

P.S. It is working in PostgreSQL, but not in FireDAC's SQLite3 (maybe some firedac sqlite driver settings needed?). Same Name value error with sqlite3.

Last edited by chapa (2013-07-05 11:29:14)

ab · 2013-07-05 11:59:56

In FireDAC, sounds like if TADWideString=UnicodeString for Delphi 2009+ and TADWideString=WideString before.
So AsWideString(s) does not refers to WideString, but UnicodeString in Delphi 2009+
Pretty confusing, isn't it?

Please try http://synopse.info/fossil/info/0b13b05e2f

chapa · 2013-07-05 12:36:54

Yes, you are absolutely right
So far I confirm it works with PostgreSQL, but SQLite3 breaks again at utf8-encoded Name values.

More changes should be good to be done so far:
1. Add uADPhysPG in conditional uses in PerfTest
2. Override SQLAddIndex at TSQLDBFireDACConnectionProperties in order to add "IF NOT EXISTS". Annoying things are unnecessary exceptions.

Thanks you, ab.

ab · 2013-07-08 09:38:28

Now TSQLDBFireDACConnectionProperties.GetFields() will identify indexed fields (and will therefore avoid to execute CREATE INDEX if it is already existing).
See http://synopse.info/fossil/info/edf68f6b07

chapa · 2013-07-08 09:54:34

Thanks ab

ab · 2013-07-09 06:24:26

I've enhanced indexed field detection for UniDAC too.
Now process should be consistent.

mORMot Open Source

#1 2013-06-14 14:55:17

Introducing FireDAC / AnyDAC support for mORMot

#2 2013-06-28 00:55:05

Re: Introducing FireDAC / AnyDAC support for mORMot

#3 2013-06-28 07:16:19

Re: Introducing FireDAC / AnyDAC support for mORMot

#4 2013-06-28 20:45:16

Re: Introducing FireDAC / AnyDAC support for mORMot

#5 2013-07-01 10:47:35

Re: Introducing FireDAC / AnyDAC support for mORMot

#6 2013-07-01 11:07:35

Re: Introducing FireDAC / AnyDAC support for mORMot

#7 2013-07-01 11:24:12

Re: Introducing FireDAC / AnyDAC support for mORMot

#8 2013-07-01 12:16:36

Re: Introducing FireDAC / AnyDAC support for mORMot

#9 2013-07-01 12:47:08

Re: Introducing FireDAC / AnyDAC support for mORMot

#10 2013-07-04 17:52:32

Re: Introducing FireDAC / AnyDAC support for mORMot

#11 2013-07-04 17:57:50

Re: Introducing FireDAC / AnyDAC support for mORMot

#12 2013-07-04 18:14:36

Re: Introducing FireDAC / AnyDAC support for mORMot

#13 2013-07-04 20:05:48

Re: Introducing FireDAC / AnyDAC support for mORMot

#14 2013-07-05 08:12:11

Re: Introducing FireDAC / AnyDAC support for mORMot

#15 2013-07-05 09:09:32

Re: Introducing FireDAC / AnyDAC support for mORMot

#16 2013-07-05 09:10:49

Re: Introducing FireDAC / AnyDAC support for mORMot

#17 2013-07-05 09:27:46

Re: Introducing FireDAC / AnyDAC support for mORMot

#18 2013-07-05 09:31:44

Re: Introducing FireDAC / AnyDAC support for mORMot

#19 2013-07-05 09:36:18

Re: Introducing FireDAC / AnyDAC support for mORMot

#20 2013-07-05 11:13:35

Re: Introducing FireDAC / AnyDAC support for mORMot

#21 2013-07-05 11:59:56

Re: Introducing FireDAC / AnyDAC support for mORMot

#22 2013-07-05 12:36:54

Re: Introducing FireDAC / AnyDAC support for mORMot

#23 2013-07-08 09:38:28

Re: Introducing FireDAC / AnyDAC support for mORMot

#24 2013-07-08 09:54:34

Re: Introducing FireDAC / AnyDAC support for mORMot

#25 2013-07-09 06:24:26

Re: Introducing FireDAC / AnyDAC support for mORMot

Board footer