KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: How Check if Table and Field Names for Reserved Words
PRODUCT: 4D | VERSION: 17 | PLATFORM: Mac & Win
Published On: March 25, 2019
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

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