Master-Detail Browser / Toad Version

[Paul]

Toad 16.2.98.1741

Master-Detail Browser
If your connection drops, all of the speed buttons become disabled, even after connection re-establishment, so you have no choice but to close the Master-Detail Browser. As a minimum I would expect the Refresh All Grids speedbutton to always be enabled, and if it worked, it should re-enable the rest of the buttons in details and sub details.

Can you make it so there is both "Save" and "Save As" speed buttons, rather than just currently which acts as a "Save As". Edits currently take three clicks to save an existing MDB setup.

Deletions
If I edit a query and add say a join to show some extra info, I can no longer delete the record. It's because the deletion tries to pessimistically lock the record first but refers to all of the columns in the for update nowait statement

e.g.

select d.rowid, d.*, (select count(*) from MY_CHILD_DATA c where c.PARENT = d.ID) CHILD_CNT
from MY_DATA d

I want to delete a record, it issues the lock:

SELECT ID, COL1 as "_1", COL2 as "_2", CHILD_CNT as "_3"
FROM SCHEMA.MY_DATA
WHERE
  ROWID = 'AAELt7AAAAAA0RaAAJ'
FOR UPDATE NOWAIT

and this fails, because CHILD_CNT doesn't exist. To achieve a lock, it doesn't need to select the values, it can select null, which is far more robust and less resource hungry :

SELECT null DUMMY#
FROM SCHEMA.MY_DATA
WHERE
  ROWID = 'AAELt7AAAAAA0RaAAJ'
FOR UPDATE NOWAIT

Toad | About
Can you add a "Copy to clipboard" hyperlink / button on the Toad About screen please, to copy the version information (e.g. Toad For Oracle, 16.2.98.1741, 32 Bit) currently it is read only. It'll be useful for users when reporting issues and telling colleagues what version they are using.

EDIT : Added delete issue.

Regards
Paul

[JohnDorlon]

Hi Paul:

• Master-detail connection lost problem: seems like a bug. I'll investigate on Monday.
• Edit/Delete when a subquery exists: The column selection exists to verify that another user hasn't changed the row since you did your initial SELECT. Not sure that we'll be able so solve this one w/o causing some other issue. You could work around it by putting the record count as a child dataset that you don't intend to edit. I know, it's not as helpful that way.
• Help -> About: Good idea. As a workaround, go to Help -> Support bundle instead. You can copy/paste it from there.

-John

[Paul]

Hi John

Couldn't you inline view the original query before applying the filtered for update nowait, that way the row check is preserved but it supports the lock? I'd imagine the predicate will get pushed into the view.

For my example

select *
from (
  select d.rowid as ROWID#, d.*, (select count(*) from MY_CHILD_DATA c where c.PARENT = d.ID) CHILD_CNT
  from MY_DATA d
)
where ROWID# = 'AAELt7AAAAAA0RaAAJ'
for update nowait

Thanks, I know about the support bundle, but it just involves extra work, cutting out what you need, plus most apps have a copy version info button or hyperlink for speed nowadays.

Regards
Paul

[Paul]

Hi John

Another REALLY annoying issue on the Master-Detail Browser are grid heights reverting back after a column header click sort. Resize a grid, click column to sort, grid resizes back again.

Kind regards
Paul

[Paul]

Hi John

Also, if I use "Add Missing Columns", it wipes out any manual amendments I've made to the query. E.g. Joins, manually added columns

Paul

[JohnDorlon]

The point of "Add Missing Columns" is to rebuild the query, so that if columns have been added to the table, they will be included. Seems like it's asking for trouble if I am to just work that into the existing SQL along with whatever changes have been made to it. Maybe I could add a feature that just lets you know of any columns that exist in the table that are not in the SQL. Then you could add them.

I'll look at the resize thing.

[Paul]

Hi John

It's always best to give some context: I have quite a lot of saved Master - Detail Browser (MDB) sets, it's a really useful and powerful piece of Toad functionality for me as it allows me to check data and sub dependencies in complex scenarios, by scrolling though data, filtering, sorting, even editing and correcting data if need be etc. It saves me a lot of time where I'd normally have to write queries and is more powerful than a single flat query result set, given the fact MDB can be a hierarchical in nature (Master driving details, each detail driving sub details etc. etc.).

Some of the queries in my MDBs have been edited to include extra info from other tables - sometimes for information purposes, like a description (e.g. if it's a link table where only IDs exist), or a count or a 0 or 1 to show something exists.

So regarding the issues I highlighted:

1. I can't edit the underlying table data if I've added something to query. I also have the same problem applying a filter to a query. I get an error relating the the extra columns I've added.
2. If connections drop (I connect to DBs across VPN or via Cloud services, they occasionally drop out) I have to close the MDB, even after I reconnect the session.
3. I sometimes resize the grids if I'm focusing on certain data and it has lots of rows, but when I filter or sort, that resizing resets.
4. Obviously tables evolve (columns added) so these saved MDB sets end up missing columns, it would be nice to be able to add those to the tables and queries with a click of "Add Missing Columns", even if I've edited them. My (hopeful) thinking was the columns projected by the query would be checked against the table specified and any missing ones would be appended to the select section.

I hope that all makes sense. Thanks as always for your hard work and great product.

Regards
Paul

[JohnDorlon]

Hi Paul,

Thanks for the details. I think we're more or less on the same page here.

1. I haven't determined yet why you have edit problems after the SQL edits described above, but it's on my to-do list. There is probably some option you can change for this. I'll be looking at this again soon.
2. this is fixed for next beta
3. this is fixed for next beta
4. next beta will have a drop-down arrow next to "add missing columns" that will just "find missing columns" and offer to copy to clipboard. Then you can paste that into your SQL. I can maybe come back later to look at adding w/o breaking your sQL.
5. Next beta will also have save/save as buttons.

@Pavel is the one you can thank for these changes, not me.

[JohnDorlon]

Hi Paul,

I've looked at the edit/delete problem further. I found that master-detail actually is issuing the statement like "SELECT ID, COL1 as "_1", COL2 as "_2" except for me, it was not including the fields that are not part of the table, so I had no errors when editing/deleting.

The SQL wasn't coming up in Spool SQL (I'll fix that) which is why I thought it wasn't being executed. Maybe you got yours from SQL Tracker.

Anyway, my SQL was:

Select m.rowid, m.*,
       (select count(*) from dept d where d.deptno= m.empno) as dept_cnt
from   JDORLON.AA_EMP1 m

Was yours exactly as shown above or did you simplify it for the purpose of the post to the forum?

-John

[Paul]

Hi John

The example with the inline count also had a joined column in, and on investigation it is to do with that, where a joined column isn't aliased. I've created a simple example that shows the problem:

create table T_PS_TEST (
  ID primary key
, DECSR  
) as
select 1, 'Test1' from dual union all
select 2, 'Test2' from dual;

-- Create MDB on T_PS_TEST, edit query to this (notice DUMMY isn't alias qualified) :

Select m.rowid,
       m.ID,m.DECSR,DUMMY
from   PAUL.T_PS_TEST m
left join dual on m.ID = 1

When I edit DESCR, this happens:

--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41

SAVEPOINT LOCK_


Elapsed time: 0.022

--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41

SELECT ID AS "_1", DECSR AS "_2", DUMMY AS "_3" FROM PAUL.T_PS_TEST
WHERE
  ROWID = 'AAEdXVAAAAABdxjAAA'
FOR UPDATE NOWAIT

Error occurred: [904] (ORA-00904: "DUMMY": invalid identifier
)

--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41

ROLLBACK TO SAVEPOINT LOCK_


Elapsed time: 0.023

If I change the query, aliasing dual and the Dummy column, it works:

Select m.rowid,
       m.ID,m.DECSR,d.DUMMY
from   PAUL.T_PS_TEST m
left join dual d on m.ID = 1

Timestamp: 01/12/2022 10:18:20

SAVEPOINT LOCK_


Elapsed time: 0.025

--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:18:20

SELECT ID AS "_1", DECSR AS "_2" FROM PAUL.T_PS_TEST
WHERE
  ROWID = 'AAEdXVAAAAABdxjAAA'
FOR UPDATE NOWAIT


Old_ROWID=['AAEdXVAAAAABdxjAAA']


Elapsed time: 0.028

In my actual case, the columns in many tables have a column prefix (yuk, not my design), so aliasing isn't necessary. E.g. T_MY_TABLE, columns would be MT_ID, MT_DESCR etc. At least I now know a workaround.

Whilst work is being done in this area, if I load a MDB set, could you add the filename to the hint you get when hover over a MDB tab please? E.g. SCHEMA@INSTANCE Master-Detail Browser - C:\User\Paul\Test.md. I often have many open and it's hard to see at a glance which I loaded from.

Many thanks @Pavel for your efforts in this area!

Regards
Paul

[JohnDorlon]

Hi Paul,

Yeah we can add the file name to the form context. How about master->detail table name instead?
Seems more user friendly. I'm thinking just the first detail, if there are multiple. If you don't like that and would prefer to see file name, do you really want full path or just file name?

Thanks for the details on the edit/delete thing. Yup, looks like aliases are the key here. I'll see if I can make any improvement with that.

-John

[Paul]

Hi John

All good suggestions.

I like the idea of master->detail table name but many of my MDB sets are multiple levels deep, so not sure how the naming would work there? E.g.

MASTER
|-------DETAIL(1)
|              |------------SUB-DETAIL(1.1)
|              |------------SUB-DETAIL(1.2)
|-------DETAIL(2)
                |------------SUB-DETAIL(2.1)
                |                   |------------SUB-SUB-DETAIL(2.1.1)
                |------------SUB-DETAIL(2.2)

If you go the filename route, I think the full filename in the tab hint (or at least an ellipses version c:\longpath...\lastdir\myfile.md) helps when you have multiple variants of the same MDB name but in different folders.

Regards
Paul

[JohnDorlon]

If I just did master and first detail for the context, would that be enough info?

Or Master-first detail (myfile.md)?

I could also put the full path of the filename on the MD status bar.

So, even if I had more detail tables, the hint would be the same.

[Paul]

The filename in the hint with some path context helps me when I have multiple open. When you added that for editor files, I found it invaluable.

Maybe
Master-first detail (c:\longpath...\lastsubdir\myfile.md)?

[Paul]

Hi John

Sorry to be a massive PITA but something else I noticed, if I edit a query in MDB to do something like add some joined data and I F4 describe a table, it doesn't allow the dragging of column names into the query editor. Any chance this could be allowed?

Regards.
Paul

[Paul]

Regarding the aliasing of columns issue, if I apply a sort via "Sort / Filter the dataset" button and pick columns to sort that are from a joined table, I get a similar problem of unknown column.

[JohnDorlon]

Hi Paul,

I've logged the F4/drag thing to look at next week.

Re: Filter/sort - yeah, that's kind of a known issue. Once you do a view/edit query and make changes, filter/sort isn't getting what it expects so stuff like that can happen. Its best to stick with view/edit query once you've made changes. I'll take a look though to see if I can get it to use the alias. That should prevent this error.

-John

[Paul]

Hi John

Many thanks. I was thinking the sorting thing might be an awkward problem to solve. We had a similar thing with column heading click sorting for grids in our applications. We used one of two approaches:

1. Use index position of a column if it's not from the master table?
   order by 3 desc, m.MY_TAB_COL
2. Inline the original query and sort on that

select *
from (
  -- Original Query
  select m.*, c.DESCR
  from MY_TAB m
  left join MY_CHILD_TAB c on c.PARENT_ID = m.ID
) a
order by DESCR, ID

I think the latter was more robust for us, but anyway, I'm sure you have some ideas.

Regards
Paul

[JohnDorlon]

I can look at inline, but for now...

For sorting, you can right-click in the grid and enable memory sorting. After that we won't run SQL each time you click a column header. The only downside to that is that it fetches all rows...so it's not ideal for large datasets.

Edit: Actually, it looks like column-header clicking (even w/o memory sorting, and w/o column aliases) is working fine. In that case we use "order by position" instead of "order by field name".

I know you are referring to the Filter/sort dialog. I'm just suggesting a workaround.

[Paul]

Hi John

It's when I want to order by multiple columns, unless there's some way of doing that with column clicks that I'm not aware of?

Regards
Paul