Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

my sql store procedure error this is not running

 
Jackson Martin
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys
i am new in mysql
this query is not running.
Can you please help me to solve this



CREATE Table tblfeedback
(
`FeedBackID` INT AUTO_IncremeNT,
`UserID` INT,
`Inserted_TS` TIMESTAMP ,
`FeedBackValue` VARCHAR(100),
PRIMARY KEY (FeedBackID)
);

CREATE PROCEDURE tblFeedBack_InsertUpdate
(
IN U_ID INT,
IN FB_Value VARCHAR(50)
)
BEGIN


IF ((Select COUNT(*) From 'tblfeedback') < 3)
BEGIN
INSERT INTO tblfeedback (`UserID`,`FeedBackValue`) VALUES (1,2);
END
ELSE
BEGIN
DECLARE @MostRecentFID INT;

Select `FeedBackID` FROM tblfeedback
WHERE UID = U_ID
ORDER BY `Inserted_TS` DESC
LIMIT 1
INTO @MostRecentFID;

UPDATE tblfeedback
SET `FeedBackValue` = @FeedBackValue
Where `FeedBackID` = @MostRecentFID
END
END
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If this is a question about JDBC then it would help if you showed us the Java code which tries to run that stored procedure.

Or if it's strictly a MySQL question and Java has nothing to do with it, then showing us what you are typing at the MySQL command line and what response you get would be helpful. Or however you are trying to run it, showing us some evidence about what happens.
 
Jackson Martin
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Modified it still error

CREATE Table tblfeedback
(
`FeedBackID` INT AUTO_IncremeNT,
`UserID` INT,
`Inserted_TS` TIMESTAMP ,
`FeedBackValue` VARCHAR(100),
PRIMARY KEY (FeedBackID)
);

CREATE PROCEDURE tblFeedBack_InsertUpdate
(
IN U_ID INT,
IN FB_Value VARCHAR(50)
)
BEGIN
declare @cnt int;
Select COUNT(*) From 'tblfeedback' into @cnt
IF ((@cnt < 3)
BEGIN
INSERT INTO tblfeedback (`UserID`,`FeedBackValue`) VALUES (U_ID,'FB_Value');
END
ELSE
BEGIN
DECLARE @MostRecentFID INT;

Select `FeedBackID` FROM tblfeedback
WHERE UserID = U_ID
ORDER BY `Inserted_TS` DESC
LIMIT 1
INTO @MostRecentFID;

UPDATE tblfeedback
SET `FeedBackValue` = @FeedBackValue
Where `FeedBackID` = @MostRecentFID
END
END
 
Jackson Martin
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is error while running query
coderanch.PNG
[Thumbnail for coderanch.PNG]
This is error
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm looking at the tutorial which explains how to declare variables in a MySQL stored procedure. None of their examples has a semicolon at the end of the declaration. Yours does. And since that's where the error message is pointing, perhaps you shouldn't have a semicolon there?

(I should say that I have no experience with stored procedures.)
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:I'm looking at the tutorial which explains how to declare variables in a MySQL stored procedure. None of their examples has a semicolon at the end of the declaration. Yours does. And since that's where the error message is pointing, perhaps you shouldn't have a semicolon there?

(I should say that I have no experience with stored procedures.)


';' aren't used in the MySQL console client because they mean something else (I think end of thing I want to execute).
They are legitimate elsewhere, eg the workbench or in scripts.
See here.

So, if the OP would tell us how they are running this (as you asked ealrier) we might be able to narrow things down.
 
Jackson Martin
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Error Solved
Question closed.....
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic