Informix

    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;​

IF p_exists > 0THEN    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.

    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 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.

IF p_exists > 0THEN    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.

    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 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.

IF p_exists > 0THEN    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.

    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 see what you mean.  The manual entry for EXECUTE states:

Use this statement with Informix ESQL/C.

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.

IF p_exists > 0THEN    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.

    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;​

    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;​