Tech Tip: How Check if Table and Field Names for Reserved Words

PRODUCT: 4D | VERSION: 17 | PLATFORM: Mac & Win

Published On: March 25, 2019

4D Table and Field names can be almost anything. While it is possible using reserved SQL words should be avoided to prevent issues as warned in the Inspector of the Structure Editor.

A way to check these names is to compare to see if any of the names of a field or a table matches a reserved SQL word:

ARRAY TEXT($resWords_at;0)
C_LONGINT($countTab_l)
C_LONGINT($countFld_l)

If (True) //Placed in If statement to allow collapse
   APPEND TO ARRAY($resWords_at;"__ROW_ACTION")
   APPEND TO ARRAY($resWords_at;"__ROW_ID")
   APPEND TO ARRAY($resWords_at;"__ROW_STAMP")
   APPEND TO ARRAY($resWords_at;"ABS")
   APPEND TO ARRAY($resWords_at;"ACOS")
   APPEND TO ARRAY($resWords_at;"ADD")
   APPEND TO ARRAY($resWords_at;"ALL")
   APPEND TO ARRAY($resWords_at;"ALPHA_NUMERIC")
   APPEND TO ARRAY($resWords_at;"ALTER")
   APPEND TO ARRAY($resWords_at;"AND")
   APPEND TO ARRAY($resWords_at;"ANY")
   APPEND TO ARRAY($resWords_at;"AS")
   APPEND TO ARRAY($resWords_at;"ASC")
   APPEND TO ARRAY($resWords_at;"ASCII")
   APPEND TO ARRAY($resWords_at;"ASIN")
   APPEND TO ARRAY($resWords_at;"ASYNC")
   APPEND TO ARRAY($resWords_at;"ATAN")
   APPEND TO ARRAY($resWords_at;"ATAN2")
   APPEND TO ARRAY($resWords_at;"AUTO_CLOSE")
   APPEND TO ARRAY($resWords_at;"AUTO_GENERATE")
   APPEND TO ARRAY($resWords_at;"AUTO_INCREMENT")
   APPEND TO ARRAY($resWords_at;"AVG")
   APPEND TO ARRAY($resWords_at;"BETWEEN")
   APPEND TO ARRAY($resWords_at;"BIT")
   APPEND TO ARRAY($resWords_at;"BIT_LENGTH")
   APPEND TO ARRAY($resWords_at;"BLOB")
   APPEND TO ARRAY($resWords_at;"BOOLEAN")
   APPEND TO ARRAY($resWords_at;"BOTH")
   APPEND TO ARRAY($resWords_at;"BY")
   APPEND TO ARRAY($resWords_at;"BYTE")
   APPEND TO ARRAY($resWords_at;"CASCADE")
   APPEND TO ARRAY($resWords_at;"CASE")
   APPEND TO ARRAY($resWords_at;"CAST")
   APPEND TO ARRAY($resWords_at;"CEILING")
   APPEND TO ARRAY($resWords_at;"CHAR")
   APPEND TO ARRAY($resWords_at;"CHAR_LENGTH")
   APPEND TO ARRAY($resWords_at;"CLOB")
   APPEND TO ARRAY($resWords_at;"COALESCE")
   APPEND TO ARRAY($resWords_at;"COMMIT")
   APPEND TO ARRAY($resWords_at;"CONCAT")
   APPEND TO ARRAY($resWords_at;"CONCATENATE")
   APPEND TO ARRAY($resWords_at;"CONSTRAINT")
   APPEND TO ARRAY($resWords_at;"CONSTRAINTS")
   APPEND TO ARRAY($resWords_at;"COS")
   APPEND TO ARRAY($resWords_at;"COT")
   APPEND TO ARRAY($resWords_at;"COUNT")
   APPEND TO ARRAY($resWords_at;"CREATE")
   APPEND TO ARRAY($resWords_at;"CROSS")
   APPEND TO ARRAY($resWords_at;"CURDATE")
   APPEND TO ARRAY($resWords_at;"CURRENT_DATE")
   APPEND TO ARRAY($resWords_at;"CURRENT_TIME")
   APPEND TO ARRAY($resWords_at;"CURRENT_TIMESTAMP")
   APPEND TO ARRAY($resWords_at;"CURTIME")
   APPEND TO ARRAY($resWords_at;"DATABASE")
   APPEND TO ARRAY($resWords_at;"DATABASE_PATH")
   APPEND TO ARRAY($resWords_at;"DATAFILE")
   APPEND TO ARRAY($resWords_at;"DATE")
   APPEND TO ARRAY($resWords_at;"DATE_TO_CHAR")
   APPEND TO ARRAY($resWords_at;"DAY")
   APPEND TO ARRAY($resWords_at;"DAYNAME")
   APPEND TO ARRAY($resWords_at;"DAYOFMONTH")
   APPEND TO ARRAY($resWords_at;"DAYOFWEEK")
   APPEND TO ARRAY($resWords_at;"DAYOFYEAR")
   APPEND TO ARRAY($resWords_at;"DEBUG")
   APPEND TO ARRAY($resWords_at;"DEFAULT")
   APPEND TO ARRAY($resWords_at;"DEGREES")
   APPEND TO ARRAY($resWords_at;"DELETE")
   APPEND TO ARRAY($resWords_at;"DESC")
   APPEND TO ARRAY($resWords_at;"DIRECT")
   APPEND TO ARRAY($resWords_at;"DISABLE")
   APPEND TO ARRAY($resWords_at;"DISTINCT")
   APPEND TO ARRAY($resWords_at;"DOUBLE")
   APPEND TO ARRAY($resWords_at;"DROP")
   APPEND TO ARRAY($resWords_at;"DURATION")
   APPEND TO ARRAY($resWords_at;"ELSE")
   APPEND TO ARRAY($resWords_at;"ENABLE")
   APPEND TO ARRAY($resWords_at;"END")
   APPEND TO ARRAY($resWords_at;"ESCAPE")
   APPEND TO ARRAY($resWords_at;"EXCLUSIVE")
   APPEND TO ARRAY($resWords_at;"EXECUTE")
   APPEND TO ARRAY($resWords_at;"EXISTS")
   APPEND TO ARRAY($resWords_at;"EXP")
   APPEND TO ARRAY($resWords_at;"EXTRACT")
   APPEND TO ARRAY($resWords_at;"FALSE")
   APPEND TO ARRAY($resWords_at;"FLOAT")
   APPEND TO ARRAY($resWords_at;"FLOOR")
   APPEND TO ARRAY($resWords_at;"FN")
   APPEND TO ARRAY($resWords_at;"FOR")
   APPEND TO ARRAY($resWords_at;"FOREIGN")
   APPEND TO ARRAY($resWords_at;"FROM")
   APPEND TO ARRAY($resWords_at;"FULL")
   APPEND TO ARRAY($resWords_at;"GRANT")
   APPEND TO ARRAY($resWords_at;"GROUP")
   APPEND TO ARRAY($resWords_at;"HAVING")
   APPEND TO ARRAY($resWords_at;"HOUR")
   APPEND TO ARRAY($resWords_at;"IF")
   APPEND TO ARRAY($resWords_at;"IMMEDIATE")
   APPEND TO ARRAY($resWords_at;"IN")
   APPEND TO ARRAY($resWords_at;"INDEX")
   APPEND TO ARRAY($resWords_at;"INDEXES")
   APPEND TO ARRAY($resWords_at;"INF")
   APPEND TO ARRAY($resWords_at;"INFILE")
   APPEND TO ARRAY($resWords_at;"INNER")
   APPEND TO ARRAY($resWords_at;"INSERT")
   APPEND TO ARRAY($resWords_at;"INT")
   APPEND TO ARRAY($resWords_at;"INT16")
   APPEND TO ARRAY($resWords_at;"INT32")
   APPEND TO ARRAY($resWords_at;"INT64")
   APPEND TO ARRAY($resWords_at;"INTERVAL")
   APPEND TO ARRAY($resWords_at;"INTO")
   APPEND TO ARRAY($resWords_at;"IS")
   APPEND TO ARRAY($resWords_at;"JOIN")
   APPEND TO ARRAY($resWords_at;"KEY")
   APPEND TO ARRAY($resWords_at;"LATEST")
   APPEND TO ARRAY($resWords_at;"LEADING")
   APPEND TO ARRAY($resWords_at;"LEFT")
   APPEND TO ARRAY($resWords_at;"LENGTH")
   APPEND TO ARRAY($resWords_at;"LIKE")
   APPEND TO ARRAY($resWords_at;"LIMIT")
   APPEND TO ARRAY($resWords_at;"LISTBOX")
   APPEND TO ARRAY($resWords_at;"LOCAL")
   APPEND TO ARRAY($resWords_at;"LOCATE")
   APPEND TO ARRAY($resWords_at;"LOCK")
   APPEND TO ARRAY($resWords_at;"LOG")
   APPEND TO ARRAY($resWords_at;"LOG10")
   APPEND TO ARRAY($resWords_at;"LOWER")
   APPEND TO ARRAY($resWords_at;"LTRIM")
   APPEND TO ARRAY($resWords_at;"MAX")
   APPEND TO ARRAY($resWords_at;"MILLISECOND")
   APPEND TO ARRAY($resWords_at;"MIN")
   APPEND TO ARRAY($resWords_at;"MINUTE")
   APPEND TO ARRAY($resWords_at;"MOD")
   APPEND TO ARRAY($resWords_at;"MODE")
   APPEND TO ARRAY($resWords_at;"MODIFY")
   APPEND TO ARRAY($resWords_at;"MONTH")
   APPEND TO ARRAY($resWords_at;"MONTHNAME")
   APPEND TO ARRAY($resWords_at;"NATURAL")
   APPEND TO ARRAY($resWords_at;"NOT")
   APPEND TO ARRAY($resWords_at;"NULL")
   APPEND TO ARRAY($resWords_at;"NULLIF")
   APPEND TO ARRAY($resWords_at;"NUMERIC")
   APPEND TO ARRAY($resWords_at;"OCTET_LENGTH")
   APPEND TO ARRAY($resWords_at;"OFFSET")
   APPEND TO ARRAY($resWords_at;"OJ")
   APPEND TO ARRAY($resWords_at;"ON")
   APPEND TO ARRAY($resWords_at;"OR")
   APPEND TO ARRAY($resWords_at;"ORDER")
   APPEND TO ARRAY($resWords_at;"OUTER")
   APPEND TO ARRAY($resWords_at;"OVER")
   APPEND TO ARRAY($resWords_at;"PI")
   APPEND TO ARRAY($resWords_at;"PICTURE")
   APPEND TO ARRAY($resWords_at;"POSITION")
   APPEND TO ARRAY($resWords_at;"POWER")
   APPEND TO ARRAY($resWords_at;"PRECISION")
   APPEND TO ARRAY($resWords_at;"PRIMARY")
   APPEND TO ARRAY($resWords_at;"QUARTER")
   APPEND TO ARRAY($resWords_at;"RADIANS")
   APPEND TO ARRAY($resWords_at;"RAND")
   APPEND TO ARRAY($resWords_at;"READ")
   APPEND TO ARRAY($resWords_at;"READ_WRITE")
   APPEND TO ARRAY($resWords_at;"REAL")
   APPEND TO ARRAY($resWords_at;"REFERENCES")
   APPEND TO ARRAY($resWords_at;"REMOTE")
   APPEND TO ARRAY($resWords_at;"RENAME")
   APPEND TO ARRAY($resWords_at;"REPEAT")
   APPEND TO ARRAY($resWords_at;"REPLACE")
   APPEND TO ARRAY($resWords_at;"REPLICATE")
   APPEND TO ARRAY($resWords_at;"RESTRICT")
   APPEND TO ARRAY($resWords_at;"REVOKE")
   APPEND TO ARRAY($resWords_at;"RIGHT")
   APPEND TO ARRAY($resWords_at;"ROLLBACK")
   APPEND TO ARRAY($resWords_at;"ROUND")
   APPEND TO ARRAY($resWords_at;"RTRIM")
   APPEND TO ARRAY($resWords_at;"SCHEMA")
   APPEND TO ARRAY($resWords_at;"SECOND")
   APPEND TO ARRAY($resWords_at;"SELECT")
   APPEND TO ARRAY($resWords_at;"SET")
   APPEND TO ARRAY($resWords_at;"SHARE")
   APPEND TO ARRAY($resWords_at;"SIGN")
   APPEND TO ARRAY($resWords_at;"SIN")
   APPEND TO ARRAY($resWords_at;"SMALLINT")
   APPEND TO ARRAY($resWords_at;"SOME")
   APPEND TO ARRAY($resWords_at;"SPACE")
   APPEND TO ARRAY($resWords_at;"SQL_INTERNAL")
   APPEND TO ARRAY($resWords_at;"SQRT")
   APPEND TO ARRAY($resWords_at;"STAMP")
   APPEND TO ARRAY($resWords_at;"START")
   APPEND TO ARRAY($resWords_at;"STRUCTURE_FILE")
   APPEND TO ARRAY($resWords_at;"SUBSTRING")
   APPEND TO ARRAY($resWords_at;"SUM")
   APPEND TO ARRAY($resWords_at;"SYNC")
   APPEND TO ARRAY($resWords_at;"SYNCHRONIZE")
   APPEND TO ARRAY($resWords_at;"TABLE")
   APPEND TO ARRAY($resWords_at;"TAN")
   APPEND TO ARRAY($resWords_at;"TEXT")
   APPEND TO ARRAY($resWords_at;"THEN")
   APPEND TO ARRAY($resWords_at;"THREADING")
   APPEND TO ARRAY($resWords_at;"TIME")
   APPEND TO ARRAY($resWords_at;"TIMESTAMP")
   APPEND TO ARRAY($resWords_at;"TO")
   APPEND TO ARRAY($resWords_at;"TRAILING")
   APPEND TO ARRAY($resWords_at;"TRANSACTION")
   APPEND TO ARRAY($resWords_at;"TRANSLATE")
   APPEND TO ARRAY($resWords_at;"TRIM")
   APPEND TO ARRAY($resWords_at;"TRUE")
   APPEND TO ARRAY($resWords_at;"TRUNC")
   APPEND TO ARRAY($resWords_at;"TRUNCATE")
   APPEND TO ARRAY($resWords_at;"TS")
   APPEND TO ARRAY($resWords_at;"UNIQUE")
   APPEND TO ARRAY($resWords_at;"UNLOCK")
   APPEND TO ARRAY($resWords_at;"UPDATE")
   APPEND TO ARRAY($resWords_at;"UPPER")
   APPEND TO ARRAY($resWords_at;"USE")
   APPEND TO ARRAY($resWords_at;"UTF16")
   APPEND TO ARRAY($resWords_at;"UTF8")
   APPEND TO ARRAY($resWords_at;"UUID")
   APPEND TO ARRAY($resWords_at;"VALUES")
   APPEND TO ARRAY($resWords_at;"VARCHAR")
   APPEND TO ARRAY($resWords_at;"VARYING")
   APPEND TO ARRAY($resWords_at;"VIEW")
   APPEND TO ARRAY($resWords_at;"WEEK")
   APPEND TO ARRAY($resWords_at;"WHEN")
   APPEND TO ARRAY($resWords_at;"WHERE")
   APPEND TO ARRAY($resWords_at;"WITH")
   APPEND TO ARRAY($resWords_at;"YEAR")
End if

For ($countTab_l;1;Get last table number)
   If (Find in array($resWords_at;Table name($countTab_l))>0)
     // Invalid Table Name found
     // Code should be added to handle the table

   End if
   For ($countFld_l;1;Get last field number($countTab_l))
     If (Find in array($resWords_at;Field name($countTab_l;$countFld_l))>0)
       // Invalid Field Name found
       // Code should be added to handle the Field
     End if
   End for
End for

The above can be used to identify table and field names that are using a reserved word. If any are found it is suggested that the name be changed if possible to prevent and issues from occuring.

The reserved words are based off the list of SQL Commands:
https://doc.4d.com/4Dv17R4/4D/17-R4.40/Alphabetical-list-of-commands.902-4075475.en.1.html

and the virtual Fields:
https://doc.4d.com/4Dv17R4/4D/17-R4/Replication-via-SQL.300-4075486.en.html