Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Prepared statement error help.

    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

  2. #2
    Join Date
    Sep 2008
    Posts
    8
    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')
    Last edited by BargainPredator; 03-11-09 at 19:11.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    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

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    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.

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •