CloneRows

Code in this section is saved in a file called CloneRows.

Concepts

This example shows three major concepts:

A function AE called from a table function that returns more than one output row per input row.
A function AE called from a table function that uses a shaper.
Using dynamic AE environment variables in an SQL function call.

This AE can accept any input signature. Using query metadata it sets the output row data type definition equal to the input row signature. Based on the CLONE_COUNT AE environment variable, it clones the input row the specified number of times. It also uses the CLONE_COLUMN_NAMES environment variable to set the output column names.

Code

Note that this code supports two APIs: the AE shaper API to perform shaping and the AE function API to process the data. This code will be used in the section ApplyDriver Version 3.

// CloneRows.java
// Function AE
// Handles a variable number of input columns and data types
// Returns a clone of each input row a multiplier number of times
// Clone multiplier is an integer >= 1
// Uses a shaper
// Receives clone multiplier as an environment variable
// Receives return column names as an environment variable
// Must be called as a table function
package org.netezza.education;

import org.netezza.ae.*;

public class CloneRows {
    public static void runShaper(NzaeShaper ae) {
        try {
            runShaperImpl(ae);
        } catch (Throwable t) {
            t.printStackTrace();
            try { ae.userError(t.getMessage()); }
            catch (Throwable t2) {}
        }
    }

    private static void runShaperImpl(NzaeShaper aeShp) {
        String strCloneCount =
            aeShp.getEnvironment().getValue("CLONE_COUNT");
        if (strCloneCount == null) {
            String msg =
                "CLONE_COUNT environment variable not set";
            System.err.println(msg);
            aeShp.userError(msg);
            return;
        }
        int cloneCount = 0;
        try {
            cloneCount = Integer.parseInt(strCloneCount);
        } catch (NumberFormatException e) {}
        if (cloneCount < 1) {
            String msg =
                "CLONE_COUNT must be a positive integer";
            System.err.println(msg);
            aeShp.userError(msg);
            return;
        }

        String [] columnNames = new String[0];
        String strCloneNames =
            aeShp.getEnvironment().getValue(
                "CLONE_COLUMN_NAMES");
        if (strCloneNames != null)
        {
            columnNames = strCloneNames.split(",");
            for (int i = 0; i < columnNames.length; i++) {
                if (columnNames[i] == null
                        || columnNames[i].length() == 0) {
                    String msg = "column name "
                        + i + " is invalid";
                    System.err.println(msg);
                    aeShp.userError(msg);
                    return;
                }
            }
        }

        NzaeMetadata meta = aeShp.getMetadata();

        if (aeShp.catalogIsUpper()) {
            for (int i = 0; i < columnNames.length; i++) {
                columnNames[i] = columnNames[i].toUpperCase();
            }
        }

        int numInputColumns = meta.getInputColumnCount();
        for (int i = 0; i < numInputColumns; i++) {
            String name;
            if (i < columnNames.length) {
                name = columnNames[i];
            } else {
                name = "F" + (i + 1);
            }

            switch (meta.getInputNzType(i)) {
            case NzaeDataTypes.NZUDSUDX_FIXED:
            case NzaeDataTypes.NZUDSUDX_VARIABLE:
            case NzaeDataTypes.NZUDSUDX_NATIONAL_FIXED:
            case NzaeDataTypes.NZUDSUDX_NATIONAL_VARIABLE:
                aeShp.addOutputColumnString(
                    meta.getInputNzType(i),
                    name, meta.getInputSize(i));
                break;
            case NzaeDataTypes.NZUDSUDX_NUMERIC32:
            case NzaeDataTypes.NZUDSUDX_NUMERIC64:
            case NzaeDataTypes.NZUDSUDX_NUMERIC128:
                aeShp.addOutputColumnNumeric(
                    meta.getInputNzType(i),
                    name, meta.getInputSize(i),
                    meta.getInputScale(i));
                break;
            default:
                aeShp.addOutputColumn(
                    meta.getInputNzType(i), name);
                break;
            }
        }
        aeShp.update();
    }

    public static void runAe(Nzae ae) {
        try {
            runAeImpl(ae);
        } catch (Throwable t) {
            t.printStackTrace();
            try { ae.userError(t.getMessage()); }
            catch (Throwable t2) {}
        }
    }

    private static void runAeImpl(Nzae ae) {
        String strCloneCount =
            ae.getEnvironment().getValue("CLONE_COUNT");
        if (strCloneCount == null) {
            String msg =
                "CLONE_COUNT environment variable not set";
            System.err.println(msg);
            ae.userError(msg);
            return;
        }
        int cloneCount = 0;
        try {
            cloneCount = Integer.parseInt(strCloneCount);
        } catch (NumberFormatException e) {}
        if (cloneCount < 1) {
            String msg =
                "CLONE_COUNT must be a positive integer";
            System.err.println(msg);
            ae.userError(msg);
            return;
        }
        // input row loop
        for (;;) {
            NzaeRecord input = ae.next();
            if (input == null) break;
            for (int i = 0; i < cloneCount; i++) {
                ae.outputResult(input);
            }
        }

        // it's critical that done get called
        ae.done();
    }
}