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 (
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.
Also I need to know as to which MySQL-5.0.22 data-types map to which
VB6 types so that I can map procedure parameters correctly in ADO.
Since I haven't been able to map parameter data-types, I am having the
direct "call" syntax constructed in ADO and using the same.
--
Thanks in advance,
Asif
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
