Log in to AnswerLog in to Follow
2018-09-07 11-54-27
Eric Oud Ammerveld
479Views
17Comments
Solved
Bug Report: SQL Commenting that screw up queries
Question
Hi All,

Just as a word or warning for people creating advanced queries and that use COMMENTS in those queries.
There is a bug in the SQL processor that makes the double hyphens used for commenting disable every statement AFTER the hyphens.


Make sure not to use the two hyphens to place a comment.
Outsystems will "ignore" the linefeeds behind it and concatenate everything into one row.
1
0
12 Apr 2016

Rafael Valente wrote:

Hello there,


You can use the "--" for comments as long as you don't use it inside the whole SELECT statement.


Best regards,

Rafael Valente

Yeah that was kindof allready known...

This is a BUG in the SQL parser; period.

Outsystems should parse the part having -- to make sure they don't remove the linebreak from that line before they remove all CR/LB to "minify" the query and replace all variables with the input parameters.

-- is a perfectly good single line comment indicator

https://msdn.microsoft.com/nl-nl/library/ms181627.aspx


Quote: "Comments inserted with -- are terminated by the newline character."

So don't remove the newline there; it's there for a reason.




See solution in context
3
0
21 Nov 2016
2020-09-15 13-07-23
Kilian Hekhuis
mvp_badge
MVP
This is absolutely true, and very valuable advise, but this has been the case since always (well, at least 4.2 which was the first version I worked with). The cause is that the Platform will put everything on a long line, before sending it to the database (at least that what I recall some OutSystems engineer writing here a while ago). You really mean you recently encountered this for the first time?
1
0
13 Apr 2016
I too encountered same problem one year before hence avoiding comments in Advance Queries.
1
0
13 Apr 2016

Hello there,


You can use the "--" for comments as long as you don't use it inside the whole SELECT statement.


Best regards,

Rafael Valente

0
0
21 Nov 2016

Rafael Valente wrote:

Hello there,


You can use the "--" for comments as long as you don't use it inside the whole SELECT statement.


Best regards,

Rafael Valente

Yeah that was kindof allready known...

This is a BUG in the SQL parser; period.

Outsystems should parse the part having -- to make sure they don't remove the linebreak from that line before they remove all CR/LB to "minify" the query and replace all variables with the input parameters.

-- is a perfectly good single line comment indicator

https://msdn.microsoft.com/nl-nl/library/ms181627.aspx


Quote: "Comments inserted with -- are terminated by the newline character."

So don't remove the newline there; it's there for a reason.




3
0
21 Nov 2016
2020-09-15 13-07-23
Kilian Hekhuis
mvp_badge
MVP

Yup, it's a bug allright - apparently just not one that's serious enough to fix :).

1
0
22 Nov 2016

 @Eric,

It's kind of already known for people who work with SQL for a while. For newcomers that can be a heads up.


@Killian,

I agree with you. Unfortunately, all bugs should be serious enough to fix but i can understand that they have major things to be worried.


Best regards,

Rafael Valente


1
1
22 Nov 2016

Rafael Valente wrote:

 @Eric,

It's kind of already known for people who work with SQL for a while. For newcomers that can be a heads up.


@Killian,

I agree with you. Unfortunately, all bugs should be serious enough to fix but i can understand that they have major things to be worried.


Best regards,

Rafael Valente


Hi Rafael,


I initiated the post as a word of warning to all Outsystems developers and a kind note to the Outsystems Devteam that they have a serious error which is in my humble opinion easy to track and kill.

Using -- in Outsystems syntax highlights to commented OUT until the newline as it should and as it is interpreted by MSSQL.
Hence; it is an indication that this way of commenting is supported.
The usage of it however leads to unexpected results if you expect it to comment only a single line.

The fact that SQL coding is actually changed is something that should be considered since i.e. would you actually use a newline character in your SQL the Outsystems interpreter will remove that.

The idea of bug reporting is providing feedback and expect it to be followed up whenever it would be suitable for Outsystems.
A confirmation from Outsystems that the bug is noted would be nice on this topic since until then developers will encounter this issue.

1
0
22 Nov 2016

Hi guys,

Can you tell me which version you're using and provide me a simple module where you can reproduce this problem?


Best regards,

Miguel Rebelo

0
0
22 Nov 2016

Miguel Rebelo wrote:

Hi guys,

Can you tell me which version you're using and provide me a simple module where you can reproduce this problem?


Best regards,

Miguel Rebelo

Hello Miguel,

I'm using version 9.1.0.603 of Service Studio. Is that version that you are referring to?


Best regards,

Rafael Valente

0
0
22 Nov 2016

Hi Rafael,

Can you tell me also your platform server version? 

I'm trying to reproduce the behavior you described in the most recent version of our product but without success. Do you have a module that I can use to reproduce it?

Thanks

0
0
22 Nov 2016

@Miguel,


PS is 9.1. Unfortunately, i don't have any simple module available for these versions because i was aware of this in a project.

Sorry for that.

I tried to replicate this issue on version 10 and couldn't do it.

Hopefully they solved this in this version.


Best regards,

Rafael Valente

0
0
22 Nov 2016

I tried the most recent versions of 10 and 9.1 and both worked fine. 

@Killian, @Eric, in which platform version you're getting that behavior? (full version number, e.g. 9.1.603.0)


Regards,

Miguel Rebelo

0
0
22 Nov 2016

Hi guys,

I was finally able to reproduce the problem. The bug occurs when the line with the comment doesn't start with the "--" (e.g. empty spaces before the comment or comments in the end of the SQL lines).

Thanks for reporting it.

As workaround you can put the comments in new lines that starts with "--" or use multi-line comments (/* ... */).


Best regards,

Miguel Rebelo

0
0
22 Nov 2016

Miguel Rebelo wrote:

Hi guys,

I was finally able to reproduce the problem. The bug occurs when the line with the comment doesn't start with the "--" (e.g. empty spaces before the comment or comments in the end of the SQL lines).

Thanks for reporting it.

As workaround you can put the comments in new lines that starts with "--" or use multi-line comments (/* ... */).


Best regards,

Miguel Rebelo


Yes, that is correct, actually it used to be worse (as my example wasn't working on an older version)

0
0
22 Nov 2016
2020-09-15 13-07-23
Kilian Hekhuis
mvp_badge
MVP

If I'm not mistaken, any line with a -- comment not starting at the first position in a line caused this behaviour (so also e.g.

SELECT * FROM {MyTable} -- Yes, it's *my* table!
WHERE {MyTable}.[ForeignId] IS NULL

would cause to ignore the WHERE-clause.

0
0
22 Nov 2016

Old thread still broken in OS 10 runtime, however when using ServiceStudio SQL Test operation, the -- comments work without issue.

0
0
25 Jan 2018

I noticed today two issues concerning commenting:

1. SQL modeler shows all text after a /* as commented, even when you don't end with a */

( of course you should close it.. though visually you get the wrong feedback and when running a test, it will keep running and even use all your app server cpu to try to generate SQL. Beware!)


2. When using special characters or {entityName} etc. in a commented line, it can cause the compiler to report issues in your sql statement. The generated SQL shows that it is also trying to convert commented items that are   identified as a string, entity or such.


I will not make these mistakes again but was staring at my SQL far too long before I realized the comments where causing the issue... Extra heads up or validation by OS would be a time saver for new entrants. :-)


0
0
29 Jan 2019
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.