Numeric Sequence In SQL

Few years ago I came accross an issue, when I needed to find smallest continuous integer in a sequence. My data were like 1, 2, 3, 100, 101, 102, 520, 521, 530 and the task to find lets say next continuous bigger than 100. Long story short, fastest and the final SQL was:

SELECT t1.num+1 FROM table AS t1 
WHERE NOT EXISTS 
   (SELECT t2.num FROM table AS t2  WHERE t1.num+1 = t2.num) 
   AND t1.num>=100
GROUP BY t1.num ORDER BY t1.num LIMIT 1

Today, the issue was a little more difficult. I have to find smallest continuous 2D sequence in any direction.

The data are:

x       y
-1000   -1000
 -200    -100
 -100    -100
 -100       0
   0      100
 100      100
...

This data creates some kind of grid. By direction it is meant that you want to find the closest unset grid position starting from [0, 0] going to any direction defined by random vector [53, -45]. Grid size is 100. This issue required also dynamic table name what brings prepared statements into the scene. There may exist even a simplier solution, but this procedure executes in a 0.000something sec. when crawling more than a few thousands of entries.

CREATE PROCEDURE `getEmpty`(IN `tbl` VARCHAR(50), IN `ix` FLOAT, IN `iy` FLOAT, OUT `ox` INT, OUT `oy` INT)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
label:BEGIN
	DECLARE px INT DEFAULT 0;
	DECLARE py INT DEFAULT 0;
	
	WHILE TRUE DO
		SET @res = NULL;
		SET @dsql = CONCAT('SELECT x INTO @res FROM ', tbl, 
				' WHERE x = FLOOR(', px,' / 100) * 100 AND y = FLOOR(', py, ' / 100) * 100'); 
		PREPARE stm from @dsql;
		EXECUTE stm;
		DEALLOCATE PREPARE stm;
	
		IF 
			@res IS NULL
		THEN 
			SELECT FLOOR(px / 100) * 100, FLOOR(py / 100) * 100 INTO ox, oy;
			LEAVE label; 
		END IF;
		
		SET px = px + ix;
		SET py = py + iy;
	END WHILE;
END

Now all you have to do is call created procedure:

CALL getEmpty("myTable", RAND() * 200 - 100, RAND() * 200 - 100, @x, @y);
SELECT @x, @y;

While testing it on my favourite HeidiSQL tool, everything worked like a charm. The real usage issue appeared when I tried it in PHP I received an error message saying:

1312: can't return a result set in the given context

The error is caused by prepared statements. PHP MySQL extension doesn’t support features like prepared statements and stored procedures. For using these features you have to use mysqli extension. So I switched to mysqli and everything worked.

Leave a comment

Please be polite and on topic. Your e-mail will never be published.