Select to view content in your preferred language

SQL Keywords Blocking inclusion of certain fields in Form Builder

2295
22
06-16-2023 06:07 AM
JosephHilliard94
New Contributor III

Hi there,

The new ArcGIS Online update (which clearly doesn't state this update) means you cannot use SQL keywords as field names in ArcGIS Online. Is there a definitive list of SQL keywords that we aren't allowed to use in ArcGIS Online? Going to mean quite a lot of re-configuration to existing feature services without prior warning.....

 

Tags (1)
22 Replies
DougBrowning
MVP Esteemed Contributor

That is a big one to change for sure.

Most of my stuff is in 123 so I use their list which has a lot of the SQL keywords on there.  This may be a decent place to start since it has SQL, SQL Lite, and AGOL items on this list.  Not sure how complete it is though.

A
ABS
ABSENT
ACCESS
ACCORDING
ACCOUNT
ACTIVATE
ADA
ADD
ADMIN
ADVISE
AFTER
ALL
ALL_ROWS
ALLOCATE
ALLOW
ALTER
ANALYSE
ANALYZE
AND
ANY
ARCHIVE
ARCHIVELOG
ARE
AREA
ARRAY
ARRAY_AGG
ARRAY_MAX_CARDINALITY
AS
ASC
ASENSITIVE
ASSOCIATE
ASUTIME
ASYMMETRIC
AT
ATOMIC
ATTRIBUTES
AUDIT
AUTHENTICATED
AUTHORIZATION
AUTOEXTEND
AUTOMATIC
AUX
AUXILIARY
AVG
BACKUP
BASE64
BECOME
BEFORE
BEGIN
BEGIN_FRAME
BEGIN_PARTITION
BERNOULLI
BETWEEN
BFILE
BINARY
BIT_LENGTH
BITMAP
BLOB
BLOCK
BLOCKED
BODY
BOM
BOTH
BREADTH
BREAK
BROWSE
BUFFERPOOL
BULK
BY
C
CACHE
CACHE_INSTANCES
CALL
CANCEL
CAPTURE
CARDINALITY
CASCADE
CASCADED
CASE
CAST
CATALOG_NAME
CCSID
CEIL
CEILING
CFILE
CHAINED
CHANGE
CHAR
CHAR_CS
CHAR_LENGTH
CHARACTER
CHARACTER_LENGTH
CHARACTER_SET_CATALOG
CHARACTER_SET_NAME
CHARACTER_SET_SCHEMA
CHARACTERS
CHECK
CHECKPOINT
CHOOSE
CHUNK
CLASS_ORIGIN
CLEAR
CLOB
CLONE
CLOSE
CLOSE_CACHED_OPEN_CURSORS
CLUSTER
CLUSTERED
COALESCE
COBOL
COLLATE
COLLATION
COLLATION_CATALOG
COLLATION_NAME
COLLATION_SCHEMA
COLLECT
COLLECTION
COLLID
COLUMN
COLUMN_NAME
COLUMNS
COMMAND_FUNCTION
COMMAND_FUNCTION_CODE
COMMENT
COMMIT
COMMITTED
COMPATIBILITY
COMPILE
COMPLETE
COMPOSITE_LIMIT
COMPRESS
COMPUTE
CONCAT
CONCURRENTLY
CONDITION
CONDITION_NUMBER
CONNECT
CONNECT_TIME
CONNECTION
CONNECTION_NAME
CONSTRAINT
CONSTRAINT_CATALOG
CONSTRAINT_NAME
CONSTRAINT_SCHEMA
CONSTRAINTS
CONSTRUCTOR
CONTAINS
CONTAINSTABLE
CONTENT
CONTENTS
CONTINUE
CONTROL
CONTROLFILE
CONVERT
CORR
CORRESPONDING
COST
COUNT
COVAR_POP
COVAR_SAMP
CPU_PER_CALL
CPU_PER_SESSION
CREATE
CREATIONDATE
CREATOR
CROSS
CUBE
CUME_DIST
CURREN_USER
CURRENT
CURRENT_CATALOG
CURRENT_CONNECTION
CURRENT_DATE
CURRENT_DEFAULT_TRANSFORM_GROUP
CURRENT_LC_CTYPE
CURRENT_PATH
CURRENT_ROLE
CURRENT_ROW
CURRENT_SCHEMA
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TRANSFORM_GROUP_FOR_TYPE
CURRENT_USER
CURRENT_UTCDATE
CURRENT_UTCTIME
CURRENT_UTCTIMESTAMP
CURRVAL
CURSOR
CURSOR_NAME
CYCLE
DANGLING
DATA
DATABASE
DATAFILE
DATAFILES
DATALINK
DATAOBJNO
DATE
DATETIME_INTERVAL_CODE
DATETIME_INTERVAL_PRECISION
DAY
DAYS
DB
DBA
DBCC
DBHIGH
DBINFO
DBLOW
DBMAC
DEALLOCATE
DEBUG
DEC
DECIMAL
DECLARE
DEFAULT
DEFERRABLE
DEFERRED
DEFINED
DEGREE
DELETE
DENSE_RANK
DENY
DEPTH
DEREF
DERIVED
DESC
DESCRIBE
DESCRIPTOR
DETERMINISTIC
DIAGNOSTICS
DIRECTORY
DISABLE
DISALLOW
DISCONNECT
DISK
DISMOUNT
DISPATCH
DISTINCT
DISTRIBUTED
DLNEWCOPY
DLPREVIOUSCOPY
DLURLCOMPLETE
DLURLCOMPLETEONLY
DLURLCOMPLETEWRITE
DLURLPATH
DLURLPATHONLY
DLURLPATHWRITE
DLURLSCHEME
DLURLSERVER
DLVALUE
DML
DO
DOCUMENT
DOUBLE
DROP
DSSIZE
DUMP
DYNAMIC
DYNAMIC_FUNCTION
DYNAMIC_FUNCTION_CODE
EACH
EDITDATE
EDITOR
EDITPROC
ELEMENT
ELSE
ELSEIF
ELSIF
EMAXX
EMAXY
EMAXZ
EMINX
EMINY
EMINZ
EMPTY
ENABLE
ENCODING
ENCRYPTION
END
END_FRAME
END_PARTITION
END-EXEC
ENDING
ENFORCE
ENFORCED
ENTITY
ENTRY
EQUALS
ERASE
ERRLVL
ESCAPE
EVERY
EXCEPT
EXCEPTION
EXCEPTIONS
EXCHANGE
EXCLUDING
EXCLUSIVE
EXEC
EXECUTE
EXISTS
EXIT
EXP
EXPIRE
EXPLAIN
EXPRESSION
EXTENT
EXTENTS
EXTERNAL
EXTERNALLY
FAILED_LOGIN_ATTEMPTS
FALSE
FAST
FENCED
FETCH
FID
FIELDPROC
FILE
FILLFACTOR
FILTER
FINAL
FIRST
FIRST_ROWS
FIRST_VALUE
FLAG
FLAGGER
FLOAT
FLOB
FLOOR
FLUSH
FOR
FORCE
FOREIGN
FORTRAN
FOUND
FRAME_ROW
FREE
FREELIST
FREELISTS
FREETEXT
FREETEXTTABLE
FREEZE
FROM
FS
FULL
FUNCTION
FUSION
G
GENERAL
GENERATED
GET
GLOBAL
GLOBAL_NAME
GLOBALID
GLOBALLY
GO
GOTO
GRANT
GROUP
GROUPING
GROUPS
HANDLER
HASH
HASHKEYS
HAVING
HEADER
HEAP
HEX
HIERARCHY
HOLD
HOLDLOCK
HOUR
HOURS
ID
IDENTIFIED
IDENTITY
IDENTITY_INSERT
IDENTITYCOL
IDGENERATORS
IDLE_TIME
IF
IGNORE
ILIKE
IMMEDIATE
IMMEDIATELY
IMPLEMENTATION
IMPORT
IN
INCLUDING
INCLUSIVE
INCREMENT
IND_PARTITION
INDENT
INDEX
INDEXED
INDEXES
INDICATOR
INHERIT
INITIAL
INITIALLY
INITRANS
INNER
INOUT
INSENSITIVE
INSERT
INSTANCE
INSTANCES
INSTANTIABLE
INSTEAD
INT
INTEGER
INTEGRITY
INTERMEDIATE
INTERSECT
INTERSECTION
INTO
IS
ISNULL
ISOBID
ISOLATION
ISOLATION_LEVEL
ITERATE
JAR
JOIN
K
KEEP
KEY
KEY_MEMBER
KEY_TYPE
KILL
LABEL
LAG
LANGUAGE
LAST
LAST_VALUE
LATERAL
LAYER
LC_CTYPE
LEAD
LEADING
LEAVE
LEFT
LEN
LENGTH
LESS
LEVEL
LIBRARY
LIKE
LIKE_REGEX
LIMIT
LINENO
LINK
LIST
LN
LOAD
LOB
LOCAL
LOCALE
LOCALTIME
LOCALTIMESTAMP
LOCATOR
LOCATORS
LOCK
LOCKED
LOCKMAX
LOCKSIZE
LOG
LOGFILE
LOGGING
LOGICAL_READS_PER_CALL
LOGICAL_READS_PER_SESSION
LONG
LOOP
LOWER
M
MAINTAINED
MANAGE
MAP
MASTER
MATCHED
MATERIALIZED
MAX
MAX_CARDINALITY
MAX_MEASURE
MAXARCHLOGS
MAXDATAFILES
MAXEXTENTS
MAXINSTANCES
MAXLOGFILES
MAXLOGHISTORY
MAXLOGMEMBERS
MAXSIZE
MAXTRANS
MAXVALUE
MEMBER
MERGE
MESSAGE_LENGTH
MESSAGE_OCTET_LENGTH
MESSAGE_TEXT
METHOD
MICROSECOND
MICROSECONDS
MIN
MIN_MEASURE
MINEXTENTS
MINIMUM
MINUS
MINUTE
MINUTES
MINVALUE
MLS_LABEL_FORMAT
MLSLABEL
MOD
MODE
MODIFIES
MODIFY
MODULE
MONTH
MONTHS
MORE
MOUNT
MOVE
MTS_DISPATCHERS
MULTISET
MUMPS
NAMESPACE
NATIONAL
NATURAL
NCHAR
NCHAR_CS
NCLOB
NEEDED
NESTED
NESTING
NETWORK
NEW
NEXT
NEXTVAL
NFC
NFD
NFKC
NFKD
NIL
NO
NOARCHIVELOG
NOAUDIT
NOCACHE
NOCHECK
NOCOMPRESS
NOCYCLE
NOFORCE
NOLOGGING
NOMAXVALUE
NOMINVALUE
NONCLUSTERED
NONE
NOORDER
NOOVERRIDE
NOPARALLEL
NOREVERSE
NORMAL
NORMALIZE
NORMALIZED
NOSORT
NOT
NOTHING
NOTNULL
NOWAIT
NTH_VALUE
NTILE
NULL
NULLABLE
NULLIF
NULLS
NUMBER
NUMERIC
NUMOFPTS
NUMPARTS
NVARCHAR2
OBID
OBJECT
OBJECTID
OBJNO
OBJNO_REUSE
OCCURRENCES_REGEX
OCTET_LENGTH
OCTETS
OF
OFF
OFFLINE
OFFSET
OFFSETS
OID
OIDINDEX
OLD
ON
ONLINE
ONLY
OPCODE
OPEN
OPENDATASOURCE
OPENQUERY
OPENROWSET
OPENXML
OPTIMAL
OPTIMIZATION
OPTIMIZE
OPTIMIZER_GOAL
OPTION
OR
ORDER
ORDERING
ORDINALITY
ORGANIZATION
OSLABEL
OTHERS
OUT
OUTER
OUTPUT
OVER
OVERFLOW
OVERLAPS
OVERRIDING
OWN
P
PACKAGE
PAD
PADDED
PARALLEL
PARAMETER
PARAMETER_MODE
PARAMETER_NAME
PARAMETER_ORDINAL_POSITION
PARAMETER_SPECIFIC_CATALOG
PARAMETER_SPECIFIC_NAME
PARAMETER_SPECIFIC_SCHEMA
PART
PARTITION
PARTITIONED
PARTITIONING
PASCAL
PASSTHROUGH
PASSWORD
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
PASSWORD_LOCK_TIME
PASSWORD_REUSE_MAX
PASSWORD_REUSE_TIME
PASSWORD_VERIFY_FUNCTION
PATH
PCTFREE
PCTINCREASE
PCTTHRESHOLD
PCTUSED
PCTVERSION
PERCENT
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
PERIOD
PERMANENT
PERMISSION
PIECESIZE
PIVOT
PLACING
PLAN
PLI
PLSQL_DEBUG
POINTS
PORTION
POSITION_REGEX
POST_TRANSACTION
POWER
PRECEDES
PRECISION
PREPARE
PRESERVE
PREVVAL
PRIMARY
PRINT
PRIOR
PRIQTY
PRIVATE
PRIVATE_SGA
PRIVILEGE
PRIVILEGES
PROC
PROCEDURE
PROFILE
PROGRAM
PSID
PUBLIC
PURGE
QUERY
QUERYNO
QUEUE
QUOTA
RAISERROR
RANGE
RANK
RAW
RBA
READ
READS
READTEXT
READUP
REAL
REBUILD
RECONFIGURE
RECOVER
RECOVERABLE
RECOVERY
REF
REFERENCES
REFERENCING
REFRESH
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
RELEASE
RENAME
REPEAT
REPLACE
REPLICATION
REQUIRING
RESET
RESETLOGS
RESIGNAL
RESIZE
RESOURCE
RESPECT
RESTORE
RESTRICT
RESTRICTED
RESULT
RESULT_SET_LOCATOR
RETURN
RETURNED_CARDINALITY
RETURNED_LENGTH
RETURNED_OCTET_LENGTH
RETURNED_SQLSTATE
RETURNING
RETURNS
REUSE
REVERSE
REVERT
REVOKE
RIGHT
ROLE
ROLES
ROLLBACK
ROLLUP
ROUND_CEILING
ROUND_DOWN
ROUND_FLOOR
ROUND_HALF_DOWN
ROUND_HALF_EVEN
ROUND_HALF_UP
ROUND_UP
ROUTINE
ROUTINE_CATALOG
ROUTINE_NAME
ROUTINE_SCHEMA
ROW
ROW_COUNT
ROW_NUMBER
ROWCOUNT
ROWGUIDCOL
ROWID
ROWNUM
ROWS
ROWSET
RULE
RUN
SAMPLE
SAVE
SAVEPOINT
SB4
SCALE
SCAN_INSTANCES
SCHEMA
SCHEMA_NAME
SCN
SCOPE
SCOPE_CATALOG
SCOPE_NAME
SCOPE_SCHEMA
SCRATCHPAD
SD_ALL
SD_INHIBIT
SD_SHOW
SECOND
SECONDS
SECQTY
SECTION
SECURITY
SECURITYAUDIT
SEG_BLOCK
SEG_FILE
SEGMENT
SELECT
SELECTIVE
SELF
SEMANTICKEYPHRASETABLE
SEMANTICSIMILARITYDETAILSTABLE
SEMANTICSIMILARITYTABLE
SENSITIVE
SEQUENCE
SERIALIZABLE
SERVER_NAME
SESSION
SESSION_CACHED_CURSORS
SESSION_USER
SESSIONS_PER_USER
SET
SETS
SETUSER
SHAPE
SHARE
SHARED
SHARED_POOL
SHRINK
SHUTDOWN
SIGNAL
SIMILAR
SIMPLE
SIZE
SKIP
SKIP_UNUSABLE_INDEXES
SMALLINT
SNAPSHOT
SOME
SORT
SOURCE
SPACE
SPECIFIC
SPECIFIC_NAME
SPECIFICATION
SPECIFICTYPE
SPLIT
SQL
SQL_TRACE
SQLCODE
SQLERROR
SQLEXCEPTION
SQLSTATE
SQLWARNING
SQRT
STANDARD
STANDBY
START
STATE
STATEMENT
STATEMENT_ID
STATIC
STATISTICS
STAY
STDDEV_POP
STDDEV_SAMP
STOGROUP
STOP
STORAGE
STORE
STORES
STRUCTURE
STYLE
SUBCLASS_ORIGIN
SUBMULTISET
SUBSTRING_REGEX
SUCCEEDS
SUCCESSFUL
SUM
SUMMARY
SWITCH
SYMMETRIC
SYNONYM
SYS_OP_ENFORCE_NOT_NULL$
SYS_OP_NTCIMG$
SYSDATE
SYSDBA
SYSOPER
SYSTEM
SYSTEM_TIME
SYSTEM_USER
SYSTIME
SYSTIMESTAMP
SYSUUID
T
TABLE
TABLE_NAME
TABLES
TABLESAMPLE
TABLESPACE
TABLESPACE_NO
TABNO
TEMPORARY
TEXTSIZE
THAN
THE
THEN
THREAD
TIES
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TOKEN
TOP
TOP_LEVEL_COUNT
TOPLEVEL
TRACE
TRACING
TRAILING
TRAN
TRANSACTION
TRANSACTION_ACTIVE
TRANSACTIONS_COMMITTED
TRANSACTIONS_ROLLED_BACK
TRANSFORM
TRANSFORMS
TRANSITIONAL
TRANSLATE
TRANSLATE_REGEX
TRANSLATION
TRIGGER
TRIGGER_CATALOG
TRIGGER_NAME
TRIGGER_SCHEMA
TRIGGERS
TRIM_ARRAY
TRUE
TRUNCATE
TRY_CONVERT
TSEQUAL
TX
TYPE
UB2
UBA
UESCAPE
UID
UNARCHIVED
UNDER
UNDO
UNION
UNIQUE
UNLIMITED
UNLINK
UNLOCK
UNNAMED
UNNEST
UNPIVOT
UNRECOVERABLE
UNTIL
UNTYPED
UNUSABLE
UNUSED
UPDATABLE
UPDATE
UPDATETEXT
UPPER
URI
USAGE
USE
USER
USER_DEFINED_TYPE_CATALOG
USER_DEFINED_TYPE_CODE
USER_DEFINED_TYPE_NAME
USER_DEFINED_TYPE_SCHEMA
USING
UTCDATE
UTCTIME
UTCTIMESTAMP
VALIDATE
VALIDATION
VALIDPROC
VALUE
VALUE_OF
VALUES
VAR_POP
VAR_SAMP
VARBINARY
VARCHAR
VARCHAR2
VARIABLE
VARIADIC
VARIANT
VARYING
VCAT
VERBOSE
VERSIONING
VIEW
VOLATILE
VOLUMES
WAITFOR
WHEN
WHENEVER
WHERE
WHILE
WIDTH_BUCKET
WINDOW
WITH
WITHIN
WITHIN GROUP
WITHOUT
WLM
WORK
WRITE
WRITEDOWN
WRITETEXT
WRITEUP
XID
XMLAGG
XMLBINARY
XMLCAST
XMLCOMMENT
XMLDECLARATION
XMLDOCUMENT
XMLEXISTS
XMLITERATE
XMLNAMESPACES
XMLQUERY
XMLSCHEMA
XMLTABLE
XMLTEXT
XMLVALIDATE
YEAR
YEARS
ZONE

ColinLawrence
Esri Regular Contributor

@JosephHilliard94 this wasnt an intentional change and will be fixed in the next couple of days. We should continue to ignore existing fields with reserved keywords created from another source (ie Pro) and loaded into Designer. There should only be an error when creating new fields in Designer with the restricted keywords. 

Regards,
Colin
SamiHammer
New Contributor III

Hmm, I just had this happen this morning - was there another update that caused this problem again? I haven't looked fully though, but I have fields called EDITDATE and OBJECTID that look like they are reserved. What's going on?

TAMRAO_HALLORAN
New Contributor

Any updates on this "fix"?

jcucsc
by
New Contributor III

Still nothing on my end, just ran into this problem while trying to update a survey after weeks of work. What is ESRI thinking?

 

0 Kudos
D_AnneHammond
New Contributor III

I just ran into this issue as well when trying to update a field map with offline areas, and I have an array of offline field maps (!)  Seems like every time I turn around Esri does something that makes me reconfigure on-the-fly increasing my workload substantially... so frustrating... even worse, I don't see any field names that are reserved SQL words, can someone can point out which field names in the list below are reserved SQL words?

D_AnneHammond_0-1719510652712.png

 

Tags (3)
SamiHammer
New Contributor III

Looks like you have GLOBALID and FID in that list that Doug posted above.

We have various maps that were working fine before today that also have these errors, so it's definitely an ESRI problem. (I'm also getting a weird error on some other layers that "this layer requires true curve editing, which is not currently supported in Field Maps.)

DougBrowning
MVP Esteemed Contributor

AGOL was updated last night which could be it?   https://doc.arcgis.com/en/arcgis-online/reference/whats-new.htm 

LauraBedoyan
Esri Contributor

Hi D_AnneHammond,

If you could please change your "Range" field to a different name, such as "Range_Field" or another option that doesn't use "Range" exactly as the field name This change is necessary because "Range" is a recognized SQL Lite Keyword, and we've introduced checks for such keywords for offline use. For a list of these keywords, visit https://sqlite.org/lang_keywords.html. Let me know if you encounter any problems.

This isn't a new restriction we've implemented; it's an existing one that would have previously prevented taking the map offline if it contained SQLite keywords. We've just enhanced the messaging during the authoring process to make this clearer.

0 Kudos