Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

May 22, 2002

Multi Vendor SQL

I'm developing some software. "Gosh" I hear you say, "that is original for someone who claims to be a programmer". Well it is, but I am going to do it anyway.

I'm doing this for a number of reasons, not least of which is to stress the PythonCard prototype framework. Because of my previously expressed interest in storage (particular of the transparent nature) I'm also trying to write my code in a vendor-neutral fashion so that my application can work with a number of different databases. This is not as easy as it sounds.

For the time being I am limiting myself to Oracle, MySQL and Gadfly. Its the last one that will be the hardest work, as Gadfly only supports a limited subset of the functionality which you would expect from a fully fledged RDBMS. But I am getting ahead of myself here.

This isn't going to be a nice, perfectly worked out, tutorial. Rather this will be a series of posts sharing my successes and frustrations. I will point out interesting things I found out on my journey of adventure, and hopefully share some cool code with you as well.

Our first cab off the rank is delving into the differences between different dialects of SQL. Rather than work them out myself and print them here, its best to re-use. That article on www.oreilly.com is a treasure trove for cross database developers.

Faced with the choice of writing different back-end modules for each of the databases I wish to support or using a framework I have chosen to use a framework. I'm going to be using the zdc application, part of the weblib framework originally developed by Michal Wallace. I shall also be liberally borrowing from the Bulldozer framework currently under development by Patrick O'Brien. If you want a gentle introduction to the object-relational mapping framework, wander on by to webAppWorkshop and tell them that I sent you.

If you have recovered from the linkfest then consider our first problem, NULLs. I wrote my first bunch of code directly for Oracle, before getting bitten by the mutli platform bug. So here is a sample statement;

INSERT INTO stock_prices

  ( symbol, price_date, price)

VALUES

  ( '%s', nvl(to_date('%s', 'DD-MON-YYYY'), trunc(sysdate)), %0.3f)

Nice code, eh? Well it won't work in MySQL or Gadfly. Our first problem is the use of the NVL. This is a standard Oracle function meaning 'null values'. If the first argument (our passed in date value) is NULL it substitutes the second value (the current system date). NVL is great, it is part of one of the first rules every Oracle programmer learns; "Don't forget your NVLs"

Well, I can't use it. Nor for that matter can I use sysdate, to_date (Gadfly doesn't have a date data type) or the format mask 'DD-MON-YYYY'. I don't want to build my own date class, but I'm going to have to think about this one.

First though, I'm going to bash around with NULLs and NVL. Hopefully the next post will contain a nice simple answer. Then again ...

Posted by Andy Todd at May 22, 2002 10:50 AM

Advertisements