SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Accepting enhancement suggestions for new releases
Goto page Previous  1, 2, 3, 4, 5 ... 10, 11, 12  Next
 
This topic is locked: you cannot edit posts or make replies.    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Accepting enhancement suggestions for new releases
Author Message
Larkov



Joined: 06 Oct 2008
Posts: 26
Country: Germany

Post %rowtype Reply with quote
PL/SQL-Developer / Oracle 9.2

After defining a variable in a procedure as

VAR Table%Rowtype;

SQL-Assistant does not open popup after typing "VAR." to show the fields of the table. I think the momentan functionality of Assistant 4 beta is not really thought through.

Larkov
Wed Oct 08, 2008 9:27 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
VAR Table%Rowtype; is not a valid declaration. Please provide more meaningful code fragment so we can assess the situation.
Wed Oct 08, 2008 9:31 am View user's profile Send private message
Larkov



Joined: 06 Oct 2008
Posts: 26
Country: Germany

Post Declaration Reply with quote
In the declaration-part of a procedure I declare a variable like

vAdr T_ADRESS%ROWTYPE;

basing on the rowtype of the table T_ADRESS.

After that I can use it in the code-part like

vAdr.Name := 'Michael';
vAdr.Street := 'Mainstreet 9';
vAdr.Town := 'Hamburg';
etc.

or like

SELECT *
INTO vAdr
FROM T_ADRESS TA
WHERE TA.ID = 4;

The codeassistant of PL/SQL-Developer recognizes that if I type "vAdr." that there are variables behind it and popups the variable-set. I think if I switch off the codeassistant and use SQL-Assistant instead, it should be able to do the same. Or else it is not making sense to use SQL-Assistant if the functionality goes down and not up. :-)

Larkov
Wed Oct 08, 2008 10:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Your code fragment is still invalid. Where is the DECLARE part of this block or if this is a procedure/function where is AS/IS begins? Is there a BEGIN section?

To make it easier for everybody and to save time, please post the complete code
Wed Oct 08, 2008 10:30 am View user's profile Send private message
Larkov



Joined: 06 Oct 2008
Posts: 26
Country: Germany

Post Code Reply with quote
First create a table

-- Create table
create table T_ADRESS
(
NAME varchar2(30),
STREET varchar2(30),
Town varchar2(30),
ID number(11) not null
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table T_ADRESS
add constraint PK_T_ADRESS primary key (ID);


Next start this

-- Created on 08.10.2008
declare
-- Local variables here
vAdr t_adress%ROWTYPE;
begin
-- Test statements here
vAdr{here type a dot and next a popup should popup to show the variables}

end;

You don't have to run any code to test or understand it. It's something during editing. :-)
Wed Oct 08, 2008 10:43 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Ok. now I can understand it.

This is completely inappropriate and unsupported. You expect SQL Assistant to "see" complex references to T_ADRESS table via the indirect definition of it in "vAdr t_adress%ROWTYPE" variable. Basically you are referring to columns of the table using special ROWTYPE syntax. Only a PL/SQL compiler compiling your code can resolve it back to columns of the T_ADRESS table using some internal reference hashes. SQL Assistant is not a PL/SQL compiler, it is a code assistant, reading the text from the editor and data from the database and it can see only stuff referenced directly. See below for details.


To get assistance with columns of T_ADRESS table, type T_ADRESS then type dot character. to get assistance with variable name, in the popup, expand local variables section and you will see "vAdr t_adress%ROWTYPE" in the list. When this item selected, the popup will insert "vadr" into the code.

Hope this helps.
Wed Oct 08, 2008 11:01 am View user's profile Send private message
Larkov



Joined: 06 Oct 2008
Posts: 26
Country: Germany

Post %Rowtype Reply with quote
I try to explain my view of it, but I am not a native English, so please apologize my inability to be clear enough:

The SQL-Assistant reads all information about the tables into cache. The newest version 4 even recognizes in which procedure it is to show local variables and you already accepted the idea of an enhancement that the actual packagevariables can be seen also. So there should not be a problem to show indirect information about %Rowtype-Variables too, because the informations (means: tablefields) are already in cache.

The reason why I think it is nessecary to implement is, that SQL-Assistant tries not only to be an "editor for SQL", but tries to replace the code-assistants of some products. Otherwise you would just need to offer an extra "editor-window" for typing SQL-syntax (like notepad) without recognizing windowclasses of certain applications like PL/SQL-Developer. You would not offer local variables. You would not offer help- and synatxfeatures for PL/SQL-programming.

BUT (and it is a big BUT) f.e. if the code-assistant of PL/SQL-Developer has to be switched off (so the popups do not confuse each other), I would loose some important features, SQL Assistant does not have up to now.
So the only way (and you ARE already on that way with showing local procedure-variables) is, to give the developer the basic features of a codeassistant. We developers are using SQL "inside" PL/SQL in Oracle, so we need also the basic features of a code-assistant.

I think it should not be a problem to implement it, because it is already in Cache.

Larkov
Thu Oct 09, 2008 7:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
It works fundamentally differently from your expectations, in the sense that SQL Assistant performs lexical parsing of the SQL script while the compiler performs syntaxical and grammatical parsing and compilation. SQL Assistant does not care how you define your variable, whether you make a typing error in data type name declaration or not, it doesn't matter. It picks the line with the declaration and adds it to the list. Same with tables, procedural parameters, and other stuff. It a table definition appears directly in the script, it reads its column definitions, line by line and adds them to the internal cache of table column definitions retrieved from the database (just a lexical parsing is done here). Again, if you make a typo in a column data type name, SQL Assistant will still pick it as it doesn't know whether it is a typo or just some new data type declared in some other place or created dynamically, etc…


Theoretically it is possible to look for special %ROWTYPE% syntax and from there try to find the reference to table, then try to find this table in the database, or try to find it in the same script, etc… but %ROWTYPE% is just one case of an indirect reference. Oracle supports many others. For example, you can declare a type mapped to a table row, then declare a variable of that declared type, then type "var." The mentioned type can be declared in a package declaration script and not in a package body script that your work on. There is potentially infinite number of ways how stuff can be referenced indirectly. I'm not even bringing up cases of dynamic SQL.

The bottom line, SQL Assistant currently doesn't support ROWTYPE references. You are welcome to add an enhancement request for supporting parsing and resolving of ROWTYPE references. There is sure some value in it
Thu Oct 09, 2008 10:02 am View user's profile Send private message
Larkov



Joined: 06 Oct 2008
Posts: 26
Country: Germany

Post Ok Reply with quote
Ok, I understand it is not as easy as I hoped to implement.

Please put it on your enhancementlist. :-)
Thu Oct 09, 2008 10:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Will do.
Thu Oct 09, 2008 10:21 am View user's profile Send private message
maxpesola



Joined: 26 Nov 2007
Posts: 51

Post Reply with quote
Sql server management 2005.
If is not possible now, from the second join into a query, after write only another table name and write the "ON" and space, i would like to see as the first join , in other words to see first the foreign keys and "colum name matches" of last table with all the previous tables already joined, then the columns of last table, then the columns of previous tables, then only for last the other joins that you visualize for first now (but now , if i select a join into list , the query resulting is wrong...).
Another request , now after selecting a brackets (also begin/end) if you move into the window with the scrolls vertical or horrizontal the selection is lost, i know that is possible to move to begin or end of brackets clikking on it but if it's possible not to lost the selections...

Then clikking with ctrl on a table, to see the structure of it like the sps
Tue Oct 28, 2008 5:31 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Quote:
it's possible not to lost the selections


This is very difficult to do. Basically SQL Assistant is not part of the editor, it is a monitoring application watching for and reacting to user keystrokes and some mouse events occurring in the edit box of the editor window, I mean the visible part of the SQL code which is the edit control sitting on the window. SQL Assistant only has access to that edit control, and doesn't have access to the internal memory structures of the editor and doesn't have access to all internal events that trigger scrolling and screen redraws. That's why drawing something on the screen which is context dependent and yet sticky is extremely complicated. Moreover, different editors have different internal implementations and work differently making it almost impossible to find a solution that works for all of them.

Quote:
Then clikking with ctrl on a table, to see the structure of it like the sps
-
this is in our enhancements list for future releases. Unfortunately no simple solution exists that could be used in a consistent way for all supported database systems. Yet, there are certain things that you can do now to help yourself. For example, if you work with SQL Server, you can customize the "DDL (MSSQL)" query in SQL Assistant options and change it to the following

Code:
if (select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID) = 'U'
    exec sp_help @objname = :OBJECT_NAME
else
   select text
   from /*db.*/dbo.syscomments
   where id = :OBJECT_ID
   order by colid


This will return either table structure or procedural code when you use View Procedure Code menu and click on a table.
Tue Oct 28, 2008 10:01 am View user's profile Send private message
maxpesola



Joined: 26 Nov 2007
Posts: 51

Post Reply with quote
Code:
if (select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID) = 'U'
    exec sp_help @objname = :OBJECT_NAME
else
   select text
   from /*db.*/dbo.syscomments
   where id = :OBJECT_ID
   order by colid

thanks but i've tried this code into "DB OPTIONS-->DB QUERIES-->DDL Code (MSSQL)" but return (if i do the "show procedure code" on table):
"/*
Cannot find source code for 'nometable'.
Check the "DDL Code (MSSQL)" query in SQL Assistant Options.
*/"

And about the change of list returned after the second "inner join ... on " ?(maybe, is it not clear?)


Last edited by maxpesola on Wed Oct 29, 2008 4:13 am; edited 1 time in total
Tue Oct 28, 2008 1:08 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Sorry, I entered the query as a tip to show how to customize the output and behavior of different actions, but didn't actually try it. As a result I made an error

select isnull(max(type), '?') from /*db.*/dbo.syscomments where id = :OBJECT_ID
is supposed to be
select isnull(max(type), '?') from /*db.*/dbo.sysobjects where id = :OBJECT_ID

Even better is to do something like the following. Again this is just a tip, I leave it to others to test it and make the output look pretty

Code:
select
   'Column_name: ' + name + '    ' +
   'Type: '               + ISNULL(type_name(xusertype), '') + '    ' +
   'Computed: '            + case when iscomputed = 0 then 'NO' else 'YES' end + '    ' +
   'Length: '            + ISNULL(convert(varchar, length), '(n/a)') + '    ' +
   'Prec: '               + case when charindex(type_name(xtype), N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
                        then ISNULL(convert(char(5),ColumnProperty(id, name, 'precision')), '')
                        else '     ' end + '    ' +
   'Scale: '               + case when charindex(type_name(xtype), N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
                        then ISNULL(convert(char(5),OdbcScale(xtype,xscale)), '')
                        else '     ' end + '    ' +
   'Nullable: '            + case when isnullable = 0 then 'NO' else 'YES' end + '    ' +
   'TrimTrailingBlanks: '   + case ColumnProperty(:OBJECT_ID, name, 'UsesAnsiTrim')
                        when 1 then 'NO'
                        when 0 then 'YES'
                        else '(n/a)' end + '    ' +
   'FixedLenNullInSource: '   + case
            when type_name(xtype) not in ('varbinary','varchar','binary','char')
               Then '(n/a)'
            When status & 0x20 = 0 Then 'NO'
            Else 'YES' END + '    ' +
   'Collation: '      + ISNULL(collation, '')
from syscolumns
where id = :OBJECT_ID
   and number = 0
order by colid

Tue Oct 28, 2008 1:45 pm View user's profile Send private message
pvdm



Joined: 11 Mar 2008
Posts: 22
Country: New Zealand

Post Reply with quote
Hi, would it be possible to somehow show data types for variables. Maybe as a mouse over tool-tip or colour coding? Its really difficult to scroll up and down searching variable declarations on 2000+ line stored procedures that you did not write.
Tue Oct 28, 2008 3:35 pm View user's profile Send private message
Display posts from previous:    
This topic is locked: you cannot edit posts or make replies.    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2, 3, 4, 5 ... 10, 11, 12  Next
Page 4 of 12

 
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.