CODEWITHSUNDEEP
stored-proceduresbooleaninformix
Brandon Essler
Brandon Essler

Reputation: 770

How do you pass a boolean data type as a parameter for a stored procedure?

Say you have an existing stored procedure that accepts a single parameter, which happens to be of the BOOLEAN Informix data type:

test_bool_param(BOOLEAN)

What would the EXECUTE PROCEDURE statement look like for such a stored procedure?

Here's what I've tried, and failed, with:

EXECUTE PROCEDURE test_bool_param('true'); -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('false');-- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('TRUE'); -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('FALSE');-- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param(true);   -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(false);  -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(TRUE);   -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(FALSE);  -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(1);      -- [Error Code:  -674, SQL State: IX000]  Routine (test_bool_param) can not be resolved.
EXECUTE PROCEDURE test_bool_param(0);      -- [Error Code:  -674, SQL State: IX000]  Routine (test_bool_param) can not be resolved.
EXECUTE PROCEDURE test_bool_param('\1');   -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('\0');   -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('t');    -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('f');    -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('T');    -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param('F');    -- [Error Code: -1260, SQL State: IX000]  It is not possible to convert between the specified types.
EXECUTE PROCEDURE test_bool_param(t);      -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(f);      -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(T);      -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(F);      -- [Error Code:  -201, SQL State: 42000]  A syntax error has occurred.
EXECUTE PROCEDURE test_bool_param(\1);     -- [Error Code:  -202, SQL State: IX000]  An illegal character has been found in the statement.
EXECUTE PROCEDURE test_bool_param(\0);     -- [Error Code:  -202, SQL State: IX000]  An illegal character has been found in the statement.

Upvotes: 4

Views: 8421

Answers (1)

Brandon Essler
Brandon Essler

Reputation: 770

These EXECUTE PROCEDURE calls do actually succeed:

EXECUTE PROCEDURE test_bool_param('t');     -- Result set fetched - SUCCESS
EXECUTE PROCEDURE test_bool_param('f');     -- Result set fetched - FAILURE
EXECUTE PROCEDURE test_bool_param('T');     -- Result set fetched - SUCCESS
EXECUTE PROCEDURE test_bool_param('F');     -- Result set fetched - FAILURE

But only when any checks in the stored procedure also compare to one of 't', 'f', 'T', or 'F':

CREATE PROCEDURE test_bool_param
(in_param BOOLEAN)
RETURNING VARCHAR(8)
IF (in_param = 't') THEN
        RETURN 'SUCCESS';
ELSE
        RETURN 'FAILURE';
END IF;
END PROCEDURE;

Upvotes: 4

Related Questions