Strange behaviour changing from Postgres to MySQL

Module Development
1

Hello all,

I have the following method to check if a table exists and if not, create it:

private void checkTableStructure(String tableName) {
        SRConnection con = null;
        String st;
        try {
            con = GatewayHook.getGatewayContext().getDatasourceManager().getConnection(params.getDataSource());
            DBTableSchema table = new DBTableSchema(tableName, con.getParentDatasource().getTranslator());
            table.addRequiredColumn("Timestamp", DataType.DateTime);
            table.addRequiredColumn("Value", DataType.Float4);
            table.verifyAndUpdate(con); 
        } catch (Exception e) {
            logger.info(e.toString());
        } finally {
            DBUtilities.close(con);
        }

When I used a postgres database with a psql driver, it worked perfectly but when I switched to a MySQL database connection, it keeps giving me the following error:

image
image

My guess is that “Timestamp” and “Value” shouldn’t be surrounded by " " . Is this a bug? Is it the driver’s fault? My fault?

Thank you in advance

3

The double quotes are the problem but not in the way you imagined them. MySQL prefers back ticks (`), but it does allow double quotes (") to quote identifiers. However this is not enabled by default, and in order to use them you need to tell MySQL to use "ANSI_QUOTES". Other flavors of database (such as postgres) accept double quotes by default.

I found that in this Stack Overflow question mysql double-quoted table names for setting the option permanently, and also for just within a specific query.

SET GLOBAL SQL_MODE=ANSI_QUOTES;

SET SQL_MODE=ANSI_QUOTES;

That answer also pointed to Do different databases use different name quote? that had the MySQL example of turning the option on only during the query.

SELECT * FROM `my table`;
SET SQL_MODE=ANSI_QUOTES;
SELECT * FROM "my table";

However I have no idea how you turn this option on from within Ignition

Grumble grumble Things like this are why I am biased against MySQL, it just choose to do things differently from other database flavors. My most pointed example is that in MySQL you need to have a space after a comment marker (--) otherwise it won't be recognized as a comment marker. That makes it harder to use comment markers to comment out bits of SQL when you are testing queries.

2 Likes
4

I would say this is the problem. ):

5 Likes
5

DBTableSchema is supposed to do the quoting automatically… I wonder what
con.getParentDatasource().getTranslator().getColumnQuoteCharacter() (disclaimer: that might not be the right method name) says it should be using, or indeed, whether your connections have the right SQL flavor assigned.

6

Thank you all for you responses. Apart from the problem that @pturmel pointed out, the problem was that the database translator was set to MSSQL instead of MySQL :upside_down_face: :upside_down_face: :upside_down_face:.