Vertica tables dropped into Query Builder issue

Toad Data Point
1

Hi. I am having an annoying intermittent problem. Often, when I drag a Vertica table from Object Explorer into Query Builder, the table shows up but doesn’t list its columns – it only shows two things where the columns should be: “(Add All Columns)” and "* (Wildcard)’.

I am using Toad Data Point 3.2.0.916, Vertica ODBC driver version 4.1 (the latest version that seems to work with Toad) and Vertica 5.12.

Thanks in advance for any help / guidance!

2

Hi.
To understand the issue I need more information. Could you please answer the following questions?

Does it happen for all tables or only some of them? Does it happen all time for a particular table or sometimes. Does it depend on the user id? Could you provide step by step instructions to reproduce the issue?

Do you get column list for such table? To see it please right click on a table and select View Details, the Columns tab should be on top. Do you get the columns there? If you get columns on the columns tab please try to add the table again to the Query Builder. Do you get the columns now?

Right now I could guess that it’s a timing issue. You add a table to Query Builder before columns info has been fetched. That’s why only “Add All” and “*” columns are shown.

3

Hi - it is inconsistent for a given table. Just a minute ago, I had the problem for a table with only five columns, so this doesn’t seem limited to just tables with a lot of columns. The same table appeared fine in another Query Builder earlier today. I am also encountering this issue when dragging and dropping into Database Diagram.

Answers to specific questions:

  • There is no pattern to what tables this happens to, and sometimes a given table works and other times it doesn’t. I can tell when it is about to happen, because when I drop the table on Query Analyzer, there is a longer pause before the table then appears (with just those two items in it).
  • I haven’t tried other user id’s.
  • To replicate, I open a new (blank) Query Designer. Click on a table in Object Explorer and hold, drag the table to Query Designer, release the mouse and, after a brief pause (~1 sec), the table appears with just those two items and not all the columns.
  • No I don’t get the column list … that is the issue.
  • Yes, when I right-click on table and View Details, the columns appear in the new tab just fine. But when I re-drag-and-drop that table into Query Designer, it still shows up with the problem.
  • Agreed - it does seem like a timing issue. I’ll keep notes on when the problem is occuring to see if there are any patterns I can see. In the meantime, if you can think of a solution, let me know. Thank you!
4

Thank you for quick answers. Query Builder and Database Diagram use the same engine so it’s not surprise that the issue appears in the both places.
Please correct me if I am wrong. The issue is inconsistent but could be reproduced in the following way.

  1. Drag and drop a table on Query Builder. It takes longer to see the table before it comes with just “All Columns” and “*” columns.
  2. If you open View Detail for this table all columns are there.
  3. After that deleting the table and adding again to Query Builder does not fix the issue. The columns are still missed.
    You could try the followingObject Explorer shows a column list at the bottom when a table/view is selected. If this list is empty please press F5 (refresh) and wait a little bit to see if the list is populated. And add the table to Query builder after that. Please let me know if it helps you.
5

Hi - the column list at bottom of Object Explorer always shows the column list accurately … the only issue I am encountering is the columns not being shown in the Query / Database Designer area.

6

Hi. I think I’ve found a workable solution. The issue is related to “timing”. Giving Toad enough time to fully fetch all “metadata” for the table is the key. If the table is placed into Query Designer and the columns don’t appear in the graphical table object in Query Designer:

  • remove the table from the query
  • go to Obj Explorer, right click on the table, select “Refresh Item”
  • it will say “Fetching Data” in the area below the table list (where it normally shows the list of columns)
  • once the list of columns appears in that lower area, drag the table into Query Designer again and the table and its columns should appear as expected.

I will add to this thread if I this workaround doesn’t always work, but I think we can consider this “answered” for now.

Thank you for your excellent customer support and quick response times – those are major reasons why I continue to be a TDA fan!

7

I am glad that we found a workable solution. But the issue is still there so I create CR # 104926 to track it down.
Recently we fixed some Vertica related issues so you could try the latest beta and see if it works correctly.