May 21, 2010 at 4:33 am
[font="Verdana"]Is this the design approach? By which i can get rid of the vendor specific versions/syntax of SQL. And to code once, and run for all vendor specific versions.
This would help me minimize the time and effort needed to port to and from any RDBMS.
And eventually to get a guru as "A Portable SQL", but practical?
[/font]
May 24, 2010 at 6:04 am
It really depends on the situation, but for most of us, no, this is a waste of time & effort. I've been working for a company for almost nine years. We've been building everything on SQL Server. There has been no shifts away from SQL Server at all. So if we had been building completely generic ANSI Standard only SQL, we'd have been hurting ourselves. There are lots of great functions built into T-SQL that aren't ANSI compliant. Taking advantage of those, in our situation, is a good thing.
There are only a few places where people are shifting platforms a lot. Usually we're talking about vendors writing software that can be run on multiple platforms, but even there, there are only a few of them. The vast majority of work is done on a single platform and left on that platform for years at a time. When rewrites occur, every 5-10 years, you can adjust to a new platform if needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2010 at 6:45 am
I'm with Grant on this one but I'll go even further. My feeling is that truly portable code is a myth... so why bother? Further, in order to be truly portable, you have to NOT use a lot of the incredibly useful extensions that SQL Server, Oracle, MySQL, etc, etc, each contain. It's like having an incredible scientific calculator with a graphic display and only using the 4 basic math functions because someone else might not have a similar calculator.
Unless the code is just basic interface C.R.U.D. (Create, Retrieve, Update, Delete), I won't even try to make portable code. Even then, C.R.U.D. code can't actually be made 100% portable because even things like how to name a variable or how to return a result set vary quite a bit especially when it comes to SQL Server and Oracle. That's why there's things like (gasp), NHibernate and Hibernate.
As a side bar, I don't know why everyone thinks that databases and the languages that use them have to be homogenous... the languages used to write GUI's aren't. Do you write "portable" code in C# so it can be used in Java or vice versa? Not on your life. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2010 at 6:59 am
I have worked for a sofware company focused on cross-platform ERP solutions and I think I can throw in my 2 cents.
Writing ANSI SQL is "per se" a pain, but writing ANSI SQL that really works on every platform is utopia. You can write very simple "SELECT .. FROM WHERE .." queries and reasonably expect them to work, but, in my experience, forget about anything more complex than that.
We only supported Informix, Oracle and SQL Server and it still was a pain. Take into account that every version of the same product has a different ANSI SQL support, so that queries that work perfectly on Informix IDS9 don't work on SE.
There was a sort of "smart compiler" that could rewrite some queries into the appropriate syntax, but it still couldn't catch everything.
If cross-platform or cross-database means "backward compatible", it also means extremely simple queries.
Nowadays db engines are more ANSI compliant, but ten years ago everyone wanted to speak a different dialect and still today not everybody is convinced this is not a good idea.
As I promised, just my two cents.
-- Gianluca Sartori
May 24, 2010 at 7:51 am
Just adding another thought...
Most app developers that need to support multiple platforms are taking advantage of ORM tools like Hibernate, nHibernate, LINQ, Entity Framework, etc., to perform the work for them. Instead of trying to write TSQL, they generate it.
I'm not going to argue that the code generated is worth a plug nickel, but that's how it's being done instead of trying to shoot the TSQL in the head.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2010 at 11:33 pm
[font="Verdana"]
Gianluca Sartori (5/24/2010)
I have worked for a sofware company .......As I promised, just my two cents.
I haven't thought that before about the worth of two cents. :w00t:
Thank you.[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply