0

I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided).

It seems, that different DBMS use different quoting for table names.

For example, I have the following in MySQL:

SELECT * FROM `tablename`;

while it is

SELECT * FROM [tablename];

in MS SQL Server.

Is there an "uniform" quoting that all databases support? Or is there a jdbc method that gives me tells me the correct quoting?

  • Do NOT use names which: are listed in reserved words list of any DBMS, are too long for any DBMS, contains non-A symbols. I.e. may conflict with naming rules in any DBMS. And you do not need in quotation at all. – Akina Dec 19 '19 at 13:20
  • Are underscores ok? – dan1st Dec 19 '19 at 13:38
  • Letters (assuming case insensitivity, but single-cased, lowercase recommended - thanks to PostgreSQL), digits (not from beginning) and underscores. # and @ as special marks at the beginning (for temporary objects). – Akina Dec 19 '19 at 13:42
1

The SQL standard prescribes double quotes for quoted identifiers:

SELECT * FROM "tablename"

MySQL and SQL Server can be configured to comply with the standard, while other DBMSes comply out of the box. However, as others suggested in comments, you should avoid using quoted identifiers if possible, because it opens opportunities for bugs and unexpected behaviour in the SQL code.

| improve this answer | |
  • How can I do something like CREATE TABLE IF NOT EXISTS genericly? – dan1st Dec 19 '19 at 15:46
  • That's an entirely different question (to which the short answer is "not possible"). – mustaccio Dec 19 '19 at 15:52
  • I'll just let the CREATE TABLE command fail. Thanks – dan1st Dec 19 '19 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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