Prepared statement error help.

ozzii · #1 (**permalink**) 03-10-09, 17:50

Hi

I have the following stored procedure:

Code:

CREATE PROCEDURE count_books(IN inSearchString TEXT)
BEGIN
      PREPARE statement FROM
	    "SELECT COUNT(*) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
    WHERE  MATCH (bk_search.book_title, bk_search.book_desc)
          AGAINST (?)";

     SET @p1 = inSearchString;
     
     EXECUTE statement USING @p1;
END

I call the procedure using the following statement:

Code:

CALL count_books('flower')

butI keep getting the following error message:

Code:

SQL Error: PROCEDURE count_books can't return a result set in the given context

I am using mySQL 5. Why is it throwing this error message??? Plus there isnt even much info on this error message on the mySQL site!

Any help will be appreciated.

Thanks

BargainPredator · #2 (**permalink**) 03-11-09, 18:06

I don't understand why you use the Prepared Statement

Code:

CREATE FUNCTION count_books(IN inSearchString TEXT)  RETURNS INT
BEGIN 

    DECLARE cnt INT;

    SET cnt = (SELECT COUNT(*) FROM tbl_books as books INNER JOIN tbl_book_search AS bk_search ON bk_search.book_id = books.book_id WHERE MATCH (bk_search.book_title, bk_search.book_desc) AGAINST (inSearchString)); 

    RETURN cnt;

END

Code:

SELECT genSitemaps('flower');

OR query directly :

Code:

SELECT COUNT(*) FROM tbl_books as books INNER JOIN tbl_book_search AS bk_search ON bk_search.book_id = books.book_id WHERE MATCH (bk_search.book_title, bk_search.book_desc) AGAINST ('flower')

bklr · #3 (**permalink**) 03-12-09, 03:11

Code:

try like this
CREATE PROCEDURE count_books(@cnt int output,
IN inSearchString TEXT)
AS 
SET NOCOUNT ON
BEGIN
	    SELECT @cnt =COUNT(*) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
    WHERE  MATCH (bk_search.book_title, bk_search.book_desc)
          AGAINST  (inSearchString)
     
END
SET NOCOUNT OFF

ozzii · #4 (**permalink**) 03-12-09, 15:12

Quote:

Originally Posted by bklr

Code:

try like this
CREATE PROCEDURE count_books(@cnt int output,
IN inSearchString TEXT)
AS 
SET NOCOUNT ON
BEGIN
	    SELECT @cnt =COUNT(*) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
    WHERE  MATCH (bk_search.book_title, bk_search.book_desc)
          AGAINST  (inSearchString)
     
END
SET NOCOUNT OFF

I think i've worked it out why am getting the error message. The original query works fine from the mysql command prompt. I only get the error when executing it from within Heidisql. So it must a bug within Heidisql.

ozzii · #5 (**permalink**) 03-13-09, 13:57

Quote:

Originally Posted by bklr

Code:

try like this
CREATE PROCEDURE count_books(@cnt int output,
IN inSearchString TEXT)
AS 
SET NOCOUNT ON
BEGIN
	    SELECT @cnt =COUNT(*) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
    WHERE  MATCH (bk_search.book_title, bk_search.book_desc)
          AGAINST  (inSearchString)
     
END
SET NOCOUNT OFF

HI,

As per my earlier post the original procedure works fine from the command line. However I have another problem now. I have amended the original procedure as follows:

Code:

CREATE PROCEDURE count_books(IN inSearchString TEXT)
BEGIN
      PREPARE statement FROM
	 "SELECT COUNT(DISTINCT books.book_id) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
         WHERE  (books.book_status = 'Approved')
         IF (? <> "") THEN
                 AND MATCH (bk_search.book_title, bk_search.book_desc)
                     AGAINST (?)
         END IF
         ";

     SET @p1 = inSearchString;
     
     EXECUTE statement USING @p1, @p1;
END

But i keep getting the following error message:

Code:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 
'IF (? <> ") THEN
  AND MATCH (bk_search.book_title, bk_search.book_desc)'

Any idea why am getting this error? Is there something wrong with the IF statement?

Internet Services

Web Development

Digital Marketing

Consumer Tech