Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

So far I've always coded using raw/embedded, dialectic SQL. This seems obviously wrong to me because it ties you to concrete technologies so your software can't be deployed to any machine - especially servers.

In general (independently of the programming language and RDBMS), what options does one have in this case? How good are each one of them? What would you say is the standard approach?

share|improve this question
add comment

6 Answers

up vote 11 down vote accepted

Generally what you should be looking to do is abstract your database code into its own layer (assembly, dll or whatever).

In your application code you then call the interface which "behind the scenes" works out which database code you need and calls that to do the work. Depending on how your application is configured this could be either something decided at compile time, install time or run time.

A key technology to look up is Inversion of Control or Dependency Injection. This will allow you to exercise this control over your application and decide which version of the code you need.

share|improve this answer
1  
Regardless of the (actual) validity of what HLGEM has written, this is absolutely the way to go. It creates a clean layer of code that deals with persistence issues only. And if you should ever need to change your database (= persistence medium), then that is the only part of your application that needs to change. –  wolfgangsz Jul 26 '11 at 14:22
    
Cheers for the answer! I guess that building the dependency can be just as big as the consumer app itseld so one relies on already developed solutions right? e.g. Java's DAO –  vemv Jul 27 '11 at 2:58
add comment

ORM. http://en.wikipedia.org/wiki/Object-relational_mapping

Works really, really well for divorcing an application from SQL.

share|improve this answer
    
+1 - There are so many powerful and mature ORM solutions out there that nobody needs to write straight SQL anymore. Common examples (for .NET): NHibernate, ADO.NET Entity Framework and Entity Spaces. –  Scott Whitlock Jul 26 '11 at 13:32
    
Let me clarify by explaining that by "nobody", I'm really only talking about most application developers. Obviously developers of ORM frameworks, and people really looking to squeeze performance out of their programs will be coding in straight SQL from time to time. –  Scott Whitlock Jul 26 '11 at 14:47
    
Or is ORM an anti-pattern? seldo.com/weblog/2011/06/15/orm_is_an_antipattern –  FrustratedWithFormsDesigner Jul 26 '11 at 15:54
2  
@FrustratedWithFormsDesigner: "Objects are not an adequate way of expressing the results of relational queries." Makes little sense to me. Yes, there's an Object-Relational Impedance mismatch. But it's not a fatal flaw. It's little more than a quirk. Unless, of course, you design a database to abuse the ORM layer through excessive focus on 3NF. –  S.Lott Jul 26 '11 at 15:58
add comment

(This answer has to do with the SQL statements themselves, and not with how they're deployed.)

I don't think I'd call it standard, but the most common approach I've seen is to use the "lowest common denominator" subset of SQL features that all your target platforms support. For example, if one of your target platforms doesn't support common table expressions, then you never use common table expressions.

That approach trades off some SQL flexibility and some performance for deployment flexibility.

Some SQL variations can be masked by macro substitution at build time. A macro might expand to a DATE_ADD() function on one platform, and to a datetime value + an interval value on another. This can get you closer to optimal performance on all your target platforms for a slightly larger subset of SQL language features, but it can be a real pain to maintain.

The brute-force approach is to maintain a set of platform-specific differences for all your SQL statements. Simple statements like SELECT * FROM TABLENAME can be shared on all platforms; more complex statements that leverage platform-specific features or syntax have different versions for each platform. In my experience, this approach requires a lot of automated testing to make sure all platforms behave the same.

I haven't thought much about it, but I think that the more platform-agnostic you want to be, the more likely you are to end up near the brute-force approach. How hard is that? Well, the most likely candidates are database CASE tools and database schema utilities, and there aren't very many of them that target more than a handful of current platforms. I reckon it's pretty danged hard.

share|improve this answer
add comment

Frankly as a database person, using the specific flavor of code for your database backend is the most efficient code you can write. In my 30 years of working with databases, we have never changed the database backend for an application and the only situations where it seems necessary are when you start with a poor choice to begin with (such as Access when you need a large database) or if you sell Commercial off-the-Shelf (COTS) software that needs to support whatever backend the client has. Sacrificing performance today in the idea that someday you might need to change backends is not a good way to go. There is a lot more to changing the backend of a database after it has many records than just changing the application code and the smart dba prefers not to take the risk.

share|improve this answer
3  
I totally agree. I have never seen a company change their database either. I've worked on many business softwares for which performance did matter - and thus had to sacrifice orthogonality often times. However, I've had also projects for which database-independence was a requirement and used mostly ORM for that. Database indepencende is totally overrated. –  Falcon Jul 26 '11 at 14:03
    
You can still design an application so that it theoretically could support multiple databases, without much additional effort - even if it doesn't support them all off the shelf. In other words, you might need to hire an Oracle expert before attempting to add Oracle support, but it's better than having to rewrite half the app because you didn't bother with any abstraction techniques. It's also not just COTS; mergers and acquisitions will also often necessitate a platform change in order to save on (often huge) licensing costs. –  Aaronaught Jul 26 '11 at 14:29
add comment

Others have suggested abstracting out the database layer, this is fine, but in the end you will have different database code for different database platforms, especially if the database code is using specific database platform technologies. This can be maintainable, but if the application has 400 tables and 1000 stored procs on Oracle and you need to port that SQL Server, its a big mountain to climb.

An alternative approach is to write one SQL statement that will run on all the DB platforms. SQL itself is standardized (sq-89, sql-92, sql3) etc. If all the platforms support sql-92, make sure the data, queries, and updates use sql-92 compliant code. Then the abstracted data layer runs one set of queries, regardless of DB platform.

If doing this approach, some sacrifices will have to be made and you may have more application code to support the data layer. Some databases don't support stored procedures etc. but the sql that is sent in and out should be universally accepted, then it is cross DB.

share|improve this answer
add comment

I've worked on a large application that switched databases (from SQL Server to MySQL). There's a lot I learned in that time. I think that if you want to develop a cross database application you have to have a few things in mind. First, have a list of supported databases. You won't have the resources to develop and test against all databases. Second, either use an ORM that supports all the databases you are targeting, or (and this might be preferrable) stick to basic SQL that works across all your targeted databases. Only use different SQL for each database if you have a performance issue. Depending on the application, you can get pretty far with "standard" Select, Join, Insert, Update, and Delete statements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.