Only 44 hours left in the trailboss' kickstarter!

New rewards and stretch goals. CLICK HERE!



Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

MySQL problem  RSS feed

 
Iovitu George
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all! I made some MySQL courses  and i took all the exams everything.Theoreticaly i am good but to finish with everything i had to make a projects.I came here because after lots of hours of thinking what is wrong i gave up ....So if you can help me i would be very happy especially i don't have so much time to finish the project.

I must create a procedure that create 5 tables.And one tables intermediant.In the intermediant table i must load records from a files and after to populate every tables.FIrst i must use a loop and a cursor and the other one a trigger.

The procedure to create the tables is


create schema if not exists pfinal;

use pfinal;

delimiter $

drop procedure init$
create procedure init ()

BEGIN

drop table if exists Intermediar,Vizite,Medici,Pacienti,Cabinete;
create table  Medici(id int unsigned not null   primary key  ,
nume varchar (30),prenume varchar (30),
statut enum ( 'primar','specialist'), specialitate varchar (30), unique (nume,prenume))Engine=InnoDB;

create table  Pacienti(id int unsigned not null     primary key,
nume varchar (30) , prenume varchar (30) ,data_nasteri date, unique (nume,prenume))Engine=InnoDB;

create table   Cabinete(id int unsigned not  null primary key,
nume varchar (30) unique)Engine=InnoDB;

create table if not exists Vizite(id int unsigned not null    primary key,
DataVizita datetime, Durata time,
id_medic int unsigned not null  ,
id_pacient int unsigned not null ,
id_cabinet int unsigned not null  ,
foreign key (id_medic) references Medici(id),
foreign key ( id_pacient ) references Pacienti(id),
foreign key (id_cabinet) references Cabinete(id))Engine=Innodb;

create table if not exists Intermediar(id int auto_increment primary key,
Data_Vizita varchar(30) , Ora_intrare time,Nume_pacient varchar (30),Prenume_pacient varchar (30),
Nume_medic varchar (30), Prenume_medic varchar (30), Cabinet varchar (30),
id_medic int,id_pacient int,id_cabinet int);

END$

delimiter ;

call init();



I load data from the files
load data local infile 'C:\\Users\\Nely\\Desktop\\sql_pfinal_date (1)\\medici.txt'
into table Medici
fields terminated by ','
lines terminated by '\n'
ignore 1 lines
(nume,prenume,statut,specialitate);


-- Data_Vizita date , Ora_intrare time,Nume_pacient varchar (30),Prenume_pacient varchar (30),
-- Nume_medic varchar (30), Prenume_medic varchar (30), Cabinet varchar (30));

load data local infile  'C:\\Users\\Nely\\Desktop\\sql_pfinal_date (1)\\vizite.txt'
into table Intermediar
fields terminated by ','
lines terminated by '\n'
ignore 1 lines
(Data_Vizita,Ora_intrare,Nume_pacient,Prenume_pacient,Nume_medic,Prenume_medic,Cabinet);
update intermediar set Data_vizita=str_to_date(Data_vizita,'%d/%m/%Y');


After i make a procedure to populate the tables with a cursor and

drop procedure if exists POP_TAB_cursor ;

delimiter //


CREATE PROCEDURE POP_TAB_cursor()

begin

declare data_vizita,ora_vizita,nume_pac,prenume_pac,nume_med,prenume_med,cabinet varchar(30);
declare id_medic,id_pacient,id_cabinet int;
declare stop_cursor condition for 1329;
declare pop_tab cursor for select (Data_Vizita,Ora_intrare,Nume_pacient,Prenume_pacient,Nume_medic,Prenume_medic
,Cabinet,id_medic,id_pacient,id_cabinet) from intermediar;

declare exit handler for stop_cursor -- definire handler de tip exit
begin
end;
       
       
set foreign_key_checks = 0;

open pop_tab;


loop
fetch pop_tab into data_vizita,ora_vizita,nume_pac,prenume_pac,nume_med,prenume_med
,cabinet,id_medic,id_pacient,id_cabinet;

insert ignore into Pacienti(id,nume,prenume) values(id_pacient,nume_pac,prenume_pac);
insert ignore into Medici(id,nume,prenume) values (id_medic,med,prenume_med);
insert ignore into Cabinet(id,nume) values (id_cabinet,cabinet);

insert ignore into Vizite(id_medic,id_pacient,id_cabinet) values(id_medic,id_pacient,id_cabinet);



set foreign_key_checks = 1;
   
end loop;

close pop_tab;
   
END //


DELIMITER ;

call POP_TAB_cursor();

it told me
11:00:13 call POP_TAB_cursor() Error Code: 1241. Operand should contain 1 column(s) 0.000 sec


For the trigger part i found much harder they said to use last_insert_id or select.But why tu use last insert id? because the id for medic ,pacient,cabinet is load from a files.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36393
453
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Iovitu George wrote:For the trigger part i found much harder they said to use last_insert_id or select.But why tu use last insert id? because the id for medic ,pacient,cabinet is load from a files.

The trigger doesn't know that. I think your instructor is asking you to implement it this way so you can learn about how a generic trigger works. The loading from a file is to make it easy for you to test.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!