[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to handle 0 rows returned in prepared statement

Posted on 2013-06-14
7
Medium Priority
?
647 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 30

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

650 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