[SA 9.0.162 Pro] First object selection/snippet bug?

[SA 9.0.162 Pro] First object selection/snippet bug?

There are default snippets like "up"

	Code:
	UPDATE $OBJECT$ SET $COLUMNS$ = @$COLUMNS$ WHERE\|

The first time I select a table and arrow over to expose the columns, there are no checkboxes to select $COLUMNS$ until you back out or re-execute the macro.

http://screencast.com/t/xzBPLzQZG

This seems repeatable when you close out of your target editor and re-open, it's forgotten which objects had check boxes next to them next time you go to use the Macro/Snippet.

This is another snippet which expects you to select a table name, not a column selection.

This is an intended effect from some enhancements to $OBJECT$ macro in version 7.something allowing column selection instead of the intended use of $COLUMNS$ macro for such applications. As a result, it leads to unnecessary confusion and misuse.

This is pretty much analog to one that involves cursor you can see here.

	Code:
	UPDATE $$ DECLARE @object SYSNAME = '$OBJECT(table, view, mview, tblFunc)$'; SELECT CASE WHEN PARSENAME(@object ,3) != '' THEN PARSENAME(@object ,3) + '.' + PARSENAME(@object ,2) ELSE PARSENAME(@object ,2) + '.' + PARSENAME(@object ,1) END; $$ SET $COLUMNS$ WHERE\|

Though I've seen many people objecting this form of UPDATE below, I still prefer it to the former above:

	Code:
	UPDATE u SET "u."$COLUMNS(vertical,updatable,nokeys)$" = " FROM $$ DECLARE @object SYSNAME = '$OBJECT(table, view)$'; SELECT CASE WHEN PARSENAME(@object ,3) != '' THEN PARSENAME(@object ,3) + '.' + PARSENAME(@object ,2) ELSE PARSENAME(@object ,2) + '.' + PARSENAME(@object ,1) END; $$ AS u\| WHERE 1 = 1 AND "AND u."$COLUMNS(vertical,keys)$" = "

EDIT:
Or a shorter version

	Code:
	UPDATE u SET "u."$COLUMNS(vertical,updatable,nokeys)$" = " FROM $OBJECT(ins_schema)$.$OBJECT(ins_object)$ as u WHERE 1 = 1 AND "AND u."$COLUMNS(vertical,keys)$" = ";

now that I've figured out how $OBJECT$ with its different options works.

It allows joining tables right away.

EDIT:
Unfortunately, I've found no solution yet for the case where the WHERE part breaks when you select columns instead of table/views.

Last edited by gemisigo on Wed Oct 12, 2016 5:37 am; edited 1 time in total

Re: [SA 9.0.162 Pro] First object selection/snippet bug?

	Mindflux wrote:
	The first time I select a table and arrow over to expose the columns, there are no checkboxes to select $COLUMNS$ until you back out or re-execute the macro. This seems repeatable when you close out of your target editor and re-open, it's forgotten which objects had check boxes next to them next time you go to use the Macro/Snippet.

This one is interesting. At first I thought the two popups (the first that comes from executing the snippet, and the second one that comes from 'arrowing' back and forth are different, but it does somehow remember which objects had their checkboxes enabled. Very quirky effect (side-effect?). Any hints on its cause?

	SysOp wrote:
	This is another snippet which expects you to select a table name, not a column selection. This is an intended effect from some enhancements to $OBJECT$ macro in version 7.something allowing column selection instead of the intended use of $COLUMNS$ macro for such applications. As a result, it leads to unnecessary confusion and misuse.

Well the whole point of sql assistant is to boost efficiency. Just like the cfetch post I'm not updating entire rows of data with update statements. Just certain columns in those rows. So if the macro needs adjustment perhaps a cursory look by your dev team to fix macros that allow for other than intended selection is needed ?

I don't think using the snippet and then having to cull 30 columns from the statement because I'm not supposed to pick columns but an entire table makes any sense.

I guess the factory default snippets that come installed with SA were written long ago, way before the different macros became a more powerful versions with extra functionality. With that said, yes, the default snippets definitely could use a good facelift :)

What's more confusing is I updated the 'del' snippet to:

	Code:
	DELETE FROM $OBJECT(table)$ WHERE

This works here.. it lets me drill down to columns, but I can't pick them or hit enter on them or anything..

But $OBJECT(table)$ doesn't work in the 'up' snippet. It ignores it, probably because the $COLUMNS$ macro is in the same snippet?

Either way Sysop is right, it's confusing and allows for misuse. I think something needs tweaking somewhere. If it's meant you should only select a table, then $OBJECT(table)$ should work, and to take that even further it shouldn't allow you to drill down or 'open' the table to even look at columns since that's not the intended use for that particular snippet/macro code.

	gemisigo wrote:
	I guess the factory default snippets that come installed with SA were written long ago, way before the different macros became a more powerful versions with extra functionality. With that said, yes, the default snippets definitely could use a good facelift :)

That's exactly the case.

	Mindflux wrote:
	something needs tweaking somewhere.

Absolutely, I'd say to me it's not just confusing, it's very confusing indeed. I've located and updated an existing ticket to boost the priority of this issue.

	Mindflux wrote:
	If it's meant you should only select a table, then $OBJECT(table)$ should work, and to take that even further it shouldn't allow you to drill down or 'open' the table to even look at columns since that's not the intended use for that particular snippet/macro code.

If I recall correctly, it was working that way in the past, it did not allow you to open the table (they did not have the + and - to expand and collapse them). Though I couldn't say when that one got broken.

gemisigo wrote:

	Mindflux wrote:
	If it's meant you should only select a table, then $OBJECT(table)$ should work, and to take that even further it shouldn't allow you to drill down or 'open' the table to even look at columns since that's not the intended use for that particular snippet/macro code.

If I recall correctly, it was working that way in the past, it did not allow you to open the table (they did not have the + and - to expand and collapse them). Though I couldn't say when that one got broken.

I've got 6.5 installed on a VM, I just tested it and it still has the +'s to expand.

One difference is when I pick a column from the list, it still brings in the entire table instead of just the selected column and the poor syntax on the table name.

http://screencast.com/t/ssWzgyQlb4

So at least there it still works mostly 'as intended'.

I tried setting the 'up' snippet to use $OBJECT(table)$ but it didn't change anything about how the popup menu worked.

Thank you for your help with troubleshooting this case. I think we got a good grip on the issue and looking for options to resolve it gracefully with no or very minimal side effects.

An addition: I guess we all agree that column selection should only be allowed if explicitly said so by specifying the column option in the macro. Note though, that $OBJECT$ macro with that option returns the list of selected column names instead of the schema and the object names for both ins_schema and ins_object. That should be changed as well.

	gemisigo wrote:
	An addition: I guess we all agree that column selection should only be allowed if explicitly said so by specifying the column option in the macro. Note though, that $OBJECT$ macro with that option returns the list of selected column names instead of the schema and the object names for both ins_schema and ins_object. That should be changed as well.

Wouldn't changing this to "with schema name" or something do that?

Nope. That should change how objects retrieved using $OBJECT(...)$ with ins_qualname (at least, that's what I think after reading the help). Unfortunately, for me it always qualifies objects with database and schema name, regardless of the setting mentioned.

Thank you. We are working on updating macro variables to make them better backward compatible with the old snippets and somewhat make them a bit smarter too. Based on notes I see in the support system, the latest public build 9.0.166 allows using column names in snippets like cfetch, so that individual columns can be selected in addition to an object name. The behavior of upd and some other legacy snippets hasn't been affected yet, the snippets like that expect object name selection and cannot process column selection correctly. We are working on enhancing that snippet/macro behavior.