MySQL "not a variable or NEW pseudo-variable" message

Question

I'm trying to create a procedure that will enter data and then return a message in the OUT parameter, however i'm getting this message "argument 5 for routine hospital.alextest10 is not a variable or NEW pseudo-variable in BEFORE trigger"

I have this as my procedure:

CREATE PROCEDURE alextest10
(IN a_patid CHAR(3), IN a_patnam VARCHAR(12), IN a_consno CHAR(3), IN a_ward CHAR(2),
OUT a_message VARCHAR(50))
BEGIN
SET a_message = 'Database updated';
INSERT INTO patient (patient_id, patient_name, consultant_no, ward_no)
VALUES (a_patid, a_patnam, a_consno, a_ward);
END!

And, this is my call command:

CALL alextest10 ('p99', 'Madeuppy', '999', 'w9', a_message)!

Can you help?

Much appreciated!

Shanika

Answer 1

CALL alextest10 ('p99', 'Madeuppy', '999', 'w9', @a_message);
SELECT @a_message;

Astroid

this gets rid of the error message, but it's not bringing up the message, just adding the data. any ideas? – Shanika

brilliant :) i thought the function did something else. working perfectly now, thank you both very much – Shanika

how did you declare @a_message variable? – Achene

@JitendraPancholi : were you able to figure out ? – Electroencephalograph

@Electroencephalograph sorry bro, don't remember now. – Achene

@JitendraPancholi You don't declare user-defined variables, you just start using them. See here. – School

Answer 2

I got the same error below:

ERROR 1414 (42000): OUT or INOUT argument 1 for routine apple.addition is not a variable or NEW pseudo-variable in BEFORE trigger

When I called addition procedure which has the OUT or INOUT parameters result with the raw value 3 as shown below:

mysql> CREATE PROCEDURE addition(OUT result INT)
...
mysql> CALL addition(3);

Or:

mysql> CREATE PROCEDURE addition(INOUT result INT)
...
mysql> CALL addition(3);

So, I called addition procedure with the user-defined session variable @r as shown below, then I could call addition procedure without error. *My answer and my answer explain OUT and INOUT parameters respectively:

mysql> CALL addition(@r);

Stereography

Answer 3

You should use registerOutParameter - in your case:

cStmt.registerOutParameter(5, java.sql.Types.VARCHAR);

where type of cStmt is CallableStatement.

Deutsch

This question does not mention Java at all. – School