Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation.
Here's the problem: for an exercise we were asked to create a movie database and then write several procedures. One procedure ist supposed to do the following: - take the id of a movie (as a parameter), the director's last and first name and then test wether the director already is in the table. If the answer is no, the name is added to the table. Either way, the procedure then adds the movieID and the ID of the director to a second table. Here's my code:
delimiter // create or replace procedure p_director ( par_movieID integer, par_firstName varchar(50), par_lastName varchar(100)) BEGIN DECLARE var_id INTEGER; DECLARE var_firstName varchar(100); DECLARE var_lastName varchar(100); DECLARE var_control integer; DECLARE exit handler for SQLEXCEPTION Begin rollback; End; Start TRANSACTION; set var_firstName = par_firstName; set var_lastName = par_lastName; SELECT directorID FROM director where lastName = var_lastName AND firstName = var_firstName INTO var_control; select var_control as debug; IF (var_control IS NULL) THEN select max(directorID)+1 from director into var_id; insert into director ( directorid, firstName, lastName) values (var_id, par_firstName, par_lastName); insert into moviedirector (movieID, directorID) values ( par_movieID ,var_id) ; ELSE insert into moviedirector (movieID, directorID) values ( par_movieID ,var_control); END IF; END// delimiter ;
As you can see I added the line "select var_control as debug;". I did this to get the value of the variable displayed on the screen. Anyway. This code should run fine. I know this, because I wrote a routine that did the exact same thing for a homework assignment. And for that database it works. However if I run this procedure unter MariaDB, it doe not work. Despite no syntax error, the statements between else and end if are never reached and, even stranger, the "select var_control as debug;" statement genereates no output on the screen, not even null!
As I said, my teacher stared at it for a solid 2 hours, finally he found the only difference between the code in my homework assignement and this exercise: the collation and caracterset of the database: For my homework, I used the collation latin1_swedish_ci, while at school we use utf8_general_ci.
Could this be the reason? Can the collation really have such a profound Impact? Has anyone of you ever run into thsi kind of problem?