2

I'm fixing a bug in a proprietary piece of software, where I have some kind of JDBC Connection (pooled or not, wrapped or not,...). I need to detect if it is a MySQL connection or not. All I can use is an SQL query.

What would be an SQL query that succeeds on MySQL each and every time (MySQL 5 and higher is enough) and fails (Syntax error) on every other database?

escitalopram
  • 3,750
  • 2
  • 23
  • 24
  • 1
    There's got to be a better way to figure that out. – Mike G Oct 01 '13 at 15:20
  • Maybe this? http://stackoverflow.com/questions/254213/how-to-determine-database-type-for-a-given-jdbc-connection – Mike G Oct 01 '13 at 15:21
  • why do you want the query to fail to detect if it is MySQL? can't you use `SHOW VARIABLES LIKE "%version%";` or check if `version_comment` contains string `MySQL`? – bansi Oct 01 '13 at 15:21

3 Answers3

4

The preferred way, using JDBC Metadata...

If you have access to a JDBC Connection, you can retrieve the vendor of database server fairly easily without going through an SQL query.

Simply check the connection metadata:

string dbType = connection.getMetaData().getDatabaseProductName();

This will should give you a string that beings with "MySQL" if the database is in fact MySQL (the string can differ between the community and enterprise edition).

If your bug is caused by the lack of support for one particular type of statement which so happens that MySQL doesn't support, you really should in fact rely on the appropriate metadata method to verify support for that particular feature instead of hard coding a workaround specifically for MySQL. There are other MySQL-like databases out there (MariaDB for example).

If you really must pass through an SQL query, you can retrieve the same string using this query:

SELECT @@version_comment as 'DatabaseProductName';

However, the preferred way is by reading the DatabaseMetaData object JDBC provides you with.

Andrew Moore
  • 93,497
  • 30
  • 163
  • 175
1

Assuming your interesting preconditions (which other answers try to work around):

Do something like this:

SELECT SQL_NO_CACHE 1;

This gives you a single value in MySQL, and fails in other platforms because SQL_NO_CACHE is a MySQL instruction, not a column.

Alternatively, if your connection has the appropriate privileges:

SELECT * FROM mysql.db;

This is an information table in a database specific to MySQL, so will fail on other platforms.

The other ways are better, but if you really are constrained as you say in your question, this is the way to do it.

Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
0

MySql may be the only db engine that uses backticks. That means something like this should work.

SELECT count(*)
FROM `INFORMATION_SCHEMA.CHARACTER_SETS`
where 1=3

I might not have the backticks in the right spot. Maybe they go like this:

FROM `INFORMATION_SCHEMA`.`CHARACTER_SETS`

Someone who works with MySql would know.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43