How to add an item to database keyword list

[abonic]

I have 'OVER' as the column name in several database tables and it creates problems with MariaDB, since it is a keyword in newer versions. I know where to make a change in ZEOS source but I wonder if there is a way to solve the problem in the application code?

(MariaDb 10.4.12, Zeos 7.3 latest, Delphi 2009)

[marsupilami]

I have 'OVER' as the column name in several database tables and it creates problems with MariaDB, since it is a keyword in newer versions. I know where to make a change in ZEOS source but I wonder if there is a way to solve the problem in the application code?

(MariaDb 10.4.12, Zeos 7.3 latest, Delphi 2009)

Currently there isn't a way to do that. Also I think it really should be maintained in Zeos code. Could you supply a patch file?

Best regards,

Jan

[abonic]

I don't use SVN and it's too much work for me to install it now and re-learn the forgotten so here's the change that needs to be made in src\dbc\ZDbcMySqlMetadata.pas:

Code: Select all

function TZMySQLDatabaseInfo.GetSQLKeywords: string;
begin
  Result := 'AUTO_INCREMENT,BINARY,BLOB,ENUM,INFILE,LOAD,MEDIUMINT,OPTION,'
    + 'OUTFILE,REPLACE,SET,TEXT,UNSIGNED,ZEROFILL';
  { mdaems : added all reserved words indicated by mysql documentation (up to mysql 5.1)}
  Result := Result + 'ACCESSIBLE,ADD,ALL,ANALYZE,AND,ASC,ASENSITIVE,'
    + 'BEFORE,BETWEEN,BIGINT,BOTH,CALL,CASCADE,CASE,CHANGE,CHARACTER,CHECK,'
    + 'COLLATE,CONDITION,CONSTRAINT,CONTINUE,CONVERT,CROSS,CURSOR,'
    + 'DATABASE,DATABASES,DAY_HOUR,DAY_MICROSECOND,DAY_MINUTE,DAY_SECOND,'
    + 'DEC,DECIMAL,DECLARE,DEFAULT,DELAYED,DESC,DESCRIBE,DETERMINISTIC,'
    + 'DISTINCT,DISTINCTROW,DIV,DOUBLE,DUAL,EACH,ELSE,ELSEIF,ENCLOSED,'
    + 'ESCAPED,EXISTS,EXIT,EXPLAIN,FALSE,FETCH,FLOAT,FLOAT4,FLOAT8,'
    + 'FORCE,FOREIGN,FULLTEXT,GENERAL,GRANT,HIGH_PRIORITY,HOUR_MICROSECOND,'
    + 'HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,IGNORE_SERVER_IDS,IN,INNER,INOUT,INSENSITIVE,INT,'
    + 'INT1,INT2,INT3,INT4,INT8,INTERVAL,ITERATE,JOIN,KEYS,KILL,LEADING,'
    + 'LEAVE,LEFT,LIKE,LIMIT,LINEAR,LINES,LOCK,'
    + 'LONG,LONGBLOB,LONGTEXT,LOOP,LOW_PRIORITY,MASTER_HEARTBEAT_PERIOD,MASTER_SSL_VERIFY_SERVER_CERT,'
    + 'MATCH,MAXVALUE,MEDIUMBLOB,MEDIUMTEXT,MIDDLEINT,MINUTE_MICROSECOND,MINUTE_SECOND,'
    + 'MOD,MODIFIES,NATURAL,NOT,NO_WRITE_TO_BINLOG,NUMERIC,OPTIMIZE,'
    + 'OPTIONALLY,OR,OUT,OUTER,PRECISION,PROCEDURE,PURGE,RANGE,READ,READS,'
    + 'READ_ONLY,READ_WRITE,REAL,REFERENCES,REGEXP,RELEASE,RENAME,REPEAT,'
    + 'REQUIRE,RESIGNAL,SIGNAL,RESTRICT,RETURN,REVOKE,RIGHT,RLIKE,SCHEMA,SCHEMAS,'
    + 'SECOND_MICROSECOND,SENSITIVE,SEPARATOR,SHOW,SLOW,SMALLINT,SPATIAL,'
    + 'SPECIFIC,SQL,SQLEXCEPTION,SQLSTATE,SQLWARNING,SQL_BIG_RESULT,'
    + 'SQL_CALC_FOUND_ROWS,SQL_SMALL_RESULT,SSL,STARTING,STRAIGHT_JOIN,'
    + 'TERMINATED,THEN,TINYBLOB,TINYINT,TINYTEXT,TO,TRAILING,TRIGGER,'
    + 'TRUE,UNDO,UNION,UNIQUE,UNLOCK,USAGE,USE,USING,UTC_DATE,UTC_TIME,'
    + 'UTC_TIMESTAMP,VARBINARY,VARCHARACTER,VARYING,WHEN,WHILE,WITH,'
    + 'WRITE,X509,XOR,YEAR_MONTH,ACCESSIBLE,LINEAR,'
    + 'MASTER_SSL_VERIFY_SERVER_CERT,RANGE,READ_ONLY,READ_WRITE';
// patch start ********************************************************************************* 
  { more reserved words (up to mysql 8) }  
    Result := Result + 'CUBE,CUME_DIST,DENSE_RANK,EMPTY,EXCEPT,FIRST_VALUE,FUNCTION,'
    + 'GENERATED,GET,GROUPING,GROUPS,IO_AFTER_GTIDS,IO_BEFORE_GTIDS,JSON_TABLE,'
    + 'LAG,LAST_VALUE,LATERAL,LEAD,MASTER_BIND,NTH_VALUE,NTILE,OF,OPTIMIZER_COSTS,OVER,'
    + 'PARTITION,PERCENT_RANK,RANK,RECURSIVE,ROW,ROWS,ROW_NUMBER,STORED,SYSTEM,VIRTUAL,WINDOW';
// patch end ***********************************************************************************     
end;

These are new reserved words from MySQL 8 (according to https://dev.mysql.com/doc/refman/8.0/en/keywords.html) that are not in the already existing list defined by SQL92Keywords constant (ZDbcMetaData.pas) and GetSQLKeywords, GetNumericFunctions, GetStringFunctions, GetSystemFunctions, GetTimeDateFunctions (ZDbcMySqlMetadata.pas).

These are just reserved words (labeled (R) in the linked document), not all new keywords. There are too many of them so I’m not sure how smart it would be to include them as well.

[marsupilami]

I don't use SVN and it's too much work for me to install it now and re-learn the forgotten [...]

ok - don't worry If you use git, there is an unofficial repository at https://github.com/marsupilami79/zeoslib. Changes show up there with a delay of one day since it gets updated at night only.

These are new reserved words from MySQL 8 (according to https://dev.mysql.com/doc/refman/8.0/en/keywords.html) that are not in the already existing list defined by SQL92Keywords constant (ZDbcMetaData.pas) and GetSQLKeywords, GetNumericFunctions, GetStringFunctions, GetSystemFunctions, GetTimeDateFunctions (ZDbcMySqlMetadata.pas).

These are just reserved words (labeled (R) in the linked document), not all new keywords. There are too many of them so I’m not sure how smart it would be to include them as well.

I added your changes to Zeos 7.3 in revision 6506. Could cou check please if this works as expected?

Best regards,

Jan

[abonic]

Added reserved words include changes from version 5.1 to 8, so it's wrong to include them only if the latest version of MySQL is detected. It is not "starting with v8", it's "up to v8".

Besides, there is MariaDb and its versions. As things stand now (r6507), 'OVER' (the keyword that started all this) is not recognized as reserved word with MariaDb (which I am trying to use) or any other forks.

Code: Select all

    if fMySQLFork = fMySQL then begin
      GetVersion(Major, Minor);
      if Major >= 8 then begin

In my opinion, it is best to delete this part and just include new reserved words unconditionally.

[marsupilami]

It is included unconditionally now.

[abonic]

Tested with r6516 and it now works ok. Thank you.