0

Say I have a table call ‘users’ with a column ‘order’ and I wish my application code to work across all the database systems out there.

Assume that it is impractical to change the database schema. Even if the schema was change, one of the main database engine will come up with yet another reserved word, so stopping the app working when a database engine is updated.

I would rather not have to process the SQL strings to convert them into the correct form for each database.

'How to find if a column name is a reserved keyword across various databases' partly overlaps with this question.

('Syntax error due to using a reserved word as a table or column name in MySQL' is the reference question for MySQL. )

Community
  • 1
  • 1
Ian Ringrose
  • 51,220
  • 55
  • 213
  • 317
  • No database system that I know of forbids arbitrary table name for a developer smart enough to read the documentation. You normally quote them out - i.e. SELECT * From [Users] in SQL Server - note the []. Similar exists in any other db that I Know of. As such, the code is broken as crappy sql to start with. – TomTom May 08 '14 at 12:29
  • @TomTom, So how do I write the SQL so it works on ALL the database engines, having different SQL for each engine in the app is not a good ideal. – Ian Ringrose May 08 '14 at 12:31
  • 1
    Write your schema in swahili – Strawberry May 08 '14 at 12:31
  • YOu do not. You write a sql code generator much like LINQ and then have a provider generate SQL. Simple like that. Having different SQL for different engine is reality - regardless whether you like it. SQL dialects differ in a LOT more than just reserved keywords and if that is your only problem you have seriously not written anything real yet. Join syntax is different, paging is different. Keywords are just the start. – TomTom May 08 '14 at 12:38
  • @TomTom, I have written a lot of SQL over the years, and have found that at least between SQLServer and Oracle well over 90% of the SQL can be the same. – Ian Ringrose May 08 '14 at 12:41
  • 3
    ISO standard says double quotes. Good luck that everyone has implemented it or that it's the default setting on every DBMS. – Marcus Adams May 08 '14 at 12:42
  • possible duplicate of [What does the SQL Standard say about usage of backtick(\`)?](http://stackoverflow.com/questions/10573922/what-does-the-sql-standard-say-about-usage-of-backtick) – Marcus Adams May 08 '14 at 12:43
  • 2
    @IanRingrose Then you write simple beginner SQL and / or have no complex means because I found that the other 10% make like 50% of my work. – TomTom May 08 '14 at 12:45

1 Answers1

3

You have to test on everything you are going to support, so "all the database systems out there" is not in realty an option.

To be more realistic, you can decide which DBMS you want to support, and write a subset of SQL which they are all happy with, and test them with automated tests.

For example, you might consider:

  • MySQL
  • Postgres
  • MS SQL Server
  • Oracle

I believe these all support ANSI double-quotes for quoting names, so if you quote all names all the time then you have one less thing to worry about.

Ben
  • 34,935
  • 6
  • 74
  • 113