Oninit ifxtap / Informix unit testing
Talk to an expert →

Oninit® ifxTAP — Testing Your Own SPL

Status: pre-release. Every pattern on this page is a test in the ifxTAP source tree (tests/examples/06_spl.sql and tests/spl/01_scenarios.sql) and passes on Informix 14.10 and 15.0.

Stored procedures and functions are where an Informix application keeps its rules, and they are the part that is hardest to test from outside. ifxTAP tests them from the inside: the test file creates your routine, or calls the one already in the database, and asserts on what it returned, what it changed and how it failed. The file runs in a transaction that the runner rolls back, so a routine that inserts, updates or deletes leaves nothing behind.

Call it and check the result

A function that returns a value can be called inside a SELECT, so its result can go straight into tap_is: 

CREATE FUNCTION add_interest(p_balance DECIMAL(10,2), p_pct INTEGER) RETURNING DECIMAL(10,2)
    RETURN p_balance + (p_balance * p_pct / 100);
END FUNCTION;

EXECUTE PROCEDURE tap_is((SELECT add_interest(200, 10) FROM sysmaster:sysdual), 220.00, '10% on 200 is 220');

tap_function_returns('add_interest', 'DECIMAL') checks the declared return types, and tap_has_function, tap_is_procedure and tap_can check that the routine exists. The test creates its own copy of the routine when the file defines it; to test the routine that is already installed, just call it.

Wrap what a SELECT cannot call

Informix does not let a function that runs DDL be called inside a SELECT (it fails with SQLCODE -675), and a function that changes data is best called the same way, outside one. Put the call in a small wrapper procedure inside the test file and run the assertions from there. The assertions are ordinary procedures, so they work inside your own: 

CREATE PROCEDURE t_bump()
    DEFINE v_r INTEGER;
    LET v_r = bump(2);
    EXECUTE PROCEDURE tap_is(v_r, 1, 'bump returns 1');
    EXECUTE PROCEDURE tap_is((SELECT bal FROM acct WHERE id = 2), 201, 'bump added 1 to the balance');
END PROCEDURE;
EXECUTE PROCEDURE t_bump();

Any assertion inside the wrapper counts toward the file's plan like one written at the top level.

Iterator functions

A function that returns several rows is read with TABLE(). tap_isnt_empty and tap_is_empty take the query as text: 

EXECUTE PROCEDURE tap_isnt_empty('SELECT * FROM TABLE(rich(150))', 'rich(150) returns a row');
EXECUTE PROCEDURE tap_is_empty('SELECT * FROM TABLE(rich(9999))', 'rich(9999) returns none');

Errors the routine raises

RAISE EXCEPTION gives the caller an SQLCODE and a message. tap_throws_ok checks the code and tap_throws_like checks the message. A routine that handles its own error with ON EXCEPTION is tested on both paths: the normal one and the one that would have failed. 

EXECUTE PROCEDURE tap_throws_ok('EXECUTE PROCEDURE withdraw(1, 500)', -746, 'withdraw raises -746 when funds are short');
EXECUTE PROCEDURE tap_throws_like('EXECUTE PROCEDURE withdraw(1, 500)', 'insufficient%', 'the message from RAISE EXCEPTION can be matched');

For an error the server raises rather than your routine, the text available to SPL is only the short error-information string, usually the name of the object involved (in an ANSI database it carries the owner, so match with '%name%'). Match on the SQLCODE for those.

OUT and INOUT parameters

An OUT or INOUT variable must have a value before the call. Without one the server raises SQLCODE -696, “variable has undefined value”, and the test stops. Set it with LET, then call: 

CREATE PROCEDURE sp_total(a INTEGER, b INTEGER, OUT p_sum INTEGER)
    LET p_sum = a + b;
END PROCEDURE;

CREATE PROCEDURE sp_total_w()
    DEFINE v_s INTEGER;
    LET v_s = 0;
    CALL sp_total(2, 3, v_s);
    EXECUTE PROCEDURE tap_is(v_s, 5, 'the OUT parameter holds 2 + 3');
END PROCEDURE;
EXECUTE PROCEDURE sp_total_w();

Defaults, nesting, recursion, loops

These need nothing special; call the routine and assert on what it did. Parameters with DEFAULT values are called with fewer arguments, a function that calls another is tested through its result, and a recursive function such as a factorial is called like any other. A routine that loops over a table with FOREACH sees the rows the test inserted a moment earlier, because it is the same transaction. Delete a row and run it again to test the other case.

Temporary tables and DDL

A routine that creates and drops its own temporary table works, and so does calling it twice. Because it runs DDL it must be called from a procedure or with CALL, not inside a SELECT. tap_check_test can assert that the refusal happens.

Triggers that call procedures

Fire the trigger with the DML it is defined on, then assert on the procedure's side effect: 

CREATE TRIGGER sp_trg INSERT ON sp_acct REFERENCING NEW AS n FOR EACH ROW (EXECUTE PROCEDURE sp_audit(n.id));
INSERT INTO sp_acct VALUES (7, 100);
EXECUTE PROCEDURE tap_is((SELECT COUNT(*) FROM sp_log WHERE acct = 7), 1, 'the trigger ran the procedure');

What to avoid

  • A routine that runs COMMIT WORK or ROLLBACK WORK ends the runner's transaction. The runner detects this at the end of the file and fails it, but a commit cannot be undone, so the routine's changes stay in the database.
  • Effects outside the database, such as SYSTEM calls and files written by TRACE or SET DEBUG FILE, are not rolled back. See How It Works.
  • A SERIAL value a routine uses is not given back by the rollback.

What ifxTAP does not do

There is no code coverage, no mocking of one routine by another, and no comparison of whole result sets in v1. Compare counts and chosen values, as the examples do.

To have the first tests written for you, from a file of routines, see Generating Tests from Your SPL: it finds every statement in a routine, fills in values, and writes SQL that runs each statement and the whole routine inside a rollback.

See the sixth example on the Examples page for a complete file, and the Assertion Reference for every routine named here.