Solved

How to handle 0 rows returned in prepared statement

Posted on 2013-06-14
7
629 Views
Last Modified: 2013-06-14
I am using a prepared statement:

SET @MyQuery := CONCAT("SELECT 1 INTO @SVWexists FROM Quran.rootwordattributesrange WHERE SVW = ", MySVW);

Open in new window


If 0 rows is returned, then the value of @SVWexists seems to remain the same value it was prior to executing the statement, maybe because the result returned is/has a warning (I suspect it is returning "0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed")

1) How do I elegantly code for this in a stored procedure?
2) How can I see the response when the statement is executed in a stored procedure?

Thanks--
Sam
0
Comment
Question by:SAbboushi
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Randy Downs
ID: 39248178
You could just set @SVWexists to 0 before executing the SP. Then check if it's still 0 at the end of the procedure. If it is then exit gracefully with a message like "No rows found".
0
 

Author Comment

by:SAbboushi
ID: 39248307
Thanks -- I appreciate you responding.

Great idea!  It seems though that the select statement returning 0 rows messes up my error handling...

I am using a cursor with:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done := TRUE; 

Open in new window


and a LOOP that is exited (when cursor reaches the end) by:
		IF Done THEN 
			LEAVE Cursor_Loop; 
		END IF; 

Open in new window


It seems that when the select statement returns 0 rows, this also sets Done to True resulting in my LOOP being exited.

I am looking for some error handling for the select statement that doesn't interfere with the existing CONTINUE HANDLER...
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39248353
Can you post more of your code?  At first I was going to suggest setting @SVWexists to *NULL* initially and not zero, but I don't think that's your problem.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 29

Expert Comment

by:Randy Downs
ID: 39248373
You want some logic like an IF else statement to execute the meat of your script if rows returned are not 0.

IF rows not = 0 
 execute code
Else 
 Message "No rows to work with"
ENDIF

Open in new window

0
 

Author Comment

by:SAbboushi
ID: 39248418
Thanks-

Number-1: I get the logic, it's the code I need ; )

nemwsS1: code below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_rootwordattributesrangerows`(IN ClearFlag CHAR(1))
BEGIN 

	DECLARE MyID INTEGER;
	DECLARE MySV VARCHAR(7);
	DECLARE MySVW VARCHAR(11);
	DECLARE MysWID INTEGER;
	DECLARE MyeWID INTEGER;
	DECLARE MySubString VARCHAR(1000);
	DECLARE Myrangemarker CHAR(1);
	DECLARE i INTEGER;
	DECLARE Done BOOLEAN DEFAULT FALSE; -- For CONTINUE statement

	DECLARE MyCursorC CURSOR FOR
		SELECT ID, SV, sWID, eWID FROM Quran.rootwordattributes;

	DECLARE MyCursor CURSOR FOR
		SELECT ID, SV, sWID, eWID FROM Quran.rootwordattributes WHERE rangecreated IS NULL;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done := TRUE; 


	IF ClearFlag = "C" THEN

		TRUNCATE Quran.rootwordattributesrange;

		OPEN MyCursorC;

	ELSE

		OPEN MyCursor;
		
	END IF;
	
	Cursor_Loop:
	LOOP

		IF ClearFlag = "C" THEN

			FETCH MyCursorC INTO MyID, MySV, MysWID, MyeWID;
		
		ELSE

			FETCH MyCursor INTO MyID, MySV, MysWID, MyeWID;

		END IF;

		IF Done THEN 
			LEAVE Cursor_Loop; 
		END IF; 

		SET i := MysWID;

		WHILE i <= MyeWID DO
		
			SET MySVW := concat(MySV, ':', i);

			IF i = MysWID THEN 

				SET Myrangemarker := "S";

			ELSEIF i = MyeWID THEN

				SET Myrangemarker := "E";

			ELSE

				SET Myrangemarker := "M";

			END IF;

			SET @SVWexists := NULL;
			SET @MySVW = MySVW;

			SET @MyQuery := CONCAT("SELECT 1 INTO @SVWexists FROM Quran.rootwordattributesrange WHERE SVW = '", MySVW, "'");
			PREPARE MyQuery from @MyQuery; 
			EXECUTE MyQuery;
			DEALLOCATE PREPARE MyQuery;

			IF @SVWexists IS NULL THEN
			
				SET @MyQuery := CONCAT("INSERT INTO Quran.rootwordattributesrange(SVW, rangemarker) VALUES('", MySVW, "','", Myrangemarker, "')");
				PREPARE MyQuery from @MyQuery; 
				EXECUTE MyQuery;
				DEALLOCATE PREPARE MyQuery;

			END IF;

			SET @MyQuery := CONCAT("UPDATE Quran.rootwordattributes AS RWA SET RWA.rangecreated = ", '"', "Y", '"', " WHERE ID = ", MyID);
			PREPARE MyQuery from @MyQuery; 
			EXECUTE MyQuery;
			DEALLOCATE PREPARE MyQuery;
			
			SET i := i + 1;
			
		END WHILE;

	END Loop 
	Cursor_Loop;

END

Open in new window

0
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39248697
I think you can get around this issue by declaring a new CONTINUE HANDLER within your while block.  I'm not sure if you need to define a new block or not (I haven't tested, yet), but I would try this:

1) Line after "DECLARE Done..." add:
DECLARE Done_Inner BOOLEAN DEFAULT FALSE;

2) On the line after "WHILE...DO" add:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done_Inner := TRUE;

And then never check/ignore it.  I'm fairly certain this will work, as the handlers work only within their block and are temporarily overwritten in sub-blocks.


If this does *not* work, remove the new DECLARE .. HANDLER we jut added and do this (create a new sub-block that just contains the WHILE LOOP block):

1) *Before* the "WHILE...DO" line add:
WHILEBLOCK: BEGIN
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done_Inner := TRUE;

2) On the line after "END WHILE;" add:
END WHILEBLOCK;
0
 

Author Comment

by:SAbboushi
ID: 39248763
Good stuff - thanks!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.php tree directory? 5 56
test if query has no results 2 19
geting geocode of a user with using Google maps api 8 18
insert row field data graphically 3 5
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now