[Qt-interest] Database agnostic programming

[André Somers]

Hi,

"Robert Hairgrove" <evorgriahr at hispeed.com> schreef in bericht 
news:gqfji1$3m4$1 at eple.troll.no...
> André Somers wrote:
>> I have build some Qt based software that interfaces with a database. Now, 
>> we would like to make the program database agnostic. (...)
>>
>> How do other people solve this issue? Is there some library available 
>> somewhere that can "translate" SQL dialects? One option I am looking into 
>> is KexiDB, but the page seems a bit outdated so I am not sure what the 
>> current status is. Meanwhile, other suggestions are very welcome!

> While Qt provides a nice abstract interface to different databases, what 
> it cannot do is provide an abstraction for the SQL language itself.
>
> However, if you don't let users enter ad-hoc queries and have a small list 
> of predictable differences (such as TOP vs. LIMIT vs. ROWNUM), you could 
> write your own abstract classes which provide this functionality depending 
> on which database is currently running as back end. With Qt, you can 
> always tell by calling the QSqlDatabase::driverName() member function. The 
> list of DBMS's you would be expected to support would probably be less 
> than 10 at the moment and wouldn't change very often; any new databases 
> could be supported by deriving another class from the abstract base class.
>
> For example, the base class would have a pure virtual member function:
>
>    virtual QString setLimit(const QString &sql, unsigned int lim=0, 
> unsigned int start=0) = 0;
>
> In the derived classes, you'd override this to modify the SQL string with 
> the appropriate syntax. E.g. for MS-Access, the SQL string would be 
> modified by inserting "TOP " and whatever "lim" is (if greater than 0). 
> For MySQL, it would append a "LIMIT <start,lim>". For Oracle, it would add 
> "ROWNUM < lim" to the WHERE clause, etc.

Thanks, but I fear that is not really an option for most of my applications. 
I was hoping other's had already solved this and had a solution online 
somewhere. I fear it might be more work than you make it sound to get it 
right.

> It's always a good idea to stick to ANSI-SQL for this kind of application. 
> You might be interested in Joe Celko's books, for example "SQL For 
> Smarties" (I have the 2nd edition, ISBN 1-55860-576-2) which provide pure 
> ANSI/ISO-SQL solutions for such things including sequences, TOP() 
> problems, etc.
Thanks for that tip!

André