MySQL - "SHOW VARIABLES LIKE '%version%'" not working in my Java application

Question

When I run SHOW VARIABLES LIKE '%version%' in MySQL command line console it runs very fine, but when I run the same code in the Java application I'm working on it produces the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'VARIABLES' in 'field list'

Here is my Java code:

ConnectionHelper.setDatabaseName('information_schema');

Statement statement = ConnectionHelper.getStatement();
ResultSet = statement.executeQuery ("SHOW VARIABLES LIKE '%version%'");

String serverVersion = null;
while (resultSet.next()) {
    serverVersion = resultSet.getString(3);
}

The ConnectionHelper is just a class that creates and sets up a connection to any database passed as parameter to the setDatabaseName() method.

I've even tried not to set any database since the query runs in MySQL command line console even without selecting any database, but still produces the same error.

Please I really need help.

Answer 1

You may use the INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES tables like this:

SELECT * 
FROM information_schema.global_variables 
WHERE variable_name LIKE '%version%';

Sample output:

|           VARIABLE_NAME |                       VARIABLE_VALUE |
|-------------------------|--------------------------------------|
|          INNODB_VERSION |                               5.5.32 |
|        PROTOCOL_VERSION |                                   10 |
|         VERSION_COMMENT | MySQL Community Server (GPL) by Remi |
|                 VERSION |                               5.5.32 |
| VERSION_COMPILE_MACHINE |                               x86_64 |
|      VERSION_COMPILE_OS |                                Linux |
|  SLAVE_TYPE_CONVERSIONS |                                      |

Here is SQL Fiddle demo.

---

UPDATE (based on your comments)

You can specify all variable names that you need with IN clause

SELECT * 
FROM information_schema.global_variables 
WHERE variable_name IN ('version', 'hostname', 'port');

Sample output:

| VARIABLE_NAME | VARIABLE_VALUE |
|---------------|----------------|
|       VERSION |         5.5.32 |
|      HOSTNAME |            db2 |
|          PORT |           3306 |

Here is SQL Fiddle demo.