Hi,
In my earlier post, I was making a mistake (though I didn't do so in
the posted text!) - I was passing the dob (the date field) in the
ddmmyyyy format. When I passed the date field in yyyymmdd format, the
stored procedure ran fine and the record got inserted. The problem is
MySQL hangs/goes in limbo quitely instead of throwing back an error.
Certainly smells like a bug. ???
I have psted the text of my original post at the end of this message.
--
Asif
I have the following database objects in a purely InnoDB database:
--
------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Person (
PersonID int 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 date CHECK DOB > '19000101',
nicNo varchar(13),
mrtlStts char(1) not null DEFAULT 'M' CHECK
mrtlStts in ('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Stts int 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), /* yyyymmdd */
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_ * 1000000) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 1000000) * 1000000)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 1000000)
* 1000000))
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 < '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;
--
------------------------------------------------------------------------------------------------------------------------------------
NOW, when I call the procedure like this:
Call PersonAdd ( 'Mr.', 'Tom', 'Dick', 'Harry', 'M',
'20061212', '12434321', 'S',
'314515431545432',
'www.website.com', 15 );
the MySQL goes in limbo!
I am constructing the above SQL string through VB6.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
