Possible Bug?

AndrewMurphy

When trying to expand * into a column list with the following code, SQL Prompt 3 seems to go a bit too far.
SELECT a.* from Table1 a
INNER JOIN Table2 v=b on a.id = b.id

Pressing [tab] after the *, for me, generates a list of all columns in both tables a+b, rather than just columns in table a - which is what the intended executing code would do.

It also prefixes the 1st column with a double a.a. ....in addition to it being on a different line and at a different indentation level from all the other columns.
I've searched for similar reported behaviour but can't see if it's been reported before.

Bart Read

Hi Andrew,


Sorry you're having problems. That's a very bizarre one since it certainly was working, but I can see us needing to check it out a bit more thoroughly. As far as the indentation goes depending on which editor you're using (I'm assuming it's QA) you'll need to modify the TabSize setting in the EngineOptions.xml file to match the tab size in your editor.

For Management Studio a tab is usually equivalent to 4 spaces, hence the default setting is 4, however Query Analyzer usually defaults to 8 spaces per tab.

We didn't have chance to expose this setting through the UI so what you'll need to do is open EngineOptions.xml in Notepad, or some other suitable editor (but NOT TextPad since this seems to cause issues when reloading the file later), navigate down to the TabSize element, and change the value appropriately. You can do this whilst SQL Prompt is running since it periodically checks to see if the configuration file has been modified.

This file is usually stored in the following directory:

C:\Documents and settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3

If your profile is stored elsewhere obviously the directory will be different.

Hope that helps for now. I'll get back to you about the wildcard expansion issue. Just out of interest what happens if you try to use the column picker instead? (Hit CTRL+Left when the cursor is immediately after the "a.".)


Thanks,
Bart

AndrewMurphy

I've got the functionality (as designed) to work when the ON condition is on a line of it's own after the 2n table...but it doesn't work when the ON condition is on the same line as the 2nd table.

Re CTRL+Left...this just moves the cursor to the before the "a"

Bart Read

Hi Andrew,


Thanks for the extra info. Does CTRL+LEFT have this effect when the completion list is visible?


Cheers,
Bart

AndrewMurphy

Ctrl+LEFT when A.* is already typed as part of a larger snippet of code works as "put cursor before a"
whereas
Ctrl+LEFT when A. is being typed works as "change selection/dropdown" list".

re the conversion of * into a "list of all columns"...what I get is column 1 on the line starting where the * was and all other columns on their own independant line. For consistency it would be useful to have all columns on their own lines.

I note also that indentation reported earlier that the indentation is 2x the "TabSize setting + 1" ....ie at position 18...whereas I would assumed it would be at the same position as the starting character being replaced ie position 8 (ie the length of "SELECT a") (I'm using QA).

Finally...when converting "a.*" (no join) to the list of all columns, the "a." bit is not prefixed in front on each column...other than column 1 (by residual effect more than anything else)...if this could be fixed (or become an optional setting) this would be an improvement.

Bart Read

Hi Andrew,


Thanks for getting back to me again. Can you confirm whether the query you posted at the start is actually correct because there's a fairly heinous syntax error on the second line that would almost certainly cause the parser to fail and hence the alias auto-expansion to also fail:

SELECT a.* from Table1 a
INNER JOIN Table2 v=b on a.id = b.id

The "v=b" is the bit that worries me.


Thanks,
Bart

AndrewMurphy

oooops!!!

"v=b"....typo...I was pasting (Ctrl-v) + bolding (Ctrl-b)....must have missed the ctrl key! (twice)

Andrew

Bart Read

OK thanks, I just wanted to check that wasn't in the original query. As I say, we'll take a look into it and see what's going on.
Thanks,
Bart