4

I'm currently working on a project for a web application that may be installed on several different servers with various software configurations. I want to make my application as flexible as possible by allowing the user to have various SQL servers installed. The problem is the SQL syntax used by any two server vendors does not match up. For a simple example, here is the same SELECT statement for MS SQL and MySQL:

MS SQL - SELECT TOP 1 * FROM MyTable ORDER BY DateCreated DESC

MySQL - SELECT * FROM MyTable ORDER BY DateCreated DESC LIMIT 1

Are there any standard way to abstract the statement creation for various vendors? Any online resources or books discussing this problem? Any hints or smart-alec remarks that I'd find useful?

Further information: I'm writing my we application in vanilla ASP running on a Windows server.

Thanks, Spara

Sparafusile
  • 4,696
  • 7
  • 34
  • 57
  • 3
    *Why* are you writing a new web application in a defunct and totally deprecated and unsupported scripting language? – Dan Diplo Apr 15 '10 at 18:26
  • For the same reason I still code in pure assembly - nobody can answer the question of "why not?". How about 1) I have a huge code base to work from in ASP 2) I only need notepad to update/correct/change an ASP application 3) ASP is lightweight and less sluggish 4) Fewer hosting requirements for ASP vs ASP.NET 5) Because I want to... just because a language has been replaced doesn't mean it's not still useful. – Sparafusile Apr 15 '10 at 18:34
  • I've never heard of ASP being "less sluggish" than .NET. In fact, all of your reasons seem bogus and make you sound like your clinging for dear life onto what is now ancient tech. – Corey D Apr 16 '10 at 01:17
  • IMO, it *much harder* to do a site *right* in ASP classic. Logging, exception handling, url rewriting, inheritance and the list goes on. To actually get the equivalent functionality of an ASP.NET site in ASP Classic requires orders of magnitude more code and far more developer discipline than it does in ASP.NET. – Thomas Apr 16 '10 at 01:56
  • Classic ASP has the same functionality as PHP which is still used quite often. Fortunately this was a question about vendor neutrality and not what language to use. Since I'm the only programmer on this project I'm safe to choose any language I want. Because of the reasons I stated above I choose classic ASP. – Sparafusile Apr 16 '10 at 11:12
  • @Sparafusile - I disagree that PHP and ASP Classic are still equivalent. At one point long ago, they were equivalent and they are both scripting languages but that is where the similarities end. PHP now has wide array of object oriented features and exception handling which ASP Classic does not. – Thomas Apr 16 '10 at 14:46

3 Answers3

4

You can conform to ANSI SQL 92. All major RDBMS I know will support that.

However, there are tons of things individual RDBMS makers have added to enhance their own flavor of SQL. That is where you get into a lurch.

You may have to branch out in code depending on the RDBMS you are connecting to and generate / choose the appropriate SQL statement at that point.

A better option would be to create a DAL for each supported RDBMS. Implement a DAL interface across the DALs to make them uniform. This should be easier than switching in code.

I suggest that instead of trying to please everybody, you should write your code such that you support the top one or two systems that you expect to deploy on, and add support for other RDBMS as and when required.

Carl Manaster
  • 39,912
  • 17
  • 102
  • 155
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • 3
    The SQL 92 standard had multiple levels and they all complied with the 'entry level' only - making it of limited value. – Andrew Apr 15 '10 at 14:47
  • Using the standard syntax as much as possible is certainly a good start. But it utterly fails on the simple example I gave above (TOP vs LIMIT). Regarding your branching and adding vendors at a later date - that seems like an insane amount of work. Making a branch for each statement, then going back and updating them later sounds tedious. I start thinking about storing statements somewhere and retrieving the correct one when needed... but I think I read about that on thedailywtf.com at one point. – Sparafusile Apr 15 '10 at 16:20
  • @Sparafusile - Frankly, this IS the only sane way to do it. Whether you build your DALs with an ORM or not. Further, doing this in ASP classic will be **significantly** more work than doing it in .NET where you could create a base class with functionality that worked across all supported database product versions and derived classes for situations where you needed product version specific functionality. – Thomas Apr 16 '10 at 01:52
2

I suggest you use an ORM (linq, nhibernate etc) to abstract the SQL dialect away rather than trying to write plain vanilla SQL.

Edit: Is there an OR/M for Classic ASP?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

You know, I bet you could get by with strictly ansi sql, it will just take some effort and probably extra work. i.e.

SELECT MAX(*) FROM mytable ORDER BY datecreated DESC;

There will be workarounds in ansi because really all of the db specific constructs are ways to shorten and or shortcut existing ways of getting at or describing data. Another option might be to restrict access to the various databases to stored procs and user-defined functions. That way, you could write scripts for a bunch of the dbs you know will be used with the requirement that your db specific script be run before the app will work. Just an idea.

Sean
  • 129
  • 1
  • 8
  • 1
    That's not a valid MS SQL statement. I didn't check MySQL. – Sparafusile Apr 16 '10 at 11:14
  • That was just off the top of my head but if you want a single row response, there are multiple ways of doing it. I still think that with some digging and work arounds, you should be able to code most anything you may need in vanilla flavored ansi sql. – Sean Apr 16 '10 at 15:18