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

Query editor -- Each field in different line (more visual friendly)

User, date Message
Written by Mustard007Money, Euro
1 year ago
Category: General
36 posts since Thu, 18 Apr 13
Hi !
It will be great to have in the query/view editor, each field on different line (like in MySQL workbench). Hi think is more visual friendly when you edit with a lot of fields.

Thanks for your great soft and works !!
;)
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Sorry, should to be in Features request !!
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
Looks like you prefer Workbench :)
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Sorry, but is not beacause I like features in other software that I prefer other software.

I very like HeidiSQL and use it each day. Is just suggestions for my PREFERED SQL software to be even more great !

Thanks !
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
We're talking about the VIEW editor, right? That VIEW body is a bit tricky to get from the original query, as the MySQL server rewrites the body when you save a VIEW. But Heidi already tries to load it from the .frm file on the server. Can you please paste here what you see in the SQL log at the bottom when you open the VIEW?
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Hi !
Is what I see in the SQL log:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vueTamponFC.frm'));
SHOW /*!32332 FULL */ COLUMNS FROM `vueTamponFC`;
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
But in the view editor, I mean the "Field" section. Each field like this:

`tblservicescommande`.`idService` AS `idService`,

...is on a separate line like this:

`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`idService2` AS `idService2`,
`tblservicescommande`.`idService3` AS `idService3`,
`tblservicescommande`.`idService4` AS `idService4`,
`tblservicescommande`.`idService5` AS `idService5`,
etc...

Is more readable on each line than all on the same line like this:

`tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`idService2` AS `idService2`,`tblservicescommande`.`idService3` AS `idService3`,`tblservicescommande`.`idService4` AS `idService4`, etc...

Thank you very much and keep the good work ! ;)
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
That's what I meant. The server modifies a VIEW body when you save it. Later checks for SHOW CREATE VIEW show the whole body in a one-liner. That's bad, but not really fixable by HeidiSQL. Though HeidiSQL already does its best to retrieve the originaly body, by issuing such a vicious query:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'dbname/viewname.frm'));


However, that returns an empty string in many cases, probably because of missing file privileges on the server.

Now, in HeidiSQL, you have the "Reformat SQL" feature (see the white main toolbar button with a pencil on it, 6th one counted from the right). Clicking that, or pressing its shortcut Ctrl+F8 brings the SQL in any editor in order again.
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Ok thanks ! I use already de "Reformat SQL".

Thanks for helping and taking time to check an explain that !
;)
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
He just don't work for the fields list. But for the rest is great !
Thanks !
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
If you have full access to the server, then you can also try to find a fix for the non-working LOAD_FILE() command mentioned above. You could check what the file "/path/to/mysql-data/bdtic/vueTamponFC.frm" contains and/or if it exists at all.
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Ok I will check that thanks! ;)
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Yes it exist. They content some information on the query like the definer, enconding, and the query (on one line for each info).
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
That is something I can do ?
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
The problem is, you seem not to have the privileges to read that file with the LOAD_FILE command. Probably you do not have the "FILE" privilege? You can check with HeidiSQL > Tools > User manager.
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
Ok thanks I check that !
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
After check, I have all the rights...and the "File" one.
My Server is on Linux.

Thanks !
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
I think I cath the thing....

If you edit first the view with Heidi, after the fields are correct on each line like I did. But for me, this work only in Xammp locally on my computer and not on my Linux server.

Thanks !
Written by Mustard007Money, Euro
1 year ago
36 posts since Thu, 18 Apr 13
I think it's someting with ASCII Line Return code in UNIX vs in Windows.

My line is recorded ok in a server in Windows but not in Linux.

Thanks !
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
No news on this ?
I made other testing and effectively,if the server is on Windows the view keep the view body like you mention, but if it's a Linux server he doesn't.
I check the "FILE" privilege, I have all privillege, and even with root access. Not working...

But, like I said in my last post, I found that is probably
the ASCII New Line/line return code that is different in UNIX vs Windows who make the troubles...


Thanks and keep the good works !
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Ok you should post both VIEW bodies here, the one from your Windows box and that from your Linux box. That way I can probably see what's wrong here. I'm unsure if the LOAD_FILE command works or not. But we'll see when you post your queries here.
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Also, you could post more from your SQL log when you doubleclicked your VIEW. Is there an error message after the "SELECT LOAD_FILE..." ?
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
Hi !
BODY from Windows MySQL:
------------------------

select
`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`isservice_ratache` AS `isservice_ratache`,
`tblservicescommande`.`noCommande` AS `noCommande`,
`tblservicescommande`.`duree_serv` AS `duree_serv`,
`tblitems_2_prix`.`competence` AS `competence`,
`tblitem`.`Nom` AS `Nom`,
if((`tblservicescommande`.`isservice_ratache` is not null),
`tblservicescommande`.`isservice_ratache`,
`tblservicescommande`.`idService`) AS `ID1`,
`tblservicescommande`.`nblogement` AS `nblog`
from
((`tblservicescommande`
left join `tblitems_2_prix` ON ((`tblservicescommande`.`idItem` = `tblitems_2_prix`.`idItem`)))
left join `tblitem` ON ((`tblservicescommande`.`idItem` = `tblitem`.`idItem`)))
where
((cast(`tblservicescommande`.`dateService` as date) >= now())
and ((`tblitems_2_prix`.`nbLogement` = `tblservicescommande`.`nblogement`)
or (`tblitems_2_prix`.`nbLogement` = 0)))


Body from Linux MySQL (One liner...):
----------------------
select `tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`isservice_ratache` AS `isservice_ratache`,`tblservicescommande`.`noCommande` AS `noCommande`,`tblservicescommande`.`duree_serv` AS `duree_serv`,`tblitems_2_prix`.`competence` AS `competence`,`tblitem`.`Nom` AS `Nom`,if((`tblservicescommande`.`isservice_ratache` is not null),`tblservicescommande`.`isservice_ratache`,`tblservicescommande`.`idService`) AS `ID1`,`tblservicescommande`.`nblogement` AS `nblog` from ((`tblservicescommande` left join `tblitems_2_prix` on((`tblservicescommande`.`idItem` = `tblitems_2_prix`.`idItem`))) left join `tblitem` on((`tblservicescommande`.`idItem` = `tblitem`.`idItem`))) where ((cast(`tblservicescommande`.`dateService` as date) >= now()) and ((`tblitems_2_prix`.`nbLogement` = `tblservicescommande`.`nblogement`) or (`tblitems_2_prix`.`nbLogement` = 0)))


Thanks !
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
From the SQL LOG after selecting view (Linux MySQL):

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vue_competences.frm'));
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='vue_competences' AND TABLE_SCHEMA='bdtic' ;
SELECT CURRENT_USER();
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
I think it's probably something to do with Windows/Linux format of Line return / Carriage return that is not the same ascii code. (Unix vs Windows format)
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
OK, no error after LOAD_FILE. This means that works, but you have stored the VIEW afterwards with the one-liner. Please modify the VIEW on your Linux MySQL, let's say with a few linefeeds. Then save it, go to another table or view and then back to this view. Linefeeds should have been preserved now, right?
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
No.. I already try that. This work only on Windows MySQL server. On Linux server we lost the formatting, it's not been preserved.

Like I said, I think the Unix vs Windows linefeed code is the problem.

Thanks!
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Not for HeidiSQL - the editor displays Linux and Windows linebreaks (and Mac style and some others also). So, I wonder what the LOAD_FILE command gives you.

Please fire in a query tab and post the result here:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vue_competences.frm'));

Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
The result of this is: (NULL)
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13

Mustard007 wrote: The result of this is: (NULL)


On Windows server I have: TYPE = VIEWquery...etc..
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
So.. whats wrong Doc? wink
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
I think its a BUG on MySQL; see this:

http://bugs.mysql.com/bug.php?id=38403
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Yes, looks so. A NULL is also returned when the file does not exist, but I guess that path should exist.

What does

SELECT CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vue_competences.frm')


give you?
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
Yes the path and file exist, and mysql is the owner.
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Please fire the above mentioned query and post results here.
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
There is:

/var/lib/mysql/bdtic/vue_competences.frm
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
And SELECT LOAD_FILE('/var/lib/mysql/bdtic/vue_competences.frm')
returns NULL?
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13

ansgar wrote: And SELECT LOAD_FILE('/var/lib/mysql/bdtic/vue_competences.frm')
returns NULL?



Yes: (NULL)
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
Ok. Then this is definitely something you have to verify on the server itself. You say the path is right and mysql can access it. Probably you indeed hit the above server bug.
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
YEP, it's a MySQL BUG...

So thanks again Ansgar for all your help !

;)
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
Hi Ansgar !

So for this, do you think it's possible to upgrade the CTRL-F8 function to have just one field per line on the SELECT line and not just one command per line?

Now we have this:

SELECT `tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`IdTech` AS `IdTech`,`tblservicescommande`.`idItem` AS `idItem`,`tbl........

I think it will be great to have like this on multiple line:

SELECT
`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`IdTech` AS `IdTech`,
`tblservicescommande`.`idItem` AS `idItem`,
`tbl..........

Thanks Ansgar !
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
I give you a beer ;)
Numéro de confirmation : 9U103714RJ872863J
Written by ansgar
9 months ago
4936 posts since Fri, 07 Apr 06
What the auto-formatter does is highly subject to personal preferences, in many details. There was already a discussion somewhere in the tracker, without a reasonable consensus. Finally I decided to leave it as it is. It's quite ok currently, and I am not wanting to serve hundreds of personal preferences for this cosmetic feature.
Written by Mustard007Money, Euro
9 months ago
36 posts since Thu, 18 Apr 13
Good !
Thanks ansgar !
 

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