Informix

 View Only
  • 1.  syntax error on EXECUTE statement

    Posted 29 days ago
    IDS 14.10.FC7W1 on RHEL 8.5

    I know I'm missing something simple.  I'm preparing a statement early in a procedure, and then later trying to execute it, but I get a "-201 Syntax Error":

        LET p_sql_statement = "update user_roles_tbl " ||  
                              "   set user_roles = bitandnot(user_roles, ? )" || 
                              " where emp_num = ? ";
        PREPARE updt_user_roles FROM p_sql_statement;
    
           .
           .
           .
    
        IF p_exists > 0 
        THEN
            FOREACH SELECT role_mask
                      INTO p_role_mask
                      FROM role_mask_tbl
                     WHERE base_role = "N"
    
                EXECUTE updt_user_roles USING p_role_mask, i_emp_num;
    #                   ^
    #  201: A syntax error has occurred.
    #
           .
           .
           .
    
        END FOREACH;
    ​


    I've got the correct number of variables for the number of '?' placeholders.  

    Any help appreciated.

    ------------------------------
    Mark Collins
    ------------------------------


  • 2.  RE: syntax error on EXECUTE statement

    Posted 29 days ago
    Edited by Mark Collins 29 days ago
    to clarify, p_role_mask is a locally defined variable within the procedure, and i_emp_num is passed to the procedure as an input parameter.  Both variables are of the correct type for the prepared statement.

    Also, EXECUTE IMMEDIATE works:

    IF p_exists > 0
    THEN
        FOREACH SELECT role_mask
                  INTO p_role_mask    
                  FROM role_mask_tbl    
                 WHERE base_role = "N"    
    
            LET p_sql_statement = "update user_roles_tbl " || 
                                  "   set user_roles = bitandnot(user_roles, " || p_role_mask || ")" ||
                                  " where emp_num = " || i_emp_num;
            EXECUTE IMMEDIATE p_sql_statment;
          .   
          .  
          .
        END FOREACH;​​


    But because the FOREACH loop executes several times, I was trying to get the parsing/optimization overhead moved outside the loop for performance reasons.  With the EXECUTE IMMEDIATE, this logic takes over 15 seconds to complete.  I'm hoping that having the PREPARE done outside of the loop will yield significant performance improvement.

    Thanks in advance.



    ------------------------------
    Mark Collins
    ------------------------------



  • 3.  RE: syntax error on EXECUTE statement

    Posted 29 days ago

    I think I found the answer, but it's not the one I want.  And I'm not sure why the syntax error is being called out for the EXECUTE statement, as I now believe the problem actually is with the PREPARE statement.

    In the SQL Syntax manual, there are two different sections that describe restrictions on the PREPARE statement.  One section reads:

    Restricted Statements in Single-Statement Prepares
    In general, you can prepare any data manipulation language (DML) statement.  n Informix, you can prepare any single SQL statement except for the following
    statements:
    * ALLOCATE COLLECTION
    * ALLOCATE DESCRIPTOR
    * ALLOCATE ROW
    * CLOSE
    * CONNECT
    * CREATE FUNCTION FROM
    * CREATE PROCEDURE FROM
    * CREATE ROUTINE FROM
    * DEALLOCATE COLLECTION
    * DEALLOCATE DESCRIPTOR
    * DEALLOCATE ROW
    * DECLARE
    * DESCRIBE
    * DISCONNECT
    * EXECUTE
    * EXECUTE IMMEDIATE
    * FETCH
    * FLUSH
    * FREE
    * GET DESCRIPTOR
    * GET DIAGNOSTICS
    * INFO
    * LOAD
    * OPEN
    * OUTPUT
    * PREPARE
    * PUT
    * SET AUTOFREE
    * SET CONNECTION
    * SET DEFERRED_PREPARE
    * SET DESCRIPTOR
    * UNLOAD
    * WHENEVER


    However, there is another section which includes this caveat:

    In SPL routines, a prepared object can include the text of no more than one SQL
    statement, and that statement must be either an EXECUTE FUNCTION, EXECUTE
    PROCEDURE, or SELECT statement, but the SELECT statement cannot include the
    INTO variable, INTO TEMP, or FOR UPDATE clause.


    With that in mind, I would have expected the -201 to be attached to the PREPARE statement.  Regardless, it appears that the answer is that we can't use PREPARE inside stored procedures for anything other than SELECT or EXECUTE PROCEDURE/FUNCTION.

    Darn.



    ------------------------------
    Mark Collins
    ------------------------------



  • 4.  RE: syntax error on EXECUTE statement

    IBM Champion
    Posted 29 days ago
    As I noted in my other response. The PREPARE and the particular UPDATE statement are not the problem, it is just the EXECUTE. PREPARE is fine in a stored procedure, it's just that it is only useful if you can DECLARE a cursor against it, ie it is returning some data.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: syntax error on EXECUTE statement

    Posted 29 days ago

    I see what you mean.  The manual entry for EXECUTE states:

    Use this statement with Informix ESQL/C.







    ------------------------------
    Mark Collins
    ------------------------------



  • 6.  RE: syntax error on EXECUTE statement

    IBM Champion
    Posted 29 days ago
    Yup, while the manual page for EXECUTE IMMEDIATE says: 

    Use this Dynamic SQL statement with IBM Informix ESQL/C and SPL.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: syntax error on EXECUTE statement

    IBM Champion
    Posted 29 days ago
    You cannot use "EXECUTE stmt_id" in an SPL routine, only in ESQL/C. In SPL you will have to EXECUTE IMMEDIATE p_sql_statement; but you will have to fill in the values instead of using the placeholder question marks.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------