By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,888 Members | 1,573 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,888 IT Pros & Developers. It's quick & easy.

(simple) stored procedure question - 2nd set of eyes may be all thatsneeded

P: n/a
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_addToMiscData(
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_removeMiscData(
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.
Feb 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
E.T. Grey wrote:
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_addToMiscData(
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_removeMiscData(
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.


Try comp.databases.mysql for MySQL questions. This group is for PHP
questions.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.