Hi,
I am replying to my own post - only to add that in response to the
MySQL misbehaving, I just kill it and restart it. More than once, I
also restarted the service, drop the database and re-created the
database and all the tables, procedures, triggers, grants, etc. But
still when I call the stored procedure, MySQL hangs.
Any help?
--
Thanks in advance,
Asif
On 8/16/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:
Hi,
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK Gender in
('M','F'),
dob dateCHECK DOB > '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK mrtlStts in
('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /* 1=Active,
2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i <= l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i > l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100) *
100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_,
dob_,
nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts <> 'S' and
new.mrtlStts <> 'M' and
new.mrtlStts <> 'D' and
new.mrtlStts <> 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob <
