Re: SQL -> Oracle

Marcelo

I faced that bridge a while back and did the following

 1- went to SQL-Direct (www.sql-direct.com)
     - fast (uses native links)
     - multi-database
     - works in Delphi Pro
     - not too far from BDE (has tsdQuery replace tquery)

2- extracted all my SQL queries in one single file (that was the tedious part). So when I build, I build with one platform in mind so I include the correct version of the SQL.inc. Adding a new platform is merely translating this one single file into the specific SQL jargon of the target db. with modern DB engines the syntax and capabilities are now very close (especially Oracle and Firebird) so the task at hand it not too enormous.

3- now whenever I need new query. I drop the proper component, write the query, test it as much as I want, when I am happy I copy it as a constant in the big SQL file and voila! if I build for a platform for which I forgot to translate a query, I get a nice (!) compile-time error message.

No ifs in the source code, Exe is smaller and faster as it does not carry any extra baggage.

The main diffence you're going to have is with Stored procedures

- Sybase and SQL server need a Getresults call to get the return code of a stored procedure
- Sybase and SQL server prefix their parameters with '@'
- Firebird and Oracle have selectable stored procedures so you use them as tables in a query not in a SP component
- Oracle has sequences whilst Sybase (or MS) don't AFAIK but

these are just implementation details, which need considering

All the above, with a little care can probably be encapsulated in a derived class by I have not (yet) had to do that (laziness ?) so I have a few ifs.

ha, last point. instead of dropping straigth a tsdquery or a tsdStoredproc, derive them immediatly into an object tMarceloQuery and tMarceloSP. So if you encounter a pb later on (like Selectable Sps need a tquery not a tStoredproc), you will just have to put some flesh into your derived components and not worry about have to trawl through 300 units to change object classes.

hope this helps

Didier

Marcelo [RM Sistemas] wrote: 
    I reaaly would you to comment about the situation below described:

I have a Delphi project with more than 300 units, accessing SQL Server with BDE (using regular TQuery and TDatabase).

We here, don´t want to be limited to only SQL Server customers, having also Oracle customers. And, once we will be already doing a big
work on updating (some functions wich works fine at SQL, don´t at Oracle) , we are considering to forget about BDE, and use something else, like ADO.

    Questions:

- Considering costs/benefits, wich solution should i implement ? I really don´t want to update my system so bad, and after that have this system turned to
something else, but in the other hand, i really want to forget about BDE.

- Is there a component, to replace the TQuery component, wich automatically converts query texts from SQL to Oracle ? Or i will have to write a lot of IFs, in order to
use one query text or another, depending if the connection is to a SQL Server or Oracle.

- All of our future Oracle customers, will use Oracle clients, so that´s not a problem.

    - Any other solution to have this done ?


    Thanks in advance



.

Relevant Pages

  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: IOT, memory and transaction time
    ... What is a better way of structuring this SQL for Oracle? ... Oracle expertise available at the moment but certainly SQL Server ... Server uses dynamic memory allocation whereas Oracle, in our tests, ...
    (comp.databases.oracle.misc)