Cannot be null....?

Databases
1

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?

2

Not sure but shouldn’t it be single quotes around the name?

3

Even if it’s a variable that’s already typed as a VARCHAR? Shouldnt that take care of the quotes?

4

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?

5

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.