Software Engineering on the loose

I recently found a case study written by Mark Hooper (Project Manager - University of Bedfordshire - Academic Into Business Computing Centre). It’s about my last finished project (Cardguy) and I think Mark really sums up what I have achieved with the project. At the end of the day the ABC prooved to be useful as we had a satisfied client and I received a lot of experience.

This annoying error kept popping up when I tried to run my stored procedure. After discussing this on forums plus doing an intensive search on Google I finally have a solution.

What I wanted to do is simple. Create a MySQL Stored Procedure which contains a simple SELECT statement.

This is what I’ve been doing:

DROP DATABASE IF EXISTS mytest;
CREATE DATABASE mytest;
COMMIT;
USE mytest;
CREATE TABLE tt (s1 INT);
COMMIT;
INSERT INTO tt VALUES (5);

Then create the procedure itself:

DELIMITER //CREATE PROCEDURE proc () SELECT * FROM tt; //

After this step I simply wanted to launch the procedure:

CALL proc () //

This then gave me the error message ‘SQL Error: PROCEDURE mytest.proc can’t return a result set in the given context‘.

I had to investigate this error as it was rather disturbing. However there were unclear things first. If you read the MySQL manual’s relevant part for Stored Procedures it clearly states that you are not allowed to use simple SELECT statments which return results sets:

Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to capture column values into variables, SHOW statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can’t return a result set in the given context error occurs (ER_SP_BADSELECT).

Fair enough, but I wasn’t using this manual, I was using MySQL’s downloadable PDF manual for learning about Stored Procedures. In there, the example which I have presented is used and no such error appears.

The only difference is that I was using HeidiSQL whereas in the manual the MySQL command line was used. I tried to call the procedure from the command line and the fact that it was actually working shocked me.

So what now?

It turns out that HeidiSQL uses the ZeosLib MySQL component. And the error lies within this component. So I had to abandon the idea of using HeidiSQL (or even phpMyAdmin - surprisingly it produces the same error) and change to either MySQL Query Browser (or to SQLyog) as it uses the MySQL API directly.

Ever heard of Google Suggest? I bet you did. Let me show you a simple Ajax-PHP-MySQL code based on suggest. This script will search a database and nest your result while and when you type.

I have used and modified the example shown on W3Schools.com.

What I have done, I left the Javascript file as it is and created a PHP file and instead of filling up the arrays I do the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

include("connection.php");
function suggest() {
//get the q parameter from URL
$q=addslashes($_GET["q"]);
 
//connect to database
makeConnection();
//make the query
$select = "SELECT * FROM (table) WHERE (field) LIKE '".$q."%'";
$result = mysql_query($select) or die (mysql_error());
//do whatever you want to do with the result
//disconnect from database
disconnect();
}