Active Query Builder support area

Preserve Comments in SQL Query

Avatar
  • updated
  • Completed

I would like to follow up this post regarding preserving comments while editing a query in the QueryEditor. There it is stated that this will be implemented in the next major release. Is this still valid?

Avatar
Kelly

Here is a simple SQL using hints for my comments and they "stick".

WITH c1 AS (SELECT /*+ -- CTE comments */ To_Char(:ENVIRO_NAME) AS ENVIRO_NAME,
      To_Char(SYSDATE, 'YYYY-MM-DD') AS RUN_DATE
    FROM DUAL)
SELECT /*+ -- MAIN comments */ c1.ENVIRO_NAME,
  c1.RUN_DATE,
  d0.CURR_DT
FROM (SELECT /*+ -- DERIVED comments */ SYSDATE AS CURR_DT
  FROM c1) d0,
  c1

Avatar
Andrey Zavyalov, PM

Hello, Christian.

I must admit that we haven't done this feature so far. I am terribly sorry for the inconvenience this cause everybody.

The major SQL parser update is delayed. Hopefully, we will get back to this feature in the second half of this year, but I can't promise you this.

Avatar
Christian Gold

Surely you must support comments by now?  Any update on this?

Avatar
Kelly

Any updates on when this will be available? thank you

Avatar
Andrey Zavyalov, PM

It is possible to add a custom column to the QueryColumnListControl, there's a special demo project that illustrates this feature. For now it is not possible to save comments in layout XML, but we'll think about the best way to achieve this.

Avatar
no -name

It would already help us to expose a comment column in the QueryColumnListControl and load/save this information from/to the QueryBuilder.LayoutSql (which is, in fact, an xml). If i understood you correctly, that would leave out the time consuming part of the implementation?

Avatar
Andrey Zavyalov, PM

It's not a problem of finding the place in UI to show comments. The main part of this task is to parse and then insert comments in result SQL query text. This task will need the time that we can't afford to spend now.

Avatar
no -name

We have another approach that we assume is less invasive and easier to implement for you:

Add a "Comment" text-column in the GridView for editing the (sub-)query columns. Store the value of this comment in the LayoutXml.

That could be an intermediate step that would already help us a lot.

Next step could be to parse/render this from/to the final SQL as well, as discussed above.

What do you think?

Avatar
Andrey Zavyalov, PM

Thank you for sharing your thoughts. We will think once again about the possiblity to implement this feature.

 

Oracle hints are preserved right now.

Avatar
no -name

In Oracle, optimizer hints are realized as comments. You may take a look at this documentation. If I didn't miss anything, then it would only require some extensions to the existing SQL parser and no rework. The context of optimizer hints (i.e. to which item of the query they belong) is clearly specified.


At least this special kind of comment is indispensable for us. They reduce execution time by orders of magnitudes.


However, the specification shows a pattern about how to assign comments to an item in the SQL statement. From that pattern you could derive a general approach to assign comments to a part of a SQL statement on a best-effort basis.


Finally, even if you at least collect all comments at one place and render them at the beginning/end of the actual statement would already help us. Everything would be better then just forgetting them. :)