topic Re: Comments in SQL expressions — Single-line comment at and of statement causes error in ArcGIS Pro Questions

Comments in SQL expressions — Single-line comment at and of statement causes error

Bud — Wed, 08 Feb 2023 02:13:56 GMT

ArcGIS Pro 3.0.3; mobile geodatabase:

I want to put SQL comments in definition query SQL expressions. From my testing, these kinds of comments seem to work as expected:

Works: Multi-line SQL comments at the beginning or end of the SQL expression:

/*multi-line sql comment*/
OBJECTID IN (3, 5, 11)

OBJECTID IN (3, 5, 11)
/*multi-line sql comment*/

Works: Single-line SQL comments at the beginning of the SQL expression:

--single-line sql comment
OBJECTID IN (3, 5, 11)

Doesn't work: Single-line SQL comments at the end of the SQL expression:

OBJECTID IN (3, 5, 11)
--single-line sql comment

Error: The SQL expression has invalid syntax.

Question:

Why don't single-line comments work at the end of SQL expressions in ArcGIS Pro? Is that a bug?

I don't have that issue when creating full-blown SELECT queries in database views, query layers, or SQL Developer queries. I only seem to have that problem with ArcGIS Pro SQL expressions.

Note: There is a bug in ArcGIS Pro 3.0.3 that can be a nuisance: Modifying existing SQL expression disables definition query. So when testing things like the comments/SQL above, we need to re-enable definition queries after modifying the SQL.

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

Bud — Tue, 28 Feb 2023 18:33:18 GMT

Esri Canada Case 03268522 - Comments in SQL expressions - Single-line comment at and of statement causes error

ENH-000156157 - ArcGIS Pro - Add a line break automatically to the end of any definition query SQL expression applied to a mobile geodatabase feature class to avoid the error "The SQL expression has invalid syntax." when a SQL single-line comment that is not terminated by a line break is inserted at the end of the expression.

ENH-000156184 - ArcGIS Pro - Change the error message displayed when a SQL single-line comment that is not terminated with a line break and that doesn't include a space is inserted at the end of a definition query SQL expression applied to a mobile geodatabase feature class from "Fields do not match." to "The SQL expression has invalid syntax.".

ENH-000156189 - ArcGIS Pro - Add documentation that outlines how to add single-line and multi-line comments to a definition query SQL expression applied to a mobile geodatabase feature class.

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

gpgisandsky — Tue, 30 Jan 2024 09:37:04 GMT

Hi Bud,

you have to complete the sql string with ";" then write your comment like these examples:

It's works on Pro 3.1.4. thank you

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

JoshuaBixby — Tue, 30 Jan 2024 15:32:00 GMT

Given that a semicolon has been the <SQL terminator> since the first SQL standard, and syntax rules have called for using a <SQL terminator> since the first SQL standard; this is definitely the correct answer. In fact, I think addressing some of the logged defects and enhancements would make the situation worse rather than better.

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

Bud — Tue, 30 Jan 2024 16:09:37 GMT

Regarding a single-line comment at the end of the SQL:

I tried using a semicolon in ArcGIS Pro 3.2.1 and a mobile geodatabase. But I couldn't get it working.

What am I doing wrong?

Video:

(view in My Videos)

The test in the video uses Select By Attributes. But, I see the same issue when doing a similar test in a definition query.

I also have the issue when using an Oracle 18c table in Pro 3.2.1 (unversioned).

Video:

(view in My Videos)

Am I having a brainfart?

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

gpgisandsky — Tue, 30 Jan 2024 19:08:20 GMT

Hi Bud,

maybe:

a) definition query

b) query filter

c) select by attribute

call the SQL in different way. The semicolon how <SQL terminator> and the comment and end of line (---pluto) or at a new line (/*paperino*/) works fine into a) and b) but in c) only <SQL terminator> is the cause of the error (I tested it on ArcGIS Pro 3.1.x with file geodatabase). The behavior you described could be related to how ArcGIS Pro processes SQL internally. May I ask you why you want put the comment into a temporarly query like select by attribute?

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

Bud — Tue, 30 Jan 2024 20:14:54 GMT

Definition queries are the main use case. I just happened to test in Select By Attributes because I thought it would behave the same way.

I couldn't get a semicolon to work in a definition query in ArcGIS Pro 3.2.1 / mobile geodatabase:

There was an error with the expression.

What do you mean by "query filter"?

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

gpgisandsky — Tue, 30 Jan 2024 20:31:38 GMT

Hi Bud, for mobile geodatabase don't put semicolon but try to put only this kind of comment at the end of line or at new line: /*pippo*/ likes :

it's works on my ArcGIS 3.1.x and mobile geodatabse. This confirm that ArcGIS work in different way on different format (file, personal gdb ...) too.

About "What do you mean by "query filter"?" I would say query filter in "display filter"

have a good day.

Re: Comments in SQL expressions — Single-line comment at and of statement causes error

Bud — Fri, 02 Feb 2024 18:44:46 GMT

for mobile geodatabase don't put semicolon but try to put only this kind of comment at the end of line or at new line: /*pippo*/

Yeah, I think I already mentioned that technique in the original post.