Question about stored procedure 1

[timgerr]

I an working on a stored procedure for adding a new node to a php tree. Here is the procedure that I am trying to do:

DELIMITER //
CREATE PROCEDURE TestThree()
 SELECT @myRight := rgt FROM nested_category
 WHERE name = 'TELEVISIONS';



 UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
 UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

 INSERT INTO nested_category(name, lft, rgt) VALUES('OtherThings', @myRight + 1, @myRight + 2);
END //
DELIMITER ;

I get this error when I try and create it

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ';
 UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
 UPDATE nested' at line 3

When I run the code manually, all works fine, what am I doing wrong?

Thanks for the help,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[r937]

this is not a mysql problem, it's because whatever you are using to send queries to mysql does not allow more than one statement at a time

it's either odbc or php itself that is the problem

r937.com | rudy.ca

 

[ingresman]

I havn't used SP in mysql but the @ is used in MSSQL procedures to indicate a parameter in a prepaired statement.
A quick google shows update to not used a @ but just the name of the variable.
The link is

http://www.digitalpropulsion.org/Programming/Stored_Procedures_in_MySQL_5_0

Has this syntax ever worked for you ?

 

[ingresman]

Rudy,
is it not the SP that is sending the query?, putting more than one query in a SP is simple packaging.
As I say I havn't done SP in mysql so I may be way off

 

[r937]

oh yeah, sorry, i guess i answered a question different from the one you asked

:blush:

r937.com | rudy.ca

 

[timgerr]

OK, how can I assign the output of a procedure to a variable so I can use that variable in another query within the procedure. I have to get a number from a query and then use that number all the time, somting like this:

SELECT lft FROM nested_category WHERE name = '2 WAY RADIOS';

I would like the return of that query within the procedure to go into a variable so I can do somting like this

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > NewVariable;

Can this be done and if so.... how?????
Thanks for the help and learning experience.

Timgerr


-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[ingresman]

I would suggest:

DECLARE vLFT <appropriate data type>;
select lft from nested_category into vLFT where name = '2 WAY RADIOS';
-- this gets the data into the variable vLFT
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > NewVariable;

The key being declare your variables, use the INTO clause of the select an you should be ok. The update will of, course, need to be the correct column names and variables.
I don't have v5 on my machine so can't test these out but they look ok from the syntax section of the manual.
Just out of interest ehy did you think you needed a @ before the variable names?

 

[timgerr]

Thanks for the response, I was reading a page and that was the language:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Thanks,

Timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[timgerr]

Here is what the select should look like,

 select lft into vLFT from nested_category where name = '2 WAY RADIOS';

Thanks for the help,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[ingresman]

are you ok now ?, I've gone about as far as I can go as I don't have to correct version of mysql to go any further.

 

[timgerr]

I am doing well, thanks for all the help.

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!