mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

SynDBOracle: Open Source native Oracle access

For our upcoming mORMot framework, and in completion to our SynOleDB unit, we just added a new Open Source unit, named SynDBOracle. It allows direct access to any remote Oracle server, using the Oracle Call Interface.

Oracle Call Interface (OCI) is the most comprehensive, high performance, native unmanaged interface to the Oracle Database that exposes the full power of the Oracle Database. We wrote a direct call of the oci.dll library, using our DB abstraction classes introduced for SynOleDB.

We tried to implement all best-practice patterns detailed in the official Building High Performance Drivers for Oracle document - see http://www.oracle.com/technetwork/topic … 131920.pdf

Resulting speed is quite impressive: for all requests, SynDBOracle is 3 to 5 times faster than a SynOleDB connection using the native OleDB Provider supplied by Oracle. We noted also that our implementation is 10 times faster than the one provided with ZEOS/ZDBC, which is far from optimized.

You can use the latest version of the Oracle Instant Client provided by Oracle - see http://www.oracle.com/technetwork/datab … ant-client - which allows you to run your applications without installing the standard (huge) Oracle client or having an ORACLE_HOME. Just deliver the dll files in the same directory than your application, and it will work.

Here are the main features of this unit:
- Direct access to the Oracle Call Interface (OCI) client, with no BDE, Midas, DBExpress, nor OleDB or ODBC provider necessary;
- Dedicated to work with any version of the Oracle OCI interface, starting from revision 8;
- Optimized for the latest features of Oracle 11g (e.g. using native Int64 for retrieving NUMBER fields with no decimal);
- Able to work with the Oracle Instant Client for No Setup applications;
- Natively Unicode (uses internal UTF-8 encoding), for all version of Delphi, with special handling of each database char-set;
- Tried to achieve best performance available from every version of the Oracle client;
- Designed to work under any version of Windows, either in 32 or 64 bit architecture;
- Late-binding access to column names, using a new dedicated Variant type (similar to Ole Automation runtime properties);
- Connections are multi-thread ready with low memory and CPU resource overhead;
- Can use connection strings like '//host[:port]/[service_name]', avoiding use of the TNSNAME.ORA file;
- Use Rows Array and BLOB fetching, for best performance (ZEOS/ZDBC did not handle this, for instance);
- TQuery emulation class, for direct re-use with existing code, in replacement to the BDE;
- Handle Prepared Statements - but by default, we rely on OCI-side statement cache, if available;
- Native export to JSON methods, which will be the main entry point for our mORMot framework;
- Compatible with Delphi 5 up to XE;
- Since it doesn't use the DB unit, nor DBExpress or such other technologies, works with any edition of Delphi (even Delphi XE Stater or Delphi 7 Personal);
- Open Source, released under a MPL/GPL/LGPL license.

Units presentation:                           
- SynCommons is used for all low-level stuff (like UTF-8 or dynamic arrays), and is common to all our units (including mORMot or SynPDF);
- SynDB is the main unit, providing abstract classes to implement

First of all, the solution expect to use a TSQLDBConnectionProperties sub-class, which will contain all connection settings (server, user and password, general parameters like CodePage or custom internal buffer size).

Then you can open a per-thread connection using those TSQLDBConnection classes.

And finally, it does rely on TSQLDBStatement classes to implement the actual SQL request.

But once you have a TOleDBConnectionProperties instance, you can execute a statement on it directly, as such:

procedure Test(Props: TOleDBConnectionProperties; const aName: RawUTF8);
var I: ISQLDBRows;
    Customer: Variant;
begin
  I := Props.Execute('select * from Domain.Customers where Name=%',[aName],@Customer);
  while I.Step do
    writeln(Customer.Name,' ',Customer.FirstName,' ',Customer.Address);
end;

var Props: TOleDBConnectionProperties;
begin
  Props := TSQLDBOracleConnectionProperties.Create(
    'TnsName','UserName','Password',CODEPAGE_US);
  try
    Test;
  finally
    Props.Free;
  end;
end;           
I think this above code is not difficult to follow, isn't it?

This unit is still in draft state, and will be available at first only from our Source Code repository, available at http://synopse.info/fossil/dir?ci=tip

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBOracle: Open Source native Oracle access

Debugging our Open Source DB classes, I found out a strange issue with OleDB provider, about CBLOB fields...
I was not the only one: see http://stackoverflow.com/questions/6147 … 01#6640101
So it's not SynOleDB's unit fault, but a OraOleDB provider issue..

The BLOB retrieved from our direct OCI classes just retrieve all existing BLOB content, whereas the OleDB version does sometimes return NULL, not for all rows!

Sounds definitively like an issue with the provider. I would recommend using direct OCI access, and do not rely on Oracle's OleDB provider...

I tested this with latest Oracle 11g Client and Server.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: SynDBOracle: Open Source native Oracle access

Wow, that is pretty impressive!
I'm glad that, once you gave up using ZEOS and chosen OleDB instead, there's still possibility to introduce direct, native drivers like that one

Hope to see others, including MySQL for example

TPrami

Member

Registered: 2010-07-06

Posts: 119

Re: SynDBOracle: Open Source native Oracle access

Hello,

SqLite, MySQL and an Firebird native drivers would be cool...

Is there anyone that knows those interfaces, so could help the guy out (I have no idea... I would help if I would be up to it)

-TP-

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBOracle: Open Source native Oracle access

SQLite is already integrated, since it's the core of our mORMot framework.

MySQL and FireBird could be added, just like I added the native Oracle version.
I suspect you can reuse a lot of existing logic of the SynDBOracle unit for those two candidates.

But honestly, I don't have time to do those port yet... I needed Oracle port because of Oracle's OleDB provider bugs (and using OCI without installation) for applications I'm working on... But I don't need MySQL or FireBird in the close future...

TPrami

Member

Registered: 2010-07-06

Posts: 119

Re: SynDBOracle: Open Source native Oracle access

SQLite is already integrated, since it's the core of our mORMot framework.

I know, I just listed it as important SQL engine/Server etc. to support...

MySQL and FireBird could be added, just like I added the native Oracle version.
I suspect you can reuse a lot of existing logic of the SynDBOracle unit for those two candidates.

OK, maybe someone could help here.  Maybe I have free time, but I am not sure am I good to produce code that is fast enough...

But honestly, I don't have time to do those port yet... I needed Oracle port because of Oracle's OleDB provider bugs (and using OCI without installation) for applications I'm working on... But I don't need MySQL or FireBird in the close future...

Yes, you need to follow your needs, time tables and road maps.

This is so interesting project that I am just hoping that it would gain more people to help you with it...

-TP-

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBOracle: Open Source native Oracle access

This is so interesting project that I am just hoping that it would gain more people to help you with it...

-TP-

Indeed!!!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBOracle: Open Source native Oracle access

About speed of this unit, I was quite amazed by the results.

In replacement of a simple TQuery populating a TList of objects from a single SELECT returning more than 400,000 rows of clients (including Name, FirstName, BirthDate, ID, and some other parameters), here are the results:

- TQuery using the BDE: more than 1 minute;
- TQuery wrapper using SynDBOracle: 5 seconds.

I did not cheat with the results, this is a real benchmark, on an existing application and real-world Oracle 11g database.

The only code I changed was the TQuery instance creation. The loop itself didn't change: it used TField local variables (FieldByName is called only once, for BDE comparison to be fair), then AsString / AsDateTime / AsDouble methods called to retrieve fields content, and create then Add() each object.

In fact, there is a lot of memory allocations which are avoided by using our classes. The data is retrieved directly from the Oracle memory buffers, just as retrieved from the OCI library.

I'm still amazed about the power and speed of the Oracle OCI client. It's very stable and reactive.