Simpler way for Auto Completion / Quoted Identifiers / Syntax Highlighting?

I've recently started some Cognos Development, and the SQL dump uses quoted identifiers, which doesn't play nice with DBVis' Auto Completion for me. My initial attempt at a work around was to simply use the search and replace dialog using regular expressions. I noticed that the Replace dialog does not support backreferences when choosing the RegExp option and read the suggested work-around of macro recording listed here http://www.dbvis.com/forum/thread.jspa?messageID=12825 Yet I'm not confident the macro would be an efficient work-around for hundreds of lines of SQL. Is there perhaps a way to have Auto-Complete recognize Quoted Identifiers? As it stands, I'm simply using a third party application to run a very basic the regex tr/\"(\w+)\"/$1 type concept. So it is far from a game-breaker, its simple extra steps that could be bound to a hot-key like the Upper / Lowercase / format functions. My current work-around,includes the following steps: 1. Get the 'native sql' from the Cognos Environment, 2. copy to clipboard, 3. paste into regex tool, 4. add my basic remove quotes regex 5. copy results to clipboard 6. paste into DB Visualizer Window. This is advantageous to my in several ways. In addition to regaining Auto-complete, I can also make use of the the color syntax highlighting that allows me to quickly and visually identify table / column references verses variables. (My current schema has keywords in Orange, Table / Columns in Yellow and variables / literals in Red which makes hard-codes etc simple to spot and allows me to then replace them with db visualizer's parameter syntax to explore several scenarios / run describe / explains etc.) Alternately, a 'run external tools' extension similar to what Visual Studio / Eclipse and other Development Environments have in lieu of a full extension API might be handy. Suggestions welcome.

Hi Andre, Thanks for reporting this. I found one case where quoted identifiers doesn't work with auto-completion, namely if you want to get a list of columns for a table and the table identifier is quoted, e.g. select * from "AAA" where "AAA". This will be fixed in the next maintenance release. Please confirm if this is the same case as you have encountered or if you have found other cases where quoted identifiers cause problems. Best Regards, Hans

Thank you for your prompt reply. That is indeed the issue for auto-completions. The second issue is that there seems to be no way to visually differentiate a quoted table identity or column / field from a literal value. See below for a visual of this over-simplified SQL to note the ability to quickly identify literal values verses Tables / fields. In the simple example below it doesn't really make too much of a difference, but when the output contains combinations of actual fields, derived tables, unions etc. inside hundreds of lines of auto-generated code, the impact becomes much more stark. I'll mark this question as answered since one of the issues is slated to be fixed, but would love to know if the third-party component to enable RegEx / Replace mentioned in the original linked post has seen any progress or interest and add my vote for it. The Regular Expression Replace RFE would still be required for my particular Use-Case as even with functional auto-completions, I will lose the power of simple color recognition. (Bracketed Identifiers seem to have the same auto-completion issue.) (Adding a link to a matching screen shot in case the below is displayed as plain text.) Screen Shot {color:#CC99FF}/** Quoted Identifier from typical Cognos SQL export Auto-complete unavailable Fields / Tables / Literal Values all look the same since all are controlled by 'Tools -> General -> Appearance -> SQL Editor Styles -> Quoted Literal' **/{color} {color:#FF9900}SELECT {color}{color:#0000FF}"MONTHS.DAYOFWEEK"{color} {color:#FF9900}FROM {color}{color:#0000FF}"MONTHS"{color} {color:#FF9900}WHERE {color}{color:#0000FF}"MONTHS.NAMEOFDAY"{color} {color:#33CCCC}={color} {color:#0000FF}'SUNDAY'{color}{color:#33CCCC};{color} {color:#CC99FF} /** Highlighting with Non-Quoted Identifiers Auto-complete unavailable Literal Values all look are controlled by 'Tools -> General -> Appearance -> SQL Editor Styles -> Quoted Literal' Tables / Columns Controlled by 'Tools -> General -> Appearance -> SQL Editor Styles -> Normal' **/{color} {color:#FF9900}SELECT {color}{color:#99CC00}MONTHS.DAYOFWEEK{color} {color:#FF9900}FROM {color}{color:#99CC00}MONTHS{color} {color:#FF9900}WHERE {color}{color:#99CC00}MONTHS.NAMEOFDAY{color} {color:#33CCCC}= {color}{color:#0000FF}'SUNDAY'{color}{color:#33CCCC};{color}

Hi Andre, > That is indeed the issue for auto-completions. Thanks for confirming. > The second issue is that there seems to be no way to visually differentiate a quoted table identity or column / field from a literal value. > > See below for a visual of this over-simplified SQL to note the ability to quickly identify literal values verses Tables / fields. In the simple example below it doesn't really make too much of a difference, but when the output contains combinations of actual fields, derived tables, unions etc. inside hundreds of lines of auto-generated code, the impact becomes much more stark. Right, the same style is used for both double- and single-quoted strings. Unfortunately, the number of types that can be distinguished is limited and we are already using all slots. If you use square brackets for identifiers, you can get different colors for identifiers and quoted strings. > I'll mark this question as answered since one of the issues is slated to be fixed, but would love to know if the third-party component to enable RegEx / Replace mentioned in the original linked post has seen any progress or interest and add my vote for it. I just asked them about it and they have promised to give it more priority, so hopefully we can get this added soon. > The Regular Expression Replace RFE would still be required for my particular Use-Case as even with functional auto-completions, I will lose the power of simple color recognition. (Bracketed Identifiers seem to have the same auto-completion issue.) The auto-completion issue for bracketed identifiers will be fixed along with double-quoted identifiers in the next maintenance release, but there are many uses for a fully functional regular expression replace so we are still pushing for that. Best Regards, Hans