[VSCODE] SQLFlavourOverride: db2 .... Gets error on "#" character, even though db2 allows for it

[moymike]

I'm working with DB2 (iSeries) SQL statements. One of the primary databases I work with includes "#" within the library name.

Example:
select * from db#prod.pohead where ponum = 1111;

Format Document error
Unable to format SQL: Error: Parse error: Unexpected "#prod.pohead" at line 99 column 99

If I use the IBM Client Access SQL tool, the format works fine. It appears that the the "Prettier-SQL.SQLFlavourOverride": "db2" is not allowing character "#" in the library name even though it is valid.

[nene]

Thanks for reporting.

Could you provide a link to DB2 documentation that specifies which characters are allowed in database names?

I've been following this document, which only mentions:

An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character.

Apparently that's not the full story, but I'm not familiar enough with DB2 to know from where to look for better sources.

[moymike]

In DB2 the database tables are stored as LIBRARY.FILE. (Lib.PF or Lib.LF) The naming of libraries (and library items) allow for A-Z, @, $, #, 0-9, underscores (_), and periods (.).

(oh... and just noticed ... the periods (.) character would NOT work for sql, but apparently it can be used in naming)

IBM i library naming rules

Although these special characters are allowed, and a lot of systems contain them, they can be problematic. This likely was not an issue years ago though, when many systems, such as ours was designed.

Db2 object naming rules

I tested another VScode extension "Sql Formatter" which has a "db2" option that works with the "#" library name.

If you update the extension please let me know and I'll try it out. Your extension contains more formatting options.

[nene]

Thanks for the references. I'll see what I can do to fix this.

I tested another VScode extension "Sql Formatter" which has a "db2" option that works with the "#" library name.

There seem to be many VSCode extensions named "SQL Formatter", all of them being wrappers around this same library, though often using an outdated version. The older versions of this library were more willing to treat all kinds of text as valid SQL.

[nene]

This issue should be fixed now in sql-formatter 12.0.6.

Not sure though when the fix will make it to the Prettier-SQL extension.