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]