What are options for a Delphi app to migrate away from BDE if using Oracle database

Question

We have a Delphi 5 app connecting to an Oracle 8i database.

Most logic is siting in the database as Stored Procedures. Currently we are using the Borland Database Engine (BDE) to connect to Oracle via OCI client 9.2.

This app was originally written in '96 using Delphi 1 then upgraded to Delphi 5 in '99.

The plan is to upgrade to XE client and Oracle 11g.

What are what the options to simply replace the BDE with something else and keep the effort to a minimum?

So far I have seen:

• AnyDAC (http://www.da-soft.com/anydac/)
• devart ODAC (http://www.devart.com/odac/)
• DOA (http://www.allroundautomations.com/)

I am not considering DBExpress as I do not believe that it will have the performance that we need.

Things that are important are:

• ease of upgrade, e.g. wizard or some other tool to convert DBE components,
• Good Performance.

I am leaning towards devart as they do have a migration tool (although it only does a partial job) and they have a direct TCP option to connect to database without the need for a client.

Goalie

What makes you think dbExpress won't have "the performance you need". do you have any facts or are you making the decision purely on "belief" without data? dbExpress can be very high performance indeed, especially if you do it right. And any Data access layer implemented poorly will result in a slow app. – Ardeliaardelis

I would go for devart, we are using it to connect to postgres databases for a little over a year and we are very pleased with performance, the only "issue" we have thus far is the lack of multithreaded connection – Voluble

Ok, I'll admit that maybe I've been a bit premature excluding DBExpress, especially if there is a bit of support for it. – Goalie

OK so know I believe that I was getting DBExpress and ADO mixed up. With a typical query I got the following: - ODAC 2.7s - DBExpress 2.8s - ADO 9.5s – Goalie

dbExpress itself is fast. But dbExpress + DSP + CDS is slow. But to make bidirectional cursor you will need to use this bundle. Also, we migrated our applications to AnyDAC - work great, fast and stable ! – Domenic

robmil, yes that is exactly what I have just seen. To get the DBGrid loaded I had to use the TSimpleDataSet and now my query time has gone out to 12s. I guess that is because it is loading in the entire result set. – Goalie

AnyDAC is ideal for migrating from BDE because, among other things, there is no problems with fields mapping (persistent fields, ...). You can map all fields exactly as BDE. – Seale

dbExpress may not support some Oracle features you may want to use. For example does it support TIMESTAMP WITH TIMEZONE data type? – Islamism

EDIT NOTE: I changed your question title, because otherwise future people asking a more general question would be tempted to think this one a dupe, even though it's highly ORACLE focused. – Ardeliaardelis

@Warren P Yes that was a good idea thanks. This is for an app that is Oracle only and will never need to connect to anything else. – Goalie

Answer 1

You will need to learn about the differences between BDE and other architectures. Whether you plan to move to dbExpress or not you should read and learn from this whitepaper.

The architecture of dbExpress is an intentional architecture, designed to address critical flaws not only in the BDE's lowest levels, but also in its upper levels (the TTable/TQuery components) and architecture. Understanding the reasons for those changes, and the limitations in the BDE that they address, is a key element in moving your app off the BDE, to anything.

As for your contention on performance, I like to see people use real data, not form opinions based on hand-waving, or adjectives like "fast" imparted upon objects, without experimental backing.

I have not personally used DOA, but if I wasn't going to use dbExpress, that would be the next thing I would evaluate, for Oracle-only environments. nevertheless, I wouldn't assume that dbExpress is slower, without testing both in my own environment.

Updated: Originally I mentioned Delphi OCI, but it turns out it's out of date, and not very active. DelphiOCI project on sourceforge, works with Delphi 7 and before, not on unicode delphi, and only with older (Oracle 8i and older) oracle versions... If this was able to work in my environment, I'd have tried it. Oh, and it's GPL. Okay. Not so great.

Ardeliaardelis

I think I will set up a test. I looked at this devart.com/odac/performance.html for some performance details. – Goalie

Also check AnyDAC performance: da-soft.com/anydac/data-access-performance.html – Domenic

Warren, do you think that DelphiOCI is still an option even though there does not appear to be active dev on it since '09? – Goalie

@Ants: DelphiOCI doesn't support databases beyond 8i, Delphi beyond 7 and is under a GPL license, meaning your application must be released under the GPL as well. – Islamism

Thanks ldsandon. didn't notice it was not ported to Unicode. I might try porting it upwards it myself, some day, if I get bored. – Ardeliaardelis

@Warren: remember also what "GPL" means. Licenses are not just three letters acronyms attached to a project. They have a deep meaning, and may lead to legal issues. – Islamism

Answer 2

Both ODAC and DOA (although DOA development is pretty stale lateley) will give you access to Oracle features dbExpress and other generic DB access libraries will usually not allow access to. If your application does support Oracle only, and doesn't need to support other DBs, it's IMHO far better to exploit Oracle capabilites fully. The ease of port depends on how you wrote you BDE component code. ODAC has tools to simplify BDE porting (I never used them, though), while AFAIK DOA has not. DOA TOracleQuery is not a TDataset descendant, and thereby is not a direct TQuery replacement, although it has less overhead, and thereby has good performance, while TOracleDaset is the only dataset-like component. I would not use the ODAC direct TCP connection to Oracle without the Oracle client but for very special needs - it is an unsupported (by Oracle) connection, I'd prefer to use Oracle Instant Client if a small (relatively...) footprint is needed, at least it is a fully supported option and let you (or your customers) open support request with Oracle if needed.

Islamism

I use ODAC for several years and Im very satisfied with it. I especially like three moments: 1) good performance, much better than BDE 2) capability to connect in Direct mode without installing Oracle Client software 3) ODAC support most of the useful Oracle features, which BDE doesnt (for example, Direct Path Loading, DML arrays, XML, etc.) – Urfa

Interesting you mention the direct connection, I was thinking about using that +1 – Goalie

As already said, note that the direct connection may be unsupported by Oracle, unless ODAC has obtained the license and not only reverse-engineered it. Oracle support won't give support if a not supported client is used against a given database version. If that is an internal application is up to you, if you are selling it think about your customers' needs. Paying $$$ for Oracle support and then being unable to obtain it is not nice. – Islamism