• 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
Marshal
Posts: 37465
539
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.
 
chandn Kumar
Greenhorn
Posts: 2
Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello...
        Which  types you are facing problem in my sql . you are facing problem like database problems in MySQL/PHP Applications if yes in this problems solved different types..

1. Using mysql_* functions directly
I have little wrapper which allows to do $dbcon->query(“Select email from user where name=%s”,$name) – wrapper will detect query is being called with multiple parameters and will perform needed checks and query rewriting.

2. Not using auto_increment functionality.
Innodb tables do internal full table lock if auto_increment is used so using values generated elseware might be faster.

3. Using multiple databases.
There are some performance problems with many tables some are OS and File System dependent, others correspond to Innodb storage engine or using innodb_file_per_table option in particular.

4. Not using relations.
Joins are expensive and you can often do much better with denormalized data.

5. The n+1 pattern.
This most applies to subqueries Where Subselects with IN() become corellated even if they are not, and so using IN() list of values derived by previous query


Regard
Chandan Kumar 
 
Aramis Angmar
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey I have a similar project to do. Can you help me please?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!