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?
Developing cross-DB applications
database;cross platform
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.