In the past three posts, we have written metaprograms to create functions for comparing objects of built-in types, user-defined distinct types, ROW types, and ordinary ARRAY types. In today’s post, we will write a single metaprogram that can create comparison functions for all four.
The only obstacle to writing a generic metaprogram for built-in, UDDT, ROW, and ordinary ARRAY types is that we do not know how to determine which of the four it is from the name that is passed to our metaprogram. It turns out that there is a column named METATYPE in the SYSCAT.DATATYPES catalog view that we can use. Let’s look at a short example:
CREATE TYPE SIMPLETYPE AS INTEGER WITH COMPARISONS@
CREATE OR REPLACE TYPE ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE ARRAYTYPE AS INTEGER ARRAY[]@
SELECT VARCHAR(TYPENAME, 10) TYPENAME, METATYPE
FROM SYSCAT.DATATYPES
WHERE TYPENAME IN ('SIMPLETYPE', 'ROWTYPE', 'ARRAYTYPE', 'INTEGER')@
This produces a result like the following:
TYPENAME METATYPE ---------- -------- ROWTYPE F ARRAYTYPE A SIMPLETYPE T INTEGER S 4 record(s) selected.
We can use this to write a generic function that inspects this SYSCAT.DATATYPES view before deciding which kind of comparison function to create (implementations have been elided for readability, but are simply the code from the previous three articles in this series):
CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(SCHEMA_NAME VARCHAR(128),
MODULE_NAME VARCHAR(128),
TYPE_NAME VARCHAR(128))
BEGIN
DECLARE META_TYPE CHAR(1);
...
SELECT METATYPE INTO META_TYPE
FROM SYSCAT.DATATYPES
WHERE TYPESCHEMA = SCHEMA_NAME
AND (TYPEMODULENAME IS NULL AND MODULE_NAME IS NULL OR TYPEMODULENAME = MODULE_NAME)
AND TYPENAME = TYPE_NAME;
IF (META_TYPE = 'F') THEN
-- Create ROW type EQ function
ELSEIF (META_TYPE = 'T' OR META_TYPE = 'S') THEN
-- Create UDDT/built-in type EQ function
ELSEIF (META_TYPE = 'A') THEN
-- Create ARRAY type EQ function
ELSE
SIGNAL SQLSTATE VALUE '75000' SET MESSAGE_TEXT = 'Unrecognized metatype. Cannot create comparison function.';
END IF;
END@
Now, we have a generic metaprogram that can create EQ functions for all four metatypes. The following example demonstrates its use on the built-in, row, and array metatypes:
SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TESTSCH.ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE TESTSCH.ARRAYTYPE AS INTEGER ARRAY[]@
CALL CREATE_EQ_FUNCTION('SYSIBM', NULL, 'INTEGER')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ROWTYPE')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ARRAYTYPE')@
BEGIN
DECLARE SIMPLE1 INTEGER;
DECLARE SIMPLE2 INTEGER;
DECLARE ROW1 TESTSCH.ROWTYPE;
DECLARE ROW2 TESTSCH.ROWTYPE;
DECLARE ARRAY1 TESTSCH.ARRAYTYPE;
DECLARE ARRAY2 TESTSCH.ARRAYTYPE;
SET SIMPLE1 = 1;
SET SIMPLE2 = 1;
SET ROW1.ONE = 1;
SET ROW1.TWO = 1;
SET ROW2.ONE = 1;
SET ROW2.TWO = 1;
SET ARRAY1[1] = 1;
SET ARRAY2[1] = 1;
CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
SET SIMPLE2 = 2;
SET ROW2.TWO = 2;
SET ARRAY2[1] = 2;
CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
END@
This should result in output like the following:
DB20000I The SET SERVEROUTPUT command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
Return Status = 0
CREATE OR REPLACE FUNCTION EQ(LEFT SYSIBM.INTEGER, RIGHT SYSIBM.INTEGER)
SPECIFIC EQ_SIMPLE CONTAINS SQL RETURNS INTEGER
BEGIN
DECLARE RESULT INTEGER;
IF (LEFT = RIGHT) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
RETURN RESULT;
END
Return Status = 0
CREATE OR REPLACE FUNCTION EQ(LEFT TESTSCH.ROWTYPE, RIGHT TESTSCH.ROWTYPE)
SPECIFIC EQ_ROW READS SQL DATA RETURNS INTEGER
BEGIN
DECLARE RESULT INTEGER;
IF (LEFT.ONE = RIGHT.ONE AND LEFT.TWO = RIGHT.TWO) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
RETURN RESULT;
END
Return Status = 0
CREATE OR REPLACE FUNCTION EQ(LEFT TESTSCH.ARRAYTYPE, RIGHT TESTSCH.ARRAYTYPE)
RETURNS INTEGER SPECIFIC EQ_ARRAY READS SQL DATA
BEGIN
DECLARE ID INTEGER DEFAULT 1;
DECLARE RESULT INTEGER;
IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
SET RESULT = 1;
WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
SET RESULT = EQ(LEFT[ID], RIGHT[ID]);
SET ID = ID + 1;
END WHILE;
ELSE
SET RESULT = 0;
END IF;
RETURN RESULT;
END
DB20000I The SQL command completed successfully.
EQ(SIMPLE1, SIMPLE2): 1
EQ(ROW1, ROW2): 1
EQ(ARRAY1, ARRAY2): 1
EQ(SIMPLE1, SIMPLE2): 0
EQ(ROW1, ROW2): 0
EQ(ARRAY1, ARRAY2): 0[/sourcecode]
That ends this series on metaprogramming for now. For the next two weeks, I am on vacation. Hopefully, I will have interesting new content to share when I get back.