SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Table and other obj don't work when contains symbol space

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Table and other obj don't work when contains symbol space
Author Message
X-Cite!



Joined: 28 Jan 2019
Posts: 5
Country: Belarus

Post Table and other obj don't work when contains symbol space Reply with quote
We use Microsoft SQL Server Management Studio 14.0.17289.0 and Sql Assistant 10.0.187 Professional Edition
Table and other objects don't work when contains symbol "space"
We have next tables
[Some-Soft RU$Country]
[Some-Soft RU$Delivery]
[Some-Soft RU$Sales Order]
[Some-Soft RU$Worksheet Sales Entry]
[Some-Soft RU$Cust. Declaration Relation]
When I type
select * from [ - not working. no tips
select * from Some-Soft - when I type a space, the tips disappear
When i press Ctrl+Space on select * from berk-soft ru$C or select * from [berk-soft ru$C - no tips
Mon Jan 28, 2019 4:50 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
A possible duplicate of this one >>>here<<<. Even though the OP stated it didn't work for him, I'd recommend using the same workaround I did there. I checked the method and it works. You could try that name matching method, at least until there's a fix for this one. It will probably stick even after the fix is available.
Mon Jan 28, 2019 8:42 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
FYI, version 10.1 is going to be released this week. I expect it to support cases like this one.
Mon Jan 28, 2019 10:51 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
Please try the latest version 10.1.246. It's now available for download or can be upgraded directly using Check for Update menu.
Wed Jan 30, 2019 10:23 am View user's profile Send private message
X-Cite!



Joined: 28 Jan 2019
Posts: 5
Country: Belarus

Post Reply with quote
Thank you, it's already better, but

We have next tables
[SalesOrder]
[SalesReturn]
[RequestDone]
[Some-Soft RU$Country]
[Some-Soft RU$Delivery]
[Some-Soft RU$Sales Order]
[Some-Soft RU$Worksheet Sales Entry]
[Some-Soft RU$Cust. Declaration Relation]

When I type

select * from Some-Soft - when I type a space, the tips WORKING - IT'S OK.

When i press Ctrl+Space on select * from Some-Soft RU$Worksheet Sal - invalid tips. Is displayed SalesOrder and SalesReturn. Necessary only [Some-Soft RU$Worksheet Sales Entry]
When i press Ctrl+Space on select * from Some-Soft RU$Works - no tips.
When i press Ctrl+Space on select * from Some-Soft R - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from Some-S - invalid tips. Is displayed SalesOrder and SalesReturn

and with escape symbols
When i press Ctrl+Space on select * from [Some-Soft RU$Worksheet Sal - invalid tips. Is displayed SalesOrder and SalesReturn
When i press Ctrl+Space on select * from [Some-Soft RU$Works - no tips.
When i press Ctrl+Space on select * from [Some-Soft R - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from [Some-S - invalid tips. Is displayed SalesOrder and SalesReturn
Fri Feb 01, 2019 10:20 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
I think I understand why you get what you get after Ctrl+Space. None of these case represent valid SQL syntax or syntaxically complete and guessable object name. The parser looks forward and looks back, and stops after the first space, it has no way of knowing how many words back it needs to scan to find something meaningful. Otherwise it would produce lots of false positive cases. You know the names only because you know what to expect. It doesn't know what to expect, it cannot help you with that. But if you add closing bracket ] after a complete name, then I expect it to work as it would look for a matching opening [ and then can match the object and help you with column names and other things.
Fri Feb 01, 2019 1:33 pm View user's profile Send private message
X-Cite!



Joined: 28 Jan 2019
Posts: 5
Country: Belarus

Post Reply with quote
When the cursor is before the closing ]
When i press Ctrl+Space on select * from [Some-Soft RU$Worksheet Sal] - invalid tips. Is displayed SalesOrder and SalesReturn
When i press Ctrl+Space on select * from [Some-Soft RU$Works] - no tips.
When i press Ctrl+Space on select * from [Some-Soft R] - invalid tips. Is displayed RequestDone
When i press Ctrl+Space on select * from [Some-S] - invalid tips. Is displayed SalesOrder and SalesReturn
Mon Feb 04, 2019 3:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
The Ctrl+Space forces the popup, but there are 3 possible outcomes

1. White-space with DML statement keyword before: The cursor is after a white space. It scans text backward and finds preceding word. If the word s a recognized keyword which is part of a DML statement , like SELECT, WHERE, etc... then it grabs a bit more text before and after to reconstruct the statement context and depending on keyword shows either object or column list.

2. White-space with DDL or other non-DML statement keyword before: The cursor is after a white space. It scans text backward and finds preceding word. If the word s a recognized keyword which is part of a DDL statement like ALTER, CREATE, GRANT... or something else which is part of DDL and in a middle of it, it shows a list of likely keywords that can be possibly used in that context.


3. Non white space before: The cursor is after an alpha-numeric character: it tries to complete the word basedo n the names of matched objects in the cache. If such objects are found. If that didn't work, it checks for matching column names and if found, checks if there is a following FROM keyword and it can identify the name of the object after it, so it can filter matching columns for that object
only.

In all other cases "No matching .... found" messages are displayed.

In your case, it grabs whatever the word is before and works with that word, trying to complete as described above. It has no way of knowing how many more words back it needs to scan. What you get is a result of using non-standard naming conventions. Normally object and column names shouldn't have spaces and special symbols in their names.


Last edited by SysOp on Tue Feb 05, 2019 3:45 am; edited 1 time in total
Mon Feb 04, 2019 9:14 am View user's profile Send private message
X-Cite!



Joined: 28 Jan 2019
Posts: 5
Country: Belarus

Post Reply with quote
Quote:
Normally object and column names shouldn't have spaces and special symbols in their names.


Tell it to the developers of "Microsoft Dynamics Navision". Where most tables, by default, have spaces and special characters.

May be add support escape symbols?
select * from [Some-Soft RU$Worksheet Sal]
if the cursor position is immediately before the "]", then find the "[" and show the correct tips.
And also support the correctness of the escape symbols in table name.
e.g.
select * from [Some-Soft RU$Work[she]]et Sal][/quote]
Mon Feb 04, 2019 11:54 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
I'm really happy to submit an enhancement request to somehow improve that function, but I personally struggle how to formalize the rules and describe them.

Here is a silly example, I use ending | to indicate the cursor position

select * from [Some-Soft RU$Worksheet S|

In the above case, if Ctrl+Space is pressed, the preceding "word" starts with S. If there are any tables starting with S and I'm pretty sure there should be plenty of sys... objects, how would the assistance know that you don't want to complete S names and want "RU$Worksheet S" or "Soft RU$Worksheet S" or "Some-Soft RU$Worksheet S" instead? I see no formal logic there.

You say, find the he "[", but why would it look back for "[" here are other examples that make it really impossible

select * from [Some-Soft RU$Worksheet Sal]
select * from S|

Should it scan back all the text until it finds "[Some-Soft RU$Worksheet Sal] select * from S" and what to do with that?
How about the following?
select * from [Some-Soft RU$Worksheet Sal]
where S|
Should it stop after first space or continue all the way back to "[" or not?


Here is what I would like to suggest. If you know that most of your tables have the same prefix in their name like "Some-Soft RU$Worksheet " you can develop a code snippet with a custom hotkey assigned to it that would grab several tokens back from the current cursor position, piece them together and complete the name. Here is a generic template, I haven't actually tried it

Code:
$$SELECT TOP 1 name FROM sys.objects WHERE type = 'U' AND name LIKE '$CURRENT(param5)$-$CURRENT(param4)$ $CURRENT(param3)$%_$CURRENT(param2)$ $CURRENT(param1)$%'$$



This is not a very trivial exercise, but it might work. If snippets don't provide enough flexibility, perhaps you can develop a plugin for custom code scan and complete (the Plugin IDE is included with Pro Edition)


Last edited by SysOp on Tue Feb 05, 2019 1:57 pm; edited 1 time in total
Tue Feb 05, 2019 4:26 am View user's profile Send private message
X-Cite!



Joined: 28 Jan 2019
Posts: 5
Country: Belarus

Post Reply with quote
I propose such a rule:
Add escape symbols to config. For MSSQL are [ and ]
If after pressing Ctrl + Space immediately after the cursor is ] algorithm such:
1) Find count ] to the right of the cursor but a pair of ] (]]) counts as one
2) Then move left and decrement count if found [
3) When moving left if found ] then increment count if it are pair. Else mistake
4) When count = 0 then show tips

Important! This only works if the ] immediately behind the cursor.

Example:
select * from [Some-Soft RU$Worksheet Sel]
On right ] and count = 1. Then move left until [. If count = 0 show Some-Soft RU$Worksheet Sel++++
select * from [Some-Soft RU$Worksheet [Sel]]]
On right ] and count = 2. Then move left unitl [, decrease count and move on until [. if count = 0 show Some-Soft RU$Worksheet [Sel]++++
select * from [Some-Soft RU$Wo[r]]ksheet [Sel]]]
On right ] and count = 2. Then move left unitl [, decrease count (count = 1) and move on until ]. see that pair ]] then increase count (count = 2), move on unitil [, decrease count (count = 1), move on [ and decrease count (count = 0).
Then show Some-Soft RU$Wo[r]ksheet [Sel]+++++

Maybe this only works for MSSQL or my situation.
Tue Feb 05, 2019 5:23 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
The quoting symbols are already configurable for each database. And for MSSQL [ are the default as well as ANSI mandated double quotes, which are just as good. I will ask for the feedback from the developers and revert back to you.
Tue Feb 05, 2019 2:01 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.