>> When I started programming about 15 years ago, the conventional wisdom
>> was to use middleware that would let you connect to different
>> databases without changing your code - ODBC comes to mind. Nowadays,
>IMO, ODBC is still the way to go. But I don't use Delphi very often
>(I'm a C++ guy). If for no other reason, you have a decent chance of
>being able to port your applications to other platforms besides Windows
>(e.g. Mac OSX) with minimal effort. Every major RDBMS vendor has ODBC
>drivers for multiple platforms.
I agree with ODBC. Some regard ODBC (Open DataBase Connectivity) as a
Microsoft technology, but are usually unaware that ODBC is actually an
implementation of a standards-based technology: X-Open CLI (Call Level
Interface).
>ODBC/ADO/etc. introduces a few layers of management between your app.
>and the raw APIs. For the most part, this is fine. But it really
>depends on what you plan on doing.
I would qualify that by mentioning that what you say was largely the
case many years ago when ODBC was first introduced but for the
most part is no longer the case.
ODBC because so popular as an interface to RDBMS backends that
vendors rushed to write ODBC drivers. The simplest and quickest
way to write an ODBC driver is to write it as a translator between the
ODBC API and the native API of the backend. Today, a great many
RDBMS vendors have "single tier" ODBC drivers. One that we use a
great deal is Microsoft SQL Server, which has had a single tier
ODBC driver ever since version 6.5. I attended a Microsoft developer
conference where Microsoft advised developers to write to ODBC
instead of DB-LIB (the old native SQL Server API) for performance
reasons. They claimed that their single tier ODBC driver would
perform "at least" as well as DB-LIB, and in some cases would
perform better. We have done extensive benchmarking that proves
this to be true.
There is another aspect to ODBC many do not know about; that is
ODBC escapes. There are a number of things about the SQL
language that are non-standard across RDBMS platforms. For
example, dates. While most high-end backends automatically
convert string literals to dates, a lot do not. Some require special
syntax to delimit dates. For example, MS Access requires that
you delimit literal dates with pound signs (#). In ODBC, you can
do this:
SELECT * FROM MyTable
WHERE MyDateColumn >= {d '2007-01-01'}
The curly braces ({}) are used to delimit an ODBC escape sequence.
The driver manufacture is required to handle these escapes in a
consistent way. So, when you use ODBC, you not only are able
to _connect_ to any RDBMS with an ODBC driver, you may very
well be able to use ODBC escapes to make your SQL code portable
as well.
Glenn Lawler
www.incodesystems.com
-----Original Message-----
From: Thomas Hruska [SMTP:[EMAIL PROTECTED]
Sent: Tuesday, May 01, 2007 12:25 PM
To: [email protected]
Subject: Re: [delphi-en] Database Connectivity
Jamie L. Mitchell wrote:
> When I started programming about 15 years ago, the conventional wisdom
> was to use middleware that would let you connect to different
> databases without changing your code - ODBC comes to mind. Nowadays,
> ADO seems to be the answer. However, is the conventional wisdom still
> the way to go?
>
> My application is currently scheduled to work with MS Access type MDB
> files and MS SQL Server. Therefore, I have been developing it using
> ADO; that means, in Delphi 2005, using the dbGo components.
>
> I am wondering if there is a better solution out there? I am thinking
> that I might like to move to Oracle or SQL Anywhere, or any other DBMS
> that might be in use. Is ADO my best solution? I don't mind minor
> mods to the source code but I really don't want completely separate
> applications.
>
> I know this might be a religious question...I am not trying to bring
> on a war, just wondering what you out there think on the subject.
>
> Thanks for playing...
> jamie
IMO, ODBC is still the way to go. But I don't use Delphi very often
(I'm a C++ guy). If for no other reason, you have a decent chance of
being able to port your applications to other platforms besides Windows
(e.g. Mac OSX) with minimal effort. Every major RDBMS vendor has ODBC
drivers for multiple platforms.
It also depends on the application. If you need performance, nothing is
going to be faster than the database's own raw API interface. The
downside is that it ties your application to a single RDBMS. The upside
is you can run a few hundred-thousand queries in a short amount of time.
ODBC/ADO/etc. introduces a few layers of management between your app.
and the raw APIs. For the most part, this is fine. But it really
depends on what you plan on doing.
--
Thomas Hruska
CubicleSoft President
Ph: 517-803-4197
