Autocomplete for column names

Hi,

I'm running Heidi 7.0.0.4316 on Win7 Enterprise x64 connecting to MySQL 5.1.46 Community edition on Windows Server 2008 R2.

The problem I'm having is that completion proposals in the editor work fine for everything other than column names. I'm connecting to a WordPress database. An example table is here:

CREATE TABLE `wp_users` (
`ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_login` VARCHAR(60) NOT NULL DEFAULT '',
`user_pass` VARCHAR(64) NOT NULL DEFAULT '',
`user_nicename` VARCHAR(50) NOT NULL DEFAULT '',
`user_email` VARCHAR(100) NOT NULL DEFAULT '',
`user_url` VARCHAR(100) NOT NULL DEFAULT '',
`user_registered` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` VARCHAR(60) NOT NULL DEFAULT '',
`user_status` INT(11) NOT NULL DEFAULT '0',
`display_name` VARCHAR(250) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
INDEX `user_login_key` (`user_login`),
INDEX `user_nicename` (`user_nicename`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=14;


Typing the following query (without quotes) then pressing Ctrl+Space will bring up a list of matching table names:

'SELECT * FROM wp_'

I can then select from any of the wp_ prefixed tables using the popup. That's great.

However, if I try to do it with a column name with a query like this before hitting Ctrl+Space:

'SELECT * FROM wp_users WHERE user_'

I get a popup box but it's just empty. Same would apply if I use the following query but the caret position is just after the underscore in the column name:

'SELECT user_ FROM wp_users'

If I put an alias on the table, I can get completion proposals without any problems. For example, this works fine:

'SELECT * FROM wp_users u WHERE u.'

As expected, the popup brings up all column names from wp_users.

Any idea why it doesn't work when I haven't put an alias for the table? I *think* this used to work.

No, this definitly never workedThis is because HeidiSQL needs some hint what you want to see, which is no problem with a table alias. But if there is nothing, just a starting string with some characters, you could easily ask for some MySQL function name, or for a table name, or database name, or whatever. Table aliases and table names work.

Cool that's fine. I jump between HeidiSQL and SSMS quite a lot depending on the project and I think whatever I've got installed in SSMS does the column completion. Cheers for the quick reply and keep up the great work.

I've had a look and it's SQL Complete for SSMS I was getting confused with. When you pretty Ctrl+Space in scenarios similar to the ones I mentioned above, you get the following:



The popup window seems to list the following (in this order):

* Column names for table
* UDFs
* Roles
* Databases
* T-SQL built-in functions
* T-SQL configuration functions

That does result in quite a lot of data being in the popup box but it is probably more useful than nothing popping up.

I have a problem with autocomplete.

Here is:

I have the next query:

Select * from table where tabla.idtable = 1

Autocomplete works fine, but with the next query

Select * from table t where t. <--

Autocomplete in Heidi 7 doesn't work like Heidi 6

t. should display column names from "table", should it? Doesn't it do that?

Both of these scenarios work for me:

SELECT * FROM jos_users WHERE jos_users.
SELECT * FROM jos_users u WHERE u.

In that after the period, Heidi will bring up the autocomplete window with column suggestions from that table. Not sure why it isn't working for you dazamar01 as I'm also using Heidi 7 (7.0.0.4316).

I sure wish it would complete column names based on the table(s) in the FROM portion of the query. Many times I start with SELECT * FROM blah, then want to get a specific subset of column, I'd love for it to offer autocompletions when I have something like

SELECT us| FROM users

Where | is the cursor, it could give me user_id and username if those are columns in the users table.

There's no problem anymore..! Heidi 8 solves that.
Once again I'm in love with Heidi...!
Best Regards!

@

I sure wish it would complete column names based on the table(s) in the FROM portion of the query. Many times I start with SELECT * FROM blah, then want to get a specific subset of column, I'd love for it to offer autocompletions when I have something like

SELECT us| FROM users

Where | is the cursor, it could give me user_id and username if those are columns in the users table.

Currently, you need to assign an alias to the table:

select us.|
from users us

... which is not actually the problem this thread is about. It's always better to open a new thread in this cases. It's free! ;-)