I have been busting my nut over this for pretty much most of the day and
it is driving me nuts. I posted this to an mySQL ng yesterday and I have
not had any response (I'm pulling my hair out here).
Its really a very simple stored procedure but I simply can't seem to get
it to work.
I have a simple table misc_data described as ff:
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid | int(10) unsigned | NO | | | |
| mid | int(10) unsigned | NO | | | |
| expid | int(10) unsigned | NO | | | |
+-------+------------------+------+-----+---------+-------+
This is my stored proc:
Delimiter $
create procedure sp_addToMiscDat a(
IN UID varchar(64),
IN MID INT(10),
IN EID INT(10)
)
BEGIN
DECLARE tempid INT(10);
select mid INTO tempid from misc_data where uid=UID and mid=MID ;
IF (tempid IS NULL) THEN
insert into misc_data values(UID,MID, EID) ;
ELSE
delete from misc_data where uid=UID and mid=MID ;
insert into misc_data values(UID,MID, EID) ;
END IF;
END$
the idea is that there will be distinct entries for uid and mid columns,
so if we are adding data that has an existing record with the same value
in the mid column (for the user) we will simply overwrite that record,
otherwise, simply add the record. The stored proc looks ok to me, but
whenever I call it, it deletes EVERYTHING in the misc_data table and
simply enters one new record (the data beeing added). Help !!!
To delete a specified record, I also have this stored proc:
Delimiter $
create procedure sp_removeMiscDa ta(
IN UID varchar(64),
IN MID INT
)
BEGIN
delete from misc_data m where m.uid=UID AND m.mid=MID;
END$
This stored proc when called simply deletes all records in the table,
yet still, when i run the delete statement at the command prompt, it
works correctly. wtf is going on?. Any suggestions will help. Hopefully,
it should not be difficult for any of the gurus out there, since these
are relatively (at least should be) simple stored procs.
it is driving me nuts. I posted this to an mySQL ng yesterday and I have
not had any response (I'm pulling my hair out here).
Its really a very simple stored procedure but I simply can't seem to get
it to work.
I have a simple table misc_data described as ff:
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid | int(10) unsigned | NO | | | |
| mid | int(10) unsigned | NO | | | |
| expid | int(10) unsigned | NO | | | |
+-------+------------------+------+-----+---------+-------+
This is my stored proc:
Delimiter $
create procedure sp_addToMiscDat a(
IN UID varchar(64),
IN MID INT(10),
IN EID INT(10)
)
BEGIN
DECLARE tempid INT(10);
select mid INTO tempid from misc_data where uid=UID and mid=MID ;
IF (tempid IS NULL) THEN
insert into misc_data values(UID,MID, EID) ;
ELSE
delete from misc_data where uid=UID and mid=MID ;
insert into misc_data values(UID,MID, EID) ;
END IF;
END$
the idea is that there will be distinct entries for uid and mid columns,
so if we are adding data that has an existing record with the same value
in the mid column (for the user) we will simply overwrite that record,
otherwise, simply add the record. The stored proc looks ok to me, but
whenever I call it, it deletes EVERYTHING in the misc_data table and
simply enters one new record (the data beeing added). Help !!!
To delete a specified record, I also have this stored proc:
Delimiter $
create procedure sp_removeMiscDa ta(
IN UID varchar(64),
IN MID INT
)
BEGIN
delete from misc_data m where m.uid=UID AND m.mid=MID;
END$
This stored proc when called simply deletes all records in the table,
yet still, when i run the delete statement at the command prompt, it
works correctly. wtf is going on?. Any suggestions will help. Hopefully,
it should not be difficult for any of the gurus out there, since these
are relatively (at least should be) simple stored procs.
Comment