$OBJECT$ macro anomaly

When I type a code manually like :

	Code:
	INSERT INTO myscheme.table1 ( column1, column2 ) VALUES ( '1', 2 )

SQL Assistant helps entering 'myscheme', I press '.' and then it helps entering 'table1'.

If I want the same but using 'iv' snippet, the popup shows 'myscheme' but when I press '.' it disappears. It does not completely close because pressing backspace makes it re-appear and I can select the table with either with mouse or cursor keys+enter but typing other letters (namely the table I'd like to insert into) makes it vanish and writes to the editor. Auto Complete is set to Always Fully Qualify Object Names With schema name. Should it not qualify the object with schema name other than the default?

Typing the table name without the scheme works normally, but that restricts to tables of the default scheme.

If I get it correctly, you are not using the popup properly, you are selecting one thing at a time, while you could be selecting everything in 1 go. In the popup, instead of selecting "myschema", you should select the required table. If you are using mostly keyboard, select the schema name, press Arrow Right key to expand the schema level, scroll down and select the table or just start typing the table name, that will insert the complete schema.table name. If you are using mostly mouse, click the little [+] sign in front of the schema name to expand that level, scroll down and click on the required table name.

Hope that helps.

SysOp wrote:

If I get it correctly, you are not using the popup properly, you are selecting one thing at a time, while you could be selecting everything in 1 go. In the popup, instead of selecting "myschema", you should select the required table. If you are using mostly keyboard, select the schema name, press Arrow Right key to expand the schema level, scroll down and select the table or just start typing the table name, that will insert the complete schema.table name. If you are using mostly mouse, click the little [+] sign in front of the schema name to expand that level, scroll down and click on the required table name.

Hope that helps.

I'm sorry, it seems that I was not precise enough. My problem is that when entering 'INSERT INTO' manually I can select everything in one go by typing schema name, typing '.' (the popup remains), typing table name and pressing enter. When using 'iv' snippet, after typing the schema name I cannot use the keyboard to narrow down the possible table names because after pressing '.' the popup window disappears. I'm not selecting 'myschema', I'm just pressing '.' I can do it in one go if I use either mouse or cursor keys + enter but that's a bit cumbersome when there are dozens of tables in that schema.

	Quote:
	My problem is that when entering 'INSERT INTO' manually I can select everything in one go by typing schema name, typing '.' (the popup remains), typing table name and pressing enter.

This is a visual illusion. When you type '.' the first popup disappears inserting schema name and dot and then another popup appears right away. So there are 2 separate popups with the schema name inserted into the code before the second popup . Similarly if you try that for an object in a different database typing '.' between name parts, there will be 3 popups, 1 - db name inserted, 2 - schema name inserted, and 3 - finally table name inserted.

	Quote:
	When using 'iv' snippet, after typing the schema name I cannot use the keyboard to narrow down the possible table names because after pressing '.' the popup window disappears.

That cannot be done the same way for a code snippet, because nothing can be inserted into the editor until the entire snippet execution is complete. Indeed, the result of the $OBJECT$ macro can be consumed within the snippet and never inserted into the editor, or may be transformed to something else before being inserted.

I see. So I can use the cursor keys because the popup is really not closed which is not the case when I use '.'. And is it not possible to make a second popup (and a third, if necessary) before the value/content/result of $OBJECT$ is set? Would it break the logic of how it works?

Another question. Is there a way to 'decompose' the result of $OBJECT$? I mean something like $OBJECT$.name or $OBJECT$.schema? I intend to create a snippet for altering procedures/functions that retrieves the routine code and it would be essential to be able to filter it by schema.

	Quote:
	Is there a way to 'decompose' the result of $OBJECT$? I mean something like $OBJECT$.name or $OBJECT$.schema

Yes, there is. You can create custom macro $..$ and reference $OBJECT$ inside that macto. for example (T-SQL example):

	Code:
	DECLARE @full_name SYSNAME DECLARE @schema_name SYSNAME SET @full_name = '$OBJECT$' SET @schema_name = left(@full_name, charindex('.' @full_name) - 1) SELECT @schema_name

Similarly

	Code:
	DECLARE @full_name SYSNAME DECLARE @object_name SYSNAME SET @full_name = '$OBJECT$' SET @object_name = substr(@full_name, charindex('.' @full_name) + 1, 255) SELECT @object_name

Last edited by SysOp on Fri May 28, 2010 9:09 am; edited 1 time in total

That's excellent! I could have thought about that :) Thanks

Another anomaly, again with $OBJECT$ and snippets, but this time concerning popup. When I trigger a snippet that contains $OBJECT$ and start typing its name, after selecting its schema and pressing the right arrow key to expand the objects under that schema I cannot narrow down the list by typing the name of the object because strange thinsg happen. I'll try to explain with an example.

I have a snippet "dsel" that uses $OBJECT$, a schema named "elektra", and multiple tables, including one that is named "elek" (meaning "edges" in Hungarian). After I type "dsel" and press TAB the popup appears. I begin typing "ele" and now the schema "elektra" is highlighted. I press the right arrow key, and objects under "elektra" are now visible in the popup. But now, if I try to type "d" for the object named "disc" the popup disappears as if there was no object with that name. But if I try to type "k" for the object "kartya" the popup narrows down to object "elek". Just to be sure that this behavior is consistent, I created the meaningless table "elea" and I proved to be right.

It seems that the variable that is used to filter the popup contents does not get reset after switching from the schema to the schema objects as it does with nonsnippet "INSERT INTO". Could you check that, please?

I think I understand what is going on. Let's compare regular "$OBJECT$" popup and the same popup triggered from macro snippet.

With a regular popup which you get after FROM keyword and some other keywords, when you type some text, the text is inserted into the editor and the popup context is filtered to match your entry. When you select schema name and press Arrow Right key, the schema name is inserted into the editor with a dot after the schema name, the schema level disappears (the entire popup) and you can see the next level for tables (second popup in the same place) and you start typing your table name.

With a macro snippet, when you type while the popup is visible, nothing is inserted into the editor until you press the completion key (Enter or Tab). After you press the completion key, the snippet code is executed and your input is used within the snippet. The result of the entire snippet execution is then inserted into the editor. I assume the snippet type is "Insert Output Into the Code" in this case. Please note that in this case when you select schema name and press Arrow Right key, nothing is inserted into the editor yet and you are still working with the schema level popup (first level). You can see tables in the second level, but your input is still applied to the current level - schema names. It doesn't work exactly the same way as in the regular popup, it doesn't do what you expect it to do. However, you can still select table name with a mouse and that will pass the complete object name to the snippet code.

Ohh, I see. That explains it entirely.
Are you going to change that behavior in the future? Or perhaps add a field to the top/bottom of the popup that shows letters typed so far? If I could see the letters at least I'd know what happened. It is a bit misleading to type letter "a" and to have tables filtered by "elea" and selecting purely by up/down/page up/page down is sloooow when having 100+ tables :(

Sorry, I cannot answer that kind of question. If you like, I can add an enhancement request to modify the current behavior and support nested level object name filtering in macros invoked from code snippets

I'd like to ask you to do so. That would be a very versatile addition to snippets.

Thank you. Just in case, this enhancement tracking number is SA-10731