distal-attribute
distal-attribute
distal-attribute
distal-attribute

Autocomplete for column names

bbbsss posted 2 years ago in General
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.
ansgar posted 2 years ago
No, this definitly never workedsmileThis 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.
bbbsss posted 2 years ago
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.
bbbsss posted 2 years ago
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.
dazamar01 posted 2 years ago
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

ansgar posted 2 years ago
t. should display column names from "table", should it? Doesn't it do that?
bbbsss posted 2 years ago
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).
jayknight posted 2 months ago
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.
dazamar01 posted 2 months ago
There's no problem anymore..! Heidi 8 solves that.
Once again I'm in love with Heidi...!
Best Regards!
kalvaro posted 2 months ago
@

jayknight wrote: 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


kalvaro posted 2 months ago
... 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! ;-)

Please login to leave a reply, or register at first.