Which database extension? PDO or DB or ODBC?

[ImOk]

I need to program generically. I am familiar with the ODBC layer but
never used PDO or DB.

Does anyone have any experience with these database layers?

Thanks

[Manuel Lemos]

Re: Which database extension? PDO or DB or ODBC?

Hello,

on 04/30/2006 04:20 PM ImOk said the following:[color=blue]
> I need to program generically. I am familiar with the ODBC layer but
> never used PDO or DB.
>
> Does anyone have any experience with these database layers?[/color]

None of them provides database independence. If you want to switch
databases later, you need to rewrite part of your code.

Personally I use Metabase, which provides database independence not only
at the access level, but also at the database schema installation level.

http://www.phpclasses.org/metabase

There is also PEAR::MDB2 is a follow-up of PEAR::MDB that was derived
from Metabase .


--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator

http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP

http://www.phpclasses.org/

[Mladen Gogala]

Re: Which database extension? PDO or DB or ODBC?

On Sun, 30 Apr 2006 20:20:33 -0300, Manuel Lemos wrote:
[color=blue]
> None of them provides database independence.[/color]

Actually, nothing provides database independence. Database independence is
a myth. Databases have different locking strategies, deal with connections
in different ways, optimizers are vastly different and will produce
different execution plans for the same query, on the same underlying
physical strategies, some support things like local temporary tables, some
call them cursors, some have bi-directional cursors, some do not, the
procedural extensions differ from database to database, as do limitations
of what you can do with triggers, in some cases readers block writers, in
others do not, some databases support bitmap indexes, some do not etc,
etc, etc. The term "relational database" practically means "supports SQL
as defined in ANSI 92 standard". That's it. Databases are so different
beasts that there is no way you can write a "database independent
application". I saw several attempts to do so, by using Java and object
relational mappers. General characteristics of such an application
are:
1) It tries to enforce business rules in the application and tries
to disregard stored procedures and triggers of the underlying
database. It's special fun when somebody else develops another
application using the same data and understands the business rules in
a different way. Logical corruption follows.
2) It tries to create its own security mechanism, based on the application
server and LDAP server. Not only does it duplicate the database
mechanism, it's much more easily hacked as it is one layer closer to
firewall then the database.
3) It resolves concurrency problem by creating "services" which are
essentially queues which perform certain type of transaction. This,
usually, creates a bottleneck, so the whole thing is "scaled" by
distributing the load over several servers. Now, we have deadlock
problems, which are resolved by creating new "services". The whole
vicious circle starts anew.

In other words, the only database independent applications that I've ever
seen suck equally on all underlying database. I do have considerable
database experience. I am Oracle DBA for 17 years, I have experience with
CICS DL/I, DEC RDB, PostgresSQL and SQL Server 6.5. Trust me, I've seen
many amazing things in this world, but I haven't seen such a creature as
"database independent application". If it exists, it must have been
written by a Sasqwatch or Nessie, because I haven't seen it so far.

--

http://www.mgogala.com

[ImOk]

Re: Which database extension? PDO or DB or ODBC?

Mladen,

I agree about your assesment about the myth of database independence
and each DB engine has its own specialties.

My requirements are simple:

- Use SQL Server or MySQL
- No triggers (will be done in code)
- Minimal in application engine configuration (will be done in
configuration files).
- No relational management (will be done in code).
- Transactions/Commit/Rollback.
- Portability to Linux (MySQL).
- Simple datatypes.

I am just looking for people's experiences with these 2 extensions as
far as performance.
What would you say is the best extension to use between DB or PDO given
my simple requirements?

Thanks

[Jerry Stuckle]

Re: Which database extension? PDO or DB or ODBC?

ImOk wrote:[color=blue]
> Mladen,
>
> I agree about your assesment about the myth of database independence
> and each DB engine has its own specialties.
>
> My requirements are simple:
>
> - Use SQL Server or MySQL
> - No triggers (will be done in code)
> - Minimal in application engine configuration (will be done in
> configuration files).
> - No relational management (will be done in code).
> - Transactions/Commit/Rollback.
> - Portability to Linux (MySQL).
> - Simple datatypes.
>
> I am just looking for people's experiences with these 2 extensions as
> far as performance.
> What would you say is the best extension to use between DB or PDO given
> my simple requirements?
>
> Thanks
>[/color]

I don't have any experience with these extensions. My philosophy is a bit
different.

I'll build layers of classes. The database layer contains the database-specific
calls (i.e. mysql_query). The next layer is the tables, which are derived from
the database layer. Simple one-table SQL statements go in here. On top of that
are the business objects, which communicate with the one or more table objects
to communicate between the tables and my program. I'll have multi-table SQL
statements here.

If I need to change databases, I just need to change the top layer - one class.
If I need to change the table layout, I have to change the table layer, and
probably some in the business object layer.

But any changes in the database are completely isolated from the web pages
themselves.

Simple queries result in relatively simple classes. I admit there are problems
when I get more complicated queries, but I'd have the same complications anyway.
This way I keep everything outside the web page code. And changes are limited
to a few classes instead of dozens or potentially hundreds of pages.

I admit this would be a lot of extra work for a small site. There you could
combine the table and business layer objects. But you still have database
independence and no SQL or other database specific code in your app.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===