MySQL: Unknown column in 'field list'

Question

I'm new to stored procedures in MySQL. Why is the beginning of my stored procedure...

DROP PROCEDURE IF EXISTS videodb.addVideo;
CREATE PROCEDURE videodb.addVideo(vidTitle VARCHAR(255), vidDescription VARCHAR(500), producerArray VARCHAR(500), actorArray VARCHAR(500), OUT vidSuccess BOOL)
    START TRANSACTION;
            SET @videoInsertVar = 'INSERT INTO videodb.videos (id, title, description) VALUES(?, ?, ?)';
            SET @videoId = NULL;
            SET @videoTitle = vidTitle; -- this is line 69

...producing this strange error...

ERROR 1054 (42S22) at line 69: Unknown column 'vidTitle' in 'field list'

Wrap in `BEGIN`-`END` That will make *body* a single statement. See http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html — PM 77-1, Dec 23 '14 at 05:00
Right you are. I originally had a `BEGIN ... END` but I misinterpreted the documentation. I thought this meant you should use `START TRANSACTION` in place of `BEGIN ... END`, but now I understand: "Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. To begin a transaction in this context, use START TRANSACTION instead." — Rob Johansen, Dec 23 '14 at 05:46
@PM77-1 If you turn your comment into an answer, I'll accept it. And thanks for your help! — Rob Johansen, Dec 23 '14 at 16:15

score 2 · Accepted Answer · answered Dec 24 '14 at 01:41

Your problem is that the statement that throws the error is actually outside of your procedure and, thus, - outside of its parameters scope. The solution is to wrap the entire routine_body of the procedure into a BEGIN ... END structure to create a Compound Statement.

CREATE PROCEDURE expects a "Valid SQL routine statement" in its body. Using the Compound-Statement Syntax allows to group multiple statements into a single code-block and comply with this requirement.

score -1 · Answer 2 · answered Dec 23 '14 at 05:55

-1

You are missing the IN-denoting that the input parameter, and I hope you have an END-at the last line.

Try some changes like this:

CREATE PROCEDURE videodb.addVideo(IN vidTitle VARCHAR(255), IN vidDescription VARCHAR(500), IN producerArray VARCHAR(500), IN actorArray VARCHAR(500), OUT vidSuccess BOOL)
BEGIN
    START TRANSACTION;
            SET @videoInsertVar = 'INSERT INTO videodb.videos (id, title, description) VALUES(?, ?, ?)';
            SET @videoId = NULL;
            SET @videoTitle = vidTitle; -- this is line 69

Also make sure that you have set the DELIMITER, otherwise ; will be treated as the end of the stored procedure.

answered Dec 23 '14 at 05:55

Ataboy Josef

2,087
3
22
27

2

Actually, each parameter is an `IN` parameter by default. See http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html – Rob Johansen Dec 23 '14 at 05:59
And you missed the next line: Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters. – Ataboy Josef Dec 23 '14 at 06:02
1

I didn't miss that line; however, you evidently misunderstand it. That line, to put it another way, is merely stating that you cannot specify `OUT` or `INOUT` for functions. – Rob Johansen Dec 23 '14 at 06:10

MySQL: Unknown column in 'field list'

2 Answers2

Linked