cursor in mysql returns error Error Code: 1136. Column count doesn't match value count at row 1

Question

hy

i am trying to create a procedure with a cursor in order to fill with data 3 tables; the problem is that when i am calling the procedure i get the error 'Error Code: 1136. Column count doesn't match value count at row 1'; my procedure looks like this. any idea where the problems is ?

delimiter /  

create procedure ggg () begin
    declare a varchar(50);

    declare b VARCHAR(30);
    declare c VARCHAR(30);

    declare d VARCHAR(30);

    declare e VARCHAR(30);
    declare gata int ;

DECLARE MyCursor CURSOR FOR

SELECT NumePacient,NumeMedic,PrenumeMedic,PrenumePacient,Cabinet
FROM `TABELA INTERMEDIARA`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET gata = 1;
OPEN MyCursor;
myloop: LOOP

FETCH  MyCursor INTO a,b,c,d,e;
   if gata=1 then leave myloop;
else 

insert into Pacienti select NumePacient,PrenumePacient from `TABELA INTERMEDIARA`
 where NumePacient=a and PrenumePacient=c;

insert into Medici select NumeMedic,PrenumeMedic from `TABELA INTERMEDIARA`
 where NumeMedic=c and PrenumeMedic=d;

insert into Cabinete select Cabinet from `TABELA INTERMEDIARA` where Cabinet = e;

 end if; 
end loop; 

FETCH MyCursor

INTO a,b,c,d,e;


CLOSE MyCursor;

end / 
delimiter ;

Table definitions:

CREATE TABLE Medici
(
  idMedic int auto_increment not null primary key,
  NumeMedic VARCHAR(50),
  PrenumeMedic VARCHAR(50),
  Statut ENUM('primar', 'specialist'),
  Specialitate VARCHAR(50)
);

DROP TABLE IF EXISTS Pacienti;
CREATE TABLE Pacienti
(
  IdPacient int auto_increment not null primary key,
  NumePacient VARCHAR(50),
  PrenumePacient VARCHAR(50)
);

DROP TABLE IF EXISTS Cabinete;
CREATE TABLE Cabinete
(
  IdCabinet int auto_increment not null primary key,
  DenCabinet VARCHAR(30)
);

This is just a guess, as I'd have to see the table definitions for Pacienti, Medici and Cabineti to be sure, but in your three INSERT ... SELECT statements does the number of SELECTed columns match the number of columns in the INSERTed table? — Darwin von Corax
– Darwin von Corax, Commented Feb 16, 2016 at 20:04
Also, why are you doing INSERT ... SELECT to get the values that you appear already to have in the variables a, b, c, d and e? — Darwin von Corax
– Darwin von Corax, Commented Feb 16, 2016 at 20:07
hy....you find below the definitions for the three tables...i got stuck in defining a cursor and i don't understand very well it's sintax: — roxana
– roxana, Commented Feb 16, 2016 at 20:55
CREATE TABLE Medici ( idMedic int auto_increment not null primary key NumeMedic VARCHAR(50), PrenumeMedic VARCHAR(50), Statut ENUM('primar', 'specialist'), Specialitate VARCHAR(50) DROP TABLE IF EXISTS Pacienti; CREATE TABLE Pacienti ( IdPacient int auto_increment not null primary key NumePacient VARCHAR(50), PrenumePacient VARCHAR(50) DROP TABLE IF EXISTS Cabinete; CREATE TABLE Cabinete ( IdCabinet int auto_increment not null primary key DenCabinet VARCHAR(30) — roxana
– roxana, Commented Feb 16, 2016 at 20:58

Darwin von Corax · Accepted Answer · 2016-02-16 21:30:43Z

insert into Pacienti select NumePacient, PrenumePacient from `TABELA INTERMEDIARA`
  where NumePacient=a and PrenumePacient=c;

This statement is attempting to insert two columns into a table (Pacienti) which is defined as having three columns. MySQL has no way of knowing which two of the three columns you want to insert into, so it complains. (The cursor it's complaining about is the implicit cursor created for the INSERT ... SELECT.)

To make this work, you need to specify the target columns in the INSERT clause, like so:

INSERT INTO Pacienti (NumePacient, PrenumePacient)
  SELECT NumePacient, PrenumePacient
    FROM `TABELA INTERMEDIARA`
    WHERE NumePacient = a and PrenumePacient = c;

This tells MySQL unambiguously which columns you want to insert into.

The same problem exists with the INSERT ... SELECT statements on Medici (two columns into five) and Cabinete (one into two), and can be solved in the same way.

HOWEVER:

Your explicit cursor already loads the desired values into the local variables you use in the WHERE clauses of your INSERT ... SELECT statements (SELECT NumePacient, PrenumePacient ... WHERE NumePacient = a and PrenumePacient = c;) so repeating the SELECTs is redundant - you can simply insert the variables instead.

DELIMITER /  

CREATE PROCEDURE ggg()
BEGIN
  DECLARE a VARCHAR(50);
  DECLARE b VARCHAR(30);
  DECLARE c VARCHAR(30);
  DECLARE d VARCHAR(30);
  DECLARE e VARCHAR(30);
  DECLARE gata int ;

  DECLARE MyCursor CURSOR FOR
    SELECT NumePacient, NumeMedic, PrenumeMedic ,PrenumePacient, Cabinet
      FROM `TABELA INTERMEDIARA`;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET gata = 1;

  OPEN MyCursor;
  myloop: LOOP

  FETCH  MyCursor
    INTO a, b, c, d, e;
  IF gata = 1 THEN LEAVE myloop;

  INSERT INTO Pacienti
      (NumePacient, PrenumePacient)
    VALUES
      (a, c);

  INSERT INTO Medici
      (NumeMedic, PrenumeMedic)
    VALUES
      (b, d);

  INSERT INTO Cabinete
      (Cabinet)
    VALUES
      (e);

  END LOOP; 

  CLOSE MyCursor;

END / 
DELIMITER ;

Note the last FETCH in your code was also redundant.

Collectives™ on Stack Overflow

cursor in mysql returns error Error Code: 1136. Column count doesn't match value count at row 1

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related