Re: Supporting Oracle and MSSQL simultaneously

M.Samson wrote in message <3519CDEE.C62_at_azstarnet.com>...
>Mark Michaelis wrote:
>>
>> Hi,
>>
>> I have a software product that currently uses MSSQL Server. We are
>> trying to port it to use Oracle as well. My goal is to have one
>> source for both databases. The problem is I have stored procedures
>> and user defined triggers. The syntax between Oracle and MSSQL stored
>> procedures is significantly different as follows:
>...(snipped)
>
>This is an interesting topic that comes up time to time. If you are
>trying to support multiple databases you can either
>implement all business rules in the middle tier of a multi-tier
>implementation
>or
>maintain separate sets of codes for each target database.
>In first method you are not using the target database to its full
>capabilities,
>and in the second method you spend fair amount of resources to keep the
>codes
>in sync.
>My personel preference is to maintain multiple sets of codes that take
>full
>advantage of the target database capabilities.

Indeed, we support SQL Server, Oracle, and (gasp!) Access from more a less a single code base which we compile according to the intended target platform.

We observe the following major differences between the platforms:

1. The syntax for doing LEFT OUTER JOINs is dramatically different between SQL Server and Oracle. (For our purposes, SQL Server and Access will support the same SQL syntax)
2. In SQL Server, it is not possible to determine the value of an IDENTITY column in a row to be inserted until after the row is actually inserted. Oracle sequences are somewhat easier to use in this respect. Access allows the next value to be obtained directly from the recordset.
3. SQL Server and Oracle are much more flexible in terms of operations permitted during transactions. Access will implicitly commit a pending transaction if a cursor is opened before the transaction is committed, which is a *royal* pain.
4. Oracle does not support row versioning that I am aware of, and thus we values concurrency to detect conflicting updates. SQL Server has timestamp columns. In Access we use pessimistic locking since the concurrency requirements are much less.
5. We use ODBC to interface with SQL Server and Oracle, DAO to interface with Access.

>Hope this helps,
>--
>-M. Samson Dunatunga, Ph.D. Systems Engineering
>http://www.tucson.com/objectware e-mail: msamson@azstarnet.com
>database design, reporting, query, demo tools and free software

--
Randy Baker (remove Z from address in email replies)