Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: Stored Procedure Error

    Hello,

    I have this stored procedure, btu unable to create, DB Visualizer throws syntax error but couldnt identify where and what... pl help:

    create procedure theatre_getConfirmMoviePlayingInTheatre
    (pass_movieID CHAR(5), pass_showDATE char(6), pass_theatreID char
    (4)) RETURNING BOOLEAN;

    BEGIN
    DEFINE t_retVAL BOOLEAN;
    DEFINE t_count int;
    DEFINE temp_sql_err integer;
    DEFINE temp_isam_err integer;
    DEFINE return_string CHAR(255);

    t_retVAL = 'false';
    t_count = '0';

    t_count = select count(*) from boxofc_theatre_showschedules
    where THEATRE_ID = pass_theatreID and FEATURE_CODE like
    pass_movieID || '%' and SHOW_DATE = pass_showDATE and
    ACTIVE='true';

    IF (t_count > 0)
    THEN
    t_retVAL = 'true';
    ELSE
    t_retVAL = 'false';
    END IF;

    RETURN t_retVAL;

    on exception set temp_sql_err, temp_isam_err
    LET return_string = "ERROR"||','||temp_sql_err||','||temp_isam_err ;
    return return_string;
    end exception;
    END;
    END PROCEDURE;

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    the error is in this line and in all lines containing a value setting
    t_retVAL = 'false';

    to set a value in IFMX stored procedure, the syntax is like this;
    LET t_retVAL = 'false';

    for a select statement returning 1 row, use SELECT ... INTO variablename

    The "on exception" block has to be placed just after variable definitions and before any executable code.

    This version of your SP should compile fine:
    Code:
    create procedure theatre_getConfirmMoviePlayingInTheatre
    (pass_movieID CHAR(5), pass_showDATE char(6), pass_theatreID char
    (4)) RETURNING BOOLEAN;
    
    BEGIN
    DEFINE t_retVAL BOOLEAN;
    DEFINE t_count int;
    DEFINE temp_sql_err integer;
    DEFINE temp_isam_err integer;
    DEFINE return_string CHAR(255);
    
    on exception set temp_sql_err, temp_isam_err
    LET return_string = "ERROR"||','||temp_sql_err||','||temp_isam_err ;
    return return_string;
    end exception;
    
    LET t_retVAL = 'false';
    LET t_count = '0';
    
    select count(*) INTO t_count from boxofc_theatre_showschedules
    where THEATRE_ID = pass_theatreID and FEATURE_CODE like
    pass_movieID || '%' and SHOW_DATE = pass_showDATE and
    ACTIVE='true';
    
    IF (t_count > 0)
    THEN
    LET t_retVAL = 'true';
    ELSE
    LET t_retVAL = 'false';
    END IF;
    
    RETURN t_retVAL;
    
    END;
    END PROCEDURE;

  3. #3
    Join Date
    Aug 2011
    Posts
    32

    Smile Thank you.

    Hi,

    Thanks for debugging it. I did the edits same as you had told and it works like a charm now. Thank you.

Tags for this Thread

Posting Permissions

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