Best practices to support multi db

[aandrulis]

Hi,

I started using MyBatis to try and write a application with multi db support.

Does a best practice guidelines document exist? 

For example what's the standard to support functions like the ones below without writing individual queries for each db engine?

concat: ‘A’ || ‘B’ or concat(‘A’,’B’)

nvl: NVL(col,’XXX’) or coalesce(col,’XXX’)

decode: 

     decode(col,’A,’a’,C’,’c’,’z’)  or case col when ’A’ then ‘a’ when ‘B’ then ‘b’ else ‘z’ end

Thanks in advance.

Regards,

Adriano

[Guy Rouillier]

I work regularly with both Oracle and PostgreSQL, so I recognize the
Oracle-isms in your examples. Since Oracle works perfectly well with
standards-based approaches, I'd highly suggest you avoid the proprietary
versions when possible. So, use coalesce instead of nvl, case instead
of decode, etc.

I had to convert a large application from Oracle to PostgreSQL. I
assumed I'd have to have a large number of code sections that used
conditionals: if (Oracle) do this, else do that. However, I was
pleasantly surprised to see that after I replaced all the proprietary
functions you show above with the standard alternatives, I had no
conditionals at all!

--
Guy Rouillier

[Adriano Andrulis]

Yes, there's oracle and informix and the rest. :)

Thanks a lot, that confirms out approach is a good one. Where we couldn't right ansi we're trying to remove the if's to sql snippets to be used in includes. It takes a bit the sql immediate readability but it seems to be more human readable.

Regards,

Adriano

--

		Adriano Andrulis
		Senior Software Engineer
		T: +44 208 742 1600 +44 203 249 8241 E: adriano....@openbet.com W: www.openbet.com	OpenBet Chiswick Park Building 9 566 Chiswick High Rd London W4 5XT

This message is confidential and intended only for the addressee. If you have received this message in error, please immediately notify the postm...@openbet.com and delete it from your system as well as any copies. The content of e-mails as well as traffic data may be monitored by OpenBet for employment and security purposes. To protect the environment please do not print this e-mail unless necessary. OpenBet Technologies Limited. Registered Office: Chiswick Park Building 9, 566 Chiswick High Road, London, W4 5XT, United Kingdom. A company registered in England and Wales. Registered no. 6712030. VAT no. GB927523623