Typically, you would use some kind of abstraction layer between DBI and
the application that takes care of the various SQL dialects. There are
several such layers on CPAN.
A very minimal layer could be implemented by using a "phrase book"
containing each SQL statement for each supported database.
But there are still some other traps beside the SQL differences:
* MySQL often behaves very differently from other databases (see
<http://sql-info.de/mysql/gotchas.html>),
* MS SQL Server can handle only one active statement per connection
(unless you play some dirty tricks)
* SQLite has a very different concept of data types (types bound to
values, not to columns, see <http://www.sqlite.org/datatype3.html>)
* DBD::CSV has no data types at all, and some issues with NULL
* DBD::Chart does not even talk to any kind of database ...
* Trigger support (before and after, only after, or completely missing)
* Stored procedures
* Data types
Alexander
On 21.08.2009 19:29, E R wrote:
Hi,
I was wondering if anyone has written a DBI proxy driver which
modifies the SQL before passing it to the real database driver.
Conceptually it seems pretty straight forward, but I was wondering if
there are any gotchas I need to watch out for.
To elaborate on how I intend to use it:
I am considering using this to connect an application which uses one
flavor of SQL (e.g. Oracle) to another database (e.g. mysql).
The SQL transformations would not be very sophisticated - it would be
on a query by query basis rather than try to be a generic query
translator. That is, as I encounter queries that will not work, I will
write code in the proxy to replace them with an alternative query.
Has anyone done anything like this before? And if so, any suggestions
on how to proceed?
Thanks,
ER
--
Alexander Foken
mailto:[email protected] http://www.foken.de/alexander/
