• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL problem

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 2
Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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  
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey I have a similar project to do. Can you help me please?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic