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

I need help knowing where to save a query I wish to associate with a specific table

member posted 4 years ago in General
When I write a query, that I would reuse to do math in a table, is using snippets the correct place to save it? I do not understand anything about procedures or stored routines. I would think there should be an easy access way to save table specific queries. I can't really seem to find any sort of manual for Heidi and the help in the program is way over my head. Could you point me in the right direction? Would the snippets option be the best choice? I am currently saving my query in a varchar field titled notes, but I am thinking that this can't be the best option. Thanks
Tribal posted 4 years ago
as it sounds like, the best option for you indeed would be to simply save it as a snippet, as long as you're the only one using that specific query...once you need a query beeing also called by others, there's no way around using procedures/functions as you can assign access rights to certain users and more...but just as i said, saving it as a snippet should be the option in your case...
member posted 4 years ago
I would love to understand procedures/functions. Do you know of
any place where I could learn how that works in Heidi?
Would that be a stored routine? Could you give a really short example? I don't want to cause you too much trouble. Thanks
Tribal posted 4 years ago
well, as heidi is only an interface to mysql you have to learn the syntax and the operating principles of stored routines in mysql...adding such using heidi is simple:

1) right click the database you want to create you routine in
and select 'create new' -> 'stored routine'
2) enter all the required data (parameters, routine body and
so on)
3) click 'save' to save the routine in your database



information on how stored routines work and their syntax can be found here
you should consider using the 'mysql reference manual' as your first place to go in case of looking for help...
tutorials for working with stored routines can be found thousendfold on the web

i hope i could help you a bit :)
Tribal posted 4 years ago
@anse: is the link parsing that corrupted or is there some step im doing wrong when inserting them? :D
could you please spend a minute on reformating them and explain the mistake on my part for me?
after '...syntax can be found here' in my previous post there should be the following link:
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html

thanks in advice :)
member posted 4 years ago
Tribal,

Your links led to this:




DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;


So when I found this:


BEGIN

END


After selecting create new stored routine while I was in the database tab, I inserted my query between begin and end. This required clicking on edit and paste, as paste did not work. I then named and saved the routine. This added the routine by the name i gave it in the list of tables on the left with the word run in front.

Right clicking gave me the option to run the routine. It posted this in a query window :

CALL `run name of routine`()
and it also automatically ran without my having to enter via the blue arrow.

Alternatively, I erased some of the changes and just put CALL `run name of routine`() and hit the blue arrow


I will look at the other information in the link you gave, but you helped me very much with the info you provided.

I am posting this in hopes it will help someone else also.

Thank You!!!
ansgar posted 4 years ago
The link parser expects something between the brackets, which you did not enter. Just fixed these links here.
ansgar posted 4 years ago
Oh my god I have 2,212 posts here?
member posted 4 years ago
Forget about this part:

with the word run in front

In this phrase "This added the routine by the name i gave it in the list of tables on the left with the word run in front."

I think I wrote run in front.

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