SynDBDataset - using SynDB or mORMot ORM with any database backend

ab · 2013-02-08 22:27:11

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset-based database provider.
See http://synopse.info/forum/viewtopic.php?pid=6663#p6663

Huge step for mORMot database connectivity!

ab · 2013-02-11 17:23:36

I've implemented SynDBZEOS direct access - directly call the ZDBC abstract layer of ZEOS, by-passing the TDataset components overhead.

Tested with tested with Oracle 11g:

Insertion speed

Oracle ODBC Oracle ZEOS Oracle
Direct 484 558 363
Batch 63259 580 367
Trans 983 1096 855
Batch Trans 61312 1098 876

Read speed

Oracle ODBC Oracle ZEOS Oracle
By one 1505 1152 439
All Virtual 74698 34644 40940
All Direct 85023 39973 48713

Our direct Oracle access layer (SynDBORacle) is the fastest, but ZEOS layer is in fact similar to direct ODBC connection, or a bit faster.

EgonHugeist · 2013-02-15 15:31:43

Hi..

Thanks for the speed tests you made! Did you test AnyDAC too?

Which tests did you use for these Results? Are You sure you've created IZPreparedStatments? Did you test large binaries too? e.g Option "CHUNK_SIZE"..

Michael, ZeosDevTeam

Btw. Zeos7.1 allready uses Prepared Statments for SQLite. For Oracle it's already implemented. Zeos7.1.x stable will be available in the first Quarter of the year.

Last edited by EgonHugeist (2013-02-16 08:15:43)

ab · 2013-02-17 06:18:25

AnyDAC is not intefaced, nor tested yet.

Direct access to the ZDBC layer does make sense.
Depending on the ZDBC driver, performance is more or less optimized.
I had to fight a bit with Unicode support, but sounds to be working by now.

I did not interfaces IZPreparedStatements by now.
It was a rough wrapping of SynDB abstract classes to ZDBC.
Lack of updated documentation of Zeos components did not help (but to be honest, the UniDAC documentation is not accurate either).
You can take a look at this unit: http://synopse.info/fossil/finfo?name=SynDBZEOS.pas

So all those benchmarks are NOT a speed test comparison of the libraries, but reflects the integration within our framework.
We did not use the fastest way of use for each library, just tried to make them work as best as possible.

See also this blog article, for a wider speed comparison.

I started a fork of ZEOS some years ago, but it was too huge for me, so I wrote my own direct layers for Oracle, ODBC, OleDB and Sqlite3, with less features, but also less overhead.
You have made great work with the 7.* series! It is a pleasure to include ZEOS support in our Open Source project.
Any feedback is welcome, especially from a ZEOS expert like you!

EgonHugeist · 2013-02-17 09:33:22

Hi Arnaud,

nice to clearify the purpose of the diagramms again (for me personally). I had a look to your SynDBZEOS.pas and i'm pretty happy to see, you understand how to work with our Dbc-Layer!!!

If you've Zeos related questions, i'll try to support as good as i can! Don't hesitate to write me on my private eMail (; .
I had a look to your SynOLEDB.pas implementation because we're looking for an ADO replacement to get OLE support for FPC running too. Well i think we need a loads of additional {$IFDEF ..}'s. One user did point me to you implementation http://zeos.firmos.at/viewtopic.php?p=16948#16948 .

May i ask you if we can start an joint-venture in this domain? If ZeosDevMember finds the time of course..

OpenSource rocks! Keep it up!

ab · 2013-02-17 13:53:54

Here is what I've added today to our documentation (SAD pdf):

ZeosLib, aka Zeos, is a Open Source library that provide native access to many database systems, developed for Delphi, Kylix and Lazarus + Freepascal. It is fully object-oriented and with a totally modular design. It access the databases by wrapping their native client libraries, and make them accessible via its abstract layer, named ZDBC. Originally, ZDBC was a port of JDBC 2.0 (Java Database Connectivity API) to Object Pascal. Since that time the API was slightly extended but the main ideas remain unchanged, so official JDBC 2.0 specification is the main entry point to the ZDBC API.
Since revision 1.18 of the framework, we included direct integration of ZeosLib into mORMot persistence layer, with direct acces to the ZDBC layer. That is, it does not references DB.pas unit, and does not need to marshall all incoming data into a TDataSet compatible layout.
Such direct access, by-passing the VCL DB additional layer, is very close to our SynDB design. As such, ZeosLib is a first class citizen library for mORMot.

Working with ZDBC layer was not difficult, since it is pretty the same as JDBC.
I had only issues with Unicode support - and did only fully test it for Delphi 7-2007 yet (perhaps I missed something about parameters to use with Delphi 2009+).
But our SynDB unit and all mORMot is already Unicode, since it uses UTF-8 everywhere, in fact.

We support only Delphi by now. We prepared for FPC but it is not finished yet. There is a lot of low-level RTTI stuff in mORMot (not SynDB.pas by itself) which is not compatible with FPC.
I did not notice the {{ issue up to now with FPC. I could easily fix it and include the fix to mORMot.
I'm not able to understand the point of this forum post: is it to add a SynDB layer to Zeos (i.e. the reverse way?).
You have to note that ADO is base on OleDB, but is not the same. Our SynOleDB unit only implements the OleDB part, not the ADO overlay.

But you are right: OleDB is deprecated.
Microsoft ask developers to switch to ODBC.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!
So I guess an ODBC layer could make sense.

I suppose adding ODBC support to ZDBC is not difficult.
You can use our units as start, without any problem.
mORMot is published under a tri-license scheme, i.e. GPL / LGPL / MPL.
So you can integrate some part of our code within Zeos LGPL project. Just do not forget to acknowledge the source.

We used a tri-license and not only a LGPL, due to some restrictions issues.
As far as I understood, the LGPL license requires you to release all the source code of any library you link statically to.
I do not understand how it is compatible with ZeosLib to be used within closed source proprietary projects, statically linked with the executable.

By email wrote:

Well i've some friendly critics accordingly the Unicode-Support you've made and i hope you're receptivily for my hints? (:
Your current implementation leads to pain in several environment settings i know about. Theire are providers like FireBird which have some very spezial CharacterSets like 'NONE' or MySQL 'binary'. Your current implenation starts allways from the premisse you open an UTF8 compatible connection. Is this correctly for OLE too? Also does the SC 'NONE' absolutely no client-characterset conversations. You've to submit the data in the collumn related collation or write data 'as is'. Very tricky. I'm not sure about, but i guess Zeos currently is the only access component which is doing this job correctly... All others do simply ignore such cases.
Well i could write you endless suggestions but i propose you check my implementation on ZCompatibility.pas and ZEncoding.pas? Theire you can find a good examle how to avoid comming encoding issues on your side.You'll need also some more informations about the client-charactersets 'em selves, which you can find in each ZPlainxxx.pas files.

You are right: SynDBZeos is limited about connection provider charset, by now.
SynDB/mORMot principle is that you use UTF-8 in the APIs, and let the DB provider to the encoding for you.
In fact, UTF-8 is supported outside the SynDB layer, but any charset should be able to be used within the connection.

I set the UTF-8 encoding within SynDBZeos.pas up to now, because I wanted it to work with tables created by our ORM, which expects Unicode/UTF-8 encoding.
It is a first draft version. Feedback is welcome, and I won't invest much time if noone is using it!
With the current implementation of ZDBC, I was not able to find out the current charset encoding of the connection, in a standard way - help is needed here.
And in some places, the returned AnsiString is of the same charset than the connection - which could be diverse, by definition.
The implementation of ZCompatibility and ZEncoding just sounds over-complicated to me. You can find a light and very fast charset support in our SynDBOracle direct layer, for instance. ZDBC just does not work with pre Delphi 2009 versions of Delphi, if the database charset does not match the current VCL charset e.g. with Sqlite3 or Firebird UTF-8 support: this is the reason why I had to use WideString in SynDBZeos for Firebird and Sqlite3, which slows down everything. For Oracle, when used on a database matching the VCL AnsiString charset, it worked.
As far as I understood Zeos string handling, it does make a lot of unnecessary charset conversion or string allocation. Just make a test about our SynDBOracle layer speed against the ZDBC Oracle provider, and you will find out what I mean. SynOleDB or SynDBODBC handles perfect Unicode process, whatever the database charset is, since by definition OleDB connection is UTF-16 (like all other WinAPI), and ODBC provides *W() Unicode APIs, used by SynDBODBC.

My question is how the current connection charset could be retrieved, and row unconverted data accessed by the ZDBC providers? For what I've seen, the internal buffer is not available, and AnsiString process seems broken (at least for some ZDBC drivers I've read). So even if I've got the connection charset, client is not able to use it as expected, since it will be converted either to the current AnsiString code page (for AnsiString method), or to WideString, but not using the encoding, for some drivers.

By the way, I have at least two main issues/limitations with ZEOS:
- Firebird embedded library can not be in a sub-folder - I add to change the code and add a prev := GetDirectory / ChangeDirectory(new) / LoadLibrary / ChangeDirectory(prev) to let it work as expected;
- SQLite3 layer identifies an INTEGER column as Delphi integer, which is wrong, since in SQlite3, an INTEGER column is an Int64 - and if you use BIGINT for an ID field, it won't be shared with the internal RowID.

EgonHugeist · 2013-02-18 13:39:36

First of all let's start from the premisse i've studied you souces in less than a half hour. I didn't know anything about synopse until one user proposed to use it as OleDB replacement + FPC.

I had only issues with Unicode support - and did only fully test it for Delphi 7-2007 yet (perhaps I missed something about parameters to use with Delphi 2009+).
But our SynDB unit and all mORMot is already Unicode, since it uses UTF-8 everywhere, in fact.

I don't know you current DataSet-implementations. But i would wonder if you get WideString and WideMemo-Fields running on D7?

We support only Delphi by now. We prepared for FPC but it is not finished yet. There is a lot of low-level RTTI stuff in mORMot (not SynDB.pas by itself) which is not compatible with FPC.
I did not notice the {{ issue up to now with FPC. I could easily fix it and include the fix to mORMot.
I'm not able to understand the point of this forum post: is it to add a SynDB layer to Zeos (i.e. the reverse way?).
You have to note that ADO is base on OleDB, but is not the same. Our SynOleDB unit only implements the OleDB part, not the ADO overlay.
But you are right: OleDB is deprecated.
Microsoft ask developers to switch to ODBC.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!
So I guess an ODBC layer could make sense.
I suppose adding ODBC support to ZDBC is not difficult.
You can use our units as start, without any problem.
mORMot is published under a tri-license scheme, i.e. GPL / LGPL / MPL.
So you can integrate some part of our code within Zeos LGPL project. Just do not forget to acknowledge the source.
We used a tri-license and not only a LGPL, due to some restrictions issues.
As far as I understood, the LGPL license requires you to release all the source code of any library you link statically to.
I do not understand how it is compatible with ZeosLib to be used within closed source proprietary projects, statically linked with the executable.

Well an direct ODBC support seems the better choise for future works. The most OS's do support ODBC. Using third party souces isn't decided yet in the team. But thank you for allowing the usage in terms of your licence restriction.

You are right: SynDBZeos is limited about connection provider charset, by now.
SynDB/mORMot principle is that you use UTF-8 in the APIs, and let the DB provider to the encoding for you.

Well this works for 95% of all cases, except for some spezials like the CS 'NONE' etc. Create a FireBird DB with default Charset 'NONE', add a talbe with some spezial collations and you run into the first issues. A starting discussion and the solution: http://zeos.firmos.at/viewtopic.php?t=3 … &start=600

You can call it feature, if you want but IMHO is it a qustion of time for bug-reports.

In fact, UTF-8 is supported outside the SynDB layer, but any charset should be able to be used within the connection.
I set the UTF-8 encoding within SynDBZeos.pas up to now, because I wanted it to work with tables created by our ORM, which expects Unicode/UTF-8 encoding.
It is a first draft version. Feedback is welcome, and I won't invest much time if noone is using it!

Me too ((: Like i wrote i'll try to support you as good as i can.

With the current implementation of ZDBC, I was not able to find out the current charset encoding of the connection, in a standard way - help is needed here.

Well i wrote a DbcMetaData function GetCharacterSet(..): IZResultSet. But i had not the time to complete it for all protocols. Here our current implementation leaks if you really need it -> go on reading.

And in some places, the returned AnsiString is of the same charset than the connection - which could be diverse, by definition.

Fact. No conversations are needed except WideString/UnicodeString mapping.

The implementation of ZCompatibility and ZEncoding just sounds over-complicated to me.

No problem it was an hint how to unify the Delphi compilers with Ansi and Unicode-Support.

You can find a light and very fast charset support in our SynDBOracle direct layer, for instance.

Equals to my implementation (; Some CS aliases are missing (ZPlainOracle.pas)

ZDBC just does not work with pre Delphi 2009 versions of Delphi, if the database charset does not match the current VCL charset e.g. with Sqlite3 or Firebird UTF-8 support: this is the reason why I had to use WideString in SynDBZeos for Firebird and Sqlite3, which slows down everything. For Oracle, when used on a database matching the VCL AnsiString charset, it worked.
As far as I understood Zeos string handling, it does make a lot of unnecessary charset conversion or string allocation. Just make a test about our SynDBOracle layer speed against the ZDBC Oracle provider, and you will find out what I mean. SynOleDB or SynDBODBC handles perfect Unicode process, whatever the database charset is, since by definition OleDB connection is UTF-16 (like all other WinAPI), and ODBC provides *W() Unicode APIs, used by SynDBODBC.
My question is how the current connection charset could be retrieved, and row unconverted data accessed by the ZDBC providers? For what I've seen, the internal buffer is not available, and AnsiString process seems broken (at least for some ZDBC drivers I've read). So even if I've got the connection charset, client is not able to use it as expected, since it will be converted either to the current AnsiString code page (for AnsiString method), or to WideString, but not using the encoding, for some drivers.

Disagree. ((: D7 -> WideFields? You simply do the job twize. The Dbc-Layer uses an enum-type: TZControlsCodePage = (cCP_UTF16, cCP_UTF8, cGET_ACP)
The default for:
Ansi-Delphi is cGet_ACP
Unicode-Delphi is cCP_UTF16
FPC is cCP_UTF8.

Imagine the IZResultSet.GetString returns a String which type ever. AnsiString/UnicodeString/RawByteString(for FPC2.7.1 which is doing automated AnsiString(CP ??) conversations). 600 compiler-warnings have been gone. Convert all between Dbc and Plain-tier nowhere else.

Here i was starting from the premisse we should be able to encode the Strings in dependencies what the Controls do expect. Theire are controls like TNT which can use WideStrings or UTF-8 (with a little hack).
Add Param 'controls_cp=GET_ACP' or 'controls_cp=CP_UTF8' or 'controls_cp=CP_UTF16' to the TZURL.Properites and you have pure UTF8/Ansi/Unicode-Strings for the Ansi-Delphis or WideFields-Support. It doesn't matter which Client-Encoding Zeos does the job. Well what's faster: The Zeos is able to do ALL encodings or the Server does it? Allways think about the compiler we have and what must be done to keep the work of the users as minior as possible. Btw. Delphi calls the same function on casting AnsiString to Wide and vice versa which Zeos has allready done if you move everything up to WideStrings. Here i've the impression the same String is converted 4x until the Dataset retrieves him? Speed?

Hint for full overview: ZCompatibility.pas procedure TZCodePagedObject.SetConSettingsFromInfo(Info: TStrings);
This Enum, the current os CP and the Client-CP are the magic key. Zeos does everything for you. Another option is 'AutoEncodeStrings=ON' which simply checks the encoding end sends the Data like the Server does expect it, but slows donw the speed for updates. No exceptions like 'Invalid Byte Sequence for UTF8'... Start from the premisse a newbie is using Zeos. Everything is optional for newbies and pro's.

Zeos is able to open Latin1 connections (even if CP 1251 isn't you current), leave the String as is/convert to UTF8 or Wide..

By the way, I have at least two main issues/limitations with ZEOS:
- Firebird embedded library can not be in a sub-folder - I add to change the code and add a prev := GetDirectory / ChangeDirectory(new) / LoadLibrary / ChangeDirectory(prev) to let it work as expected;

TZURL.LibLocation := 'C:\blabla\MyLib.dll' doesn't work to load the libs named and located somewhere else?

- SQLite3 layer identifies an INTEGER column as Delphi integer, which is wrong, since in SQlite3, an INTEGER column is an Int64 - and if you use BIGINT for an ID field, it won't be shared with the internal RowID.

Thanks for the hint (: Will be done tomorrow.

ab · 2013-02-18 14:34:55

EgonHugeist wrote:

I don't know you current DataSet-implementations. But i would wonder if you get WideString and WideMemo-Fields running on D7?

No it was not working e.g. with Sqlite3 provider, as far as I remember.

EgonHugeist wrote:

Well this works for 95% of all cases, except for some spezials like the CS 'NONE' etc. Create a FireBird DB with default Charset 'NONE', add a talbe with some spezial collations and you run into the first issues. A starting discussion and the solution: http://zeos.firmos.at/viewtopic.php?t=3 … &start=600

Within mORMot, we won't have a big problem with it, since we basically expect CRUD statements to be available, not using complex collation scheme at the DB layer.
I understand the ZeosLib point of view, which has to feature a wider range of abilities.

EgonHugeist wrote:

Fact. No conversations are needed except WideString/UnicodeString mapping.

But then, how do you convert the returned data into the current charset, if you do not know it?

EgonHugeist wrote:

Equals to my implementation (; Some CS aliases are missing (ZPlainOracle.pas)

Just check how many string allocation appear, and how SynCommons handle ASCII 7 chars, and I suspect you will find a difference.

EgonHugeist wrote:

Disagree. ((: D7 -> WideFields? You simply do the job twize. The Dbc-Layer uses an enum-type: TZControlsCodePage = (cCP_UTF16, cCP_UTF8, cGET_ACP)

I tried this property, but it was not working for me.
Is it part of ZDBC layer SynDBZeos.pas uses? Or is it at component level?
I'll check this twice.
Thanks for the input.

EgonHugeist wrote:

TZURL.LibLocation := 'C:\blabla\MyLib.dll' doesn't work to load the libs named and located somewhere else?

No, the sub libraries (e.g. icuuc30.dll) are reported as missing, if you do not change the current library to C:\Blabla
At least with latest stable 2.5.2 I downloaded from official web site.

Thanks a lot for the feedback!

ab · 2013-02-18 17:08:14

I've just used fURL.Properties.Add('controls_cp=CP_UTF8') instead of WideString conversion for Delphi 7.
Code just sounds better than my previous version, since it should now work with any connection charset, just as expected.
If I use GetBinaryString(), it just does not work, since for SQlite3 it returns always ''.
So I used GetString().
If I'm right:
- TZRowAccessor.GetString() returns the UTF-8 encoded text in Delphi < 2009;
- TZRowAccessor.GetString() returns an UnicodeString converted from UTF-8 content for Delphi > 2009.
The Result := String(TempBlob.GetString); statement in line 1003 of ZDBCCache.pas sounds like an unneeded conversion to me, for Unicode versions of Delphi.
Or should I just not use "controls_cp=CP_UTF8" for Unicode version of Delphi?

See http://synopse.info/fossil/info/1141f74f08

OLD wrote:

Insertion speed
Direct Batch Trans Batch Trans
ZEOS SQlite3 372 374 362 467
ZEOS Firebird 1791 1870 6522 7213
Read speed
By one All Virtual All Direct
ZEOS SQlite3 3486 81959 115856
ZEOS Firebird 2508 56771 71822

But, unfortunately, no noticeable speed increase... around 5-10% faster or slower, depending on SQLite3 or Firebird.

NEW wrote:

Insertion speed
Direct Batch Trans Batch Trans
ZEOS SQlite3 472 421 446 429
ZEOS Firebird 1809 1650 7368 7649
Read speed
By one All Virtual All Direct
ZEOS SQlite3 3299 100124 120307
ZEOS Firebird 2677 61863 82527

Our direct classes are still much efficient (SQLite3 reading speed is e.g. 26527 435995 438519).

About transactions, I'm quite sure I've missed something, at least for SQlite3.
Or perhaps the IZConnection.SetAutoCommit(true) does not start a transaction for the SQLite3 provider, whereas it seems to create a transaction for Firebird (as above results may indicate).
Our direct Sqlite3 classes writing speed is 536 525 93687 113527. So here transaction does make a huge difference!

Any feedback is welcome, and thanks for the "controls_cp=CP_UTF8" trick!

EgonHugeist · 2013-02-18 18:27:01

ab wrote:

No, the sub libraries (e.g. icuuc30.dll) are reported as missing, if you do not change the current library to C:\Blabla
At least with latest stable 2.5.2 I downloaded from official web site.

Should we add the extrected path to the current OS path-settings? We've the same behavior on PostgreSQL. Propose a patch (;

ab wrote:

Our direct classes are still much efficient (SQLite3 reading speed is e.g. 26527 435995 438519).
About transactions, I'm quite sure I've missed something, at least for SQlite3.
Or perhaps the IZConnection.SetAutoCommit(true) does not start a transaction for the SQLite3 provider, whereas it seems to create a transaction for Firebird (as above results may indicate).
Our direct Sqlite3 classes writing speed is 536 525 93687 113527. So here transaction does make a huge difference!

Well i won't battle with with mORMot..

Uh yes, the Transactions do terrible slow down SQLite on insertion speed if AutoCommit = True. I can not find the thread but i made a behavior test with the PreparedStamtents + SQLite:
I hope i'm right 10.000 Inserts
Autocommit = true 10000 inserts 2min?
Autocommit = False + StartTransaction 4 secs
Autocommit = False + StartTransaction + PreparedStatment (7.1) 2 secs

I was also surpriced about the huge difference!
IZConnection.SetTransactionIsolation(tiReadCommitted);

and you would wonder what happens ((: Test it!

ab wrote:

If I use GetBinaryString(), it just does not work, since for SQlite3 it returns always ''.

Surprise again!! Hmpf

ab wrote:

If I'm right:
- TZRowAccessor.GetString() returns the UTF-8 encoded text in Delphi < 2009;
- TZRowAccessor.GetString() returns an UnicodeString converted from UTF-8 content for Delphi > 2009.

This is right in your case. UTF8 is optimal, but all othe AnsiString-CP are equal possible to map into the Wide/UnicodeStrings -> ZEncoding.pas no problem. But i'll check your suggstions too.

ab wrote:

The Result := String(TempBlob.GetString); statement in line 1003 of ZDBCCache.pas sounds like an unneeded conversion to me, for Unicode versions of Delphi.
Or should I just not use "controls_cp=CP_UTF8" for Unicode version of Delphi?

Have no sources here, i'll check it. but i thing IZBlob.GetString returns allways RawByteStrings...

ab · 2013-02-18 19:14:34

EgonHugeist wrote:

Should we add the extrected path to the current OS path-settings? We've the same behavior on PostgreSQL. Propose a patch (;

Very simple: just previous := getdir + chdir(blabla) + chdir(previous).
Something like that in unit ZPlainLoader:

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: TFileName; // AB modif
begin
   if FLoaded then
      Self.FreeNativeLibrary;
   FLoaded := False;
   Result := False;
   newpath := ExtractFilePath(Location);
   // AB modif BEGIN
   try
     if newpath<>'' then begin
       temp := GetCurrentDir;
       SetCurrentDir(newpath);
     end;
   // AB modif END

{$IFDEF UNIX}
  {$IFDEF FPC}
        FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
        FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
        FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
    // AB modif BEGIN
   finally
     if temp<>'' then
       SetCurrentDir(temp);
   end;
   // AB modif END
   if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
   begin
      FLoaded := True;
      FCurrentLocation := Location;
      Result := True;
   end;
end;

EgonHugeist wrote:

Autocommit = False + StartTransaction 4 secs
Autocommit = False + StartTransaction + PreparedStatment (7.1) 2 secs

1. Is my SetTransactionIsolation() use correct?
See http://synopse.info/fossil/info/0256f1ac15
Now performance is much better for SQlite3:

Insertion speed
Direct Batch Trans Batch Trans
ZEOS SQlite3 471 485 8529 8751
ZEOS Firebird 1813 1771 7542 7746
Read speed
By one All Virtual All Direct
ZEOS SQlite3 3272 100060 117530
ZEOS Firebird 2529 60101 66367

2. How do Prepared Statements work with ZDBC?
How do you know the provider supports it?

What worries me is that is I reuse a IZPreparedStatement, e.g. in SQlite3, TZEmulatedPreparedStatement.ExecuteQueryPrepared() will inline the parameter (e.g. changing ID=? into ID=1), then call sqlite3_prepare() twice with the sample handle, so it will just fail.
In short: sounds to me that the ZDBC Sqlite3 driver just does not allow proper parameters binding!

With Firebird, the same parametrized query (ID=?) is re-used, and it speeds up the process from x1.5 to x3

Insertion speed
Direct Batch Trans Batch Trans
ZEOS Firebird 2184 2202 19666 22521
Read speed
By one All Virtual All Direct
ZEOS Firebird 4216 68108 85292

Results after commit http://synopse.info/fossil/info/9cc3701b27

EgonHugeist wrote:

Have no sources here, i'll check it. but i thing IZBlob.GetString returns allways RawByteStrings...

So string() will make a conversion into UnicodeString? ??? :s

EgonHugeist · 2013-02-23 13:31:28

I'm sorry for the delay. ):
Currently i've minior time for Zeos. Now it's weekend!

Very simple: just previous := getdir + chdir(blabla) + chdir(previous).
Something like that in unit ZPlainLoader:
function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: TFileName; // AB modif
begin
if FLoaded then
Self.FreeNativeLibrary;
FLoaded := False;
Result := False;
newpath := ExtractFilePath(Location);
// AB modif BEGIN
try
if newpath<>'' then begin
temp := GetCurrentDir;
SetCurrentDir(newpath);
end;
// AB modif END
{$IFDEF UNIX}
{$IFDEF FPC}
FHandle := LoadLibrary(PAnsiChar(Location));
{$ELSE}
FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
{$ENDIF}
{$ELSE}
FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
// AB modif BEGIN
finally
if temp<>'' then
SetCurrentDir(temp);
end;
// AB modif END
if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
begin
FLoaded := True;
FCurrentLocation := Location;
Result := True;
end;
end;

Patch done R2193 \testing-7.1 (SVN) Thank you.

1. Is my SetTransactionIsolation() use correct?
See http://synopse.info/fossil/info/0256f1ac15
Now performance is much better for SQlite3:

Well done!

2. How do Prepared Statements work with ZDBC?
How do you know the provider supports it?

The DbcConnection of each protocol decides if a PreparedStatment will be created. Theire are known issues with MySQL f.e. : http://dev.mysql.com/doc/refman/5.0/...-problems.html
To force creating the MySQL/PostgreSQL/SQLite RealPrepareds use

DbcConnection.PrepareStatementWithParams(const SQL: string; Info: TStrings): IZPreparedStatement;

and add 'preferprepared=True' to the Info-Strings.

BUT this dosn't solve you SQLite issue with Zeos-7.0.3. The CAPI Statement is supported on \testing-7.1 (SVN). Currently we do think about omiting the emulated Statements of Postgre and SQLite since the implementation is stable and only a case like 'select :p1' is NOT prepareable. If we decide this step than the RealPrepareds are default and no additional parameter must be used.

Results after commit http://synopse.info/fossil/info/9cc3701b27

Nice comment! But IMHO this should work equal with oracle.. Parameter-Binding is propably the fastest way ((:

So string() will make a conversion into UnicodeString? ??? :s

Yes this is right, and makes only sence for the binary cast... I've started to solve the GetBinaryString-Issue which is NOT supported for all cached-resultsets hmpf. Theire was no test available -> didn't see that, sorry. If i'm ready than NO converstions where done for adding the Strings to the heap and reading them with these methodes. All other behavior i'll not change. Hint this comming patch will be only available since 7.1 too.

Is theire a way to update your diagramms after my little Zeos-tutorial?

Michael

Btw. i had a look to SynCommon.pas! Terrific! Seems like you're the better coder and speed optimizer!
What are you doing with different system-codepages (932 chinese f.e.)? My current implementation does support all Windows/MacOS/*Nix codepages. I was starting from the premisse we should support all these possible CP's (since mainly the users did raise bugreports). Here i understand your UTF8 based framework which is neutral. I was also thinking about cases like OS-CP = 1251 and Client-CP = 1251. No convertations where done in this case except for the Unicode-IDE's.

ab · 2013-02-25 06:07:08

MBCS code pages will use the OS APIs.
Fixed size charset count (European, Cyrilic, Greek...) have their own optimized process, with a dedicated class.

But for all UTF-8 work, ASCII 7-bit content (e.g. numbers or field names in JSON process) will be handled in an optimized way, even for MBCS charsets.

Latest version also handle UTF-16 diacritics encoding/decoding, and content checking (i.e. invalid UTF-8 content will be identified as such).

ab · 2013-03-15 20:36:27

I've just tested the latest ZeosLib/ZDBC unstable sources, from SVN.
Sounds like if SQLite3 ZDBC provider has been enhanced for speed.

mORMot Open Source

#1 2013-02-08 22:27:11

SynDBDataset - using SynDB or mORMot ORM with any database backend

#2 2013-02-11 17:23:36

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#3 2013-02-15 15:31:43

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#4 2013-02-17 06:18:25

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#5 2013-02-17 09:33:22

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#6 2013-02-17 13:53:54

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#7 2013-02-18 13:39:36

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#8 2013-02-18 14:34:55

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#9 2013-02-18 17:08:14

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#10 2013-02-18 18:27:01

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#11 2013-02-18 19:14:34

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#12 2013-02-23 13:31:28

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#13 2013-02-25 06:07:08

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

#14 2013-03-15 20:36:27

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

Board footer