I’m… confused.
(Names of things have been changed for privacy; understand that they are not reserved words.)
CREATE DEFINER=`myusername`@`%` PROCEDURE `transactionname`(IN Parent INT, IN Newname VARCHAR(255))
BEGIN
START TRANSACTION;
SELECT @myRight := rgt FROM mytable WHERE id = @Parent;
UPDATE mytable SET rgt = rgt + 2 WHERE rgt >= @myRight;
UPDATE mytable SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO mytable(name, lft, rgt) VALUES(@Newname, @myRight + 1, @myRight + 2);
COMMIT;
END
Invoking: CALL transactionname(1,"A String")
Yields: Column 'name' cannot be null
…but… it’s not null? It’s being filled with a variable value?
Not sure but shouldn’t it be single quotes around the name?
Even if it’s a variable that’s already typed as a VARCHAR? Shouldnt that take care of the quotes?
Sanity Checking:
Are there rows with null in the name field? No, nor can there be because the field is NOT NULL.
Execute the queries manually, with the variables replaced:
query |
result |
SELECT rgt FROM mytable WHERE id = 1; |
20 (Expected) |
UPDATE mytable SET rgt = rgt + 2 WHERE rgt >= 20; |
1 rows affected (Expected) |
UPDATE mytable SET lft = lft + 2 WHERE lft > 20; |
0 rows affected (Expected) |
INSERT INTO mytable(name, lft, rgt) VALUES("A String", 20, 21); |
1 row inserted (Expected) |
(I have realized i need to tweak that last query to insert MyRight, Myright+1 instead of +1,+2; but neither here nor there.)
I tried adding:
SELECT "Test" AS brace, @Newname AS msg, CONCAT("Testing",@Newname) AS test;
and got "Test",null,null
… so … uhhh… have i completely misunderstood how to pass a string to a stored procedure?
No, i’ve fundamentally misconstrued an inline variable declaration with a procedural parameter one. Right… I shouldnt have the @'s in front of the usages of parameters.