SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.162 Pro] First object selection/snippet bug?
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.162 Pro] First object selection/snippet bug?
Author Message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post [SA 9.0.162 Pro] First object selection/snippet bug? Reply with quote
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.
Tue Oct 11, 2016 5:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Wed Oct 12, 2016 2:09 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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
Wed Oct 12, 2016 3:53 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Re: [SA 9.0.162 Pro] First object selection/snippet bug? Reply with quote
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?
Wed Oct 12, 2016 4:49 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Wed Oct 12, 2016 8:22 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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 :)
Wed Oct 12, 2016 8:31 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Wed Oct 12, 2016 9:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Wed Oct 12, 2016 9:47 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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.
Wed Oct 12, 2016 10:01 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Wed Oct 12, 2016 10:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Wed Oct 12, 2016 2:51 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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.
Mon Oct 17, 2016 5:23 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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?
Mon Oct 17, 2016 9:02 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
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.
Mon Oct 17, 2016 9:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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.
Mon Oct 17, 2016 9:47 am 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
Goto page 1, 2  Next
Page 1 of 2

 
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.