Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Pefo
Forum Newbie
Posts: 9 Joined: Wed Dec 16, 2009 5:10 pm
Post
by Pefo » Wed Jan 27, 2010 2:55 pm
How come all my variables but 'mytable' is working?
Code: Select all
DELIMITER $$
CREATE PROCEDURE add_child2 (IN mytable CHAR(40), IN myparent CHAR(40), IN mynew CHAR(40))
BEGIN
DECLARE myLeft INT;
SELECT lft INTO myLeft FROM mytable WHERE name = myparent;
UPDATE mytable SET rgt = rgt + 2 WHERE rgt > myLeft;
UPDATE mytable SET lft = lft + 2 WHERE lft > myLeft;
INSERT INTO mytable(name, lft, rgt) VALUES(mynew, myLeft + 1, myLeft + 2);
END$$
DELIMITER ;
Errormessage: table mytable does not exist.
My passed tablename does not appear.
JakeJ
Forum Regular
Posts: 675 Joined: Thu Dec 10, 2009 6:27 pm
Post
by JakeJ » Wed Jan 27, 2010 7:10 pm
Has the table been created? Does it have the correct data types? Is mytable in a different database perhaps? Try appending the database name to my table, (ex: databasename.mytable)
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Thu Jan 28, 2010 1:47 am
You can't use variables for table and column names. You need a dynamic query - take a look at the last example in
http://dev.mysql.com/tech-resources/art ... dproc.html
There are 10 types of people in this world, those who understand binary and those who don't
stevenrokz
Forum Newbie
Posts: 2 Joined: Thu Jan 28, 2010 5:24 am
Post
by stevenrokz » Thu Jan 28, 2010 5:31 am
This is general SOP, any name (variable, Table name) which contradicts with reserved words or keys can result in errors like this.
Pefo
Forum Newbie
Posts: 9 Joined: Wed Dec 16, 2009 5:10 pm
Post
by Pefo » Sat Jan 30, 2010 2:07 pm
Thanks.
Been reading and it looks like I´m able to give a tablename through an @-variable.
Right?
It is not really nesessary in this case, but I like to get a hold of the structur of it.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Sat Jan 30, 2010 2:47 pm
Yes, you can by using prepared statements as shown in the examples I gave you.
There are 10 types of people in this world, those who understand binary and those who don't
Pefo
Forum Newbie
Posts: 9 Joined: Wed Dec 16, 2009 5:10 pm
Post
by Pefo » Sun Jan 31, 2010 11:39 am
Thanks 'all.
I have some reading to do.
Might be back with an acing forehead...