C Function Definition - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

The following example implements ordering functionality that allows Teradata Database to order two circle_t UDTs by their area. The function gets the radius and computes the area, returning the result as a FLOAT data type. If the circle is null or if the x attribute, y attribute, or radius attribute of the circle is null, the function returns a null result.

/* File: c_order.c */

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void circle_t_Ordering( UDT_HANDLE *circleUdt,
                        FLOAT      *result,
                        int        *indicator_circle,
                        int        *indicator_result,
                        char       sqlstate[6],
                        SQL_TEXT   extname[129],
                        SQL_TEXT   specific_name[129],
                        SQL_TEXT   error_message[257])
{
    INTEGER x, y, r;
    int nullIndicator;
    int length;

    /* If circle is null, return null. */
    if (*indicator_circle == -1) {
        *indicator_result = -1;
        return;
    }

    /* Verify that the x attribute is not null. */
    FNC_GetStructuredAttribute(*circleUdt, "x", &x, SIZEOF_INTEGER, &nullIndicator, &length);
    if (nullIndicator == -1) {
        *indicator_result = -1;
        return;
    }

    /* Verify that the y attribute is not null. */
    FNC_GetStructuredAttribute(*circleUdt, "y", &y, SIZEOF_INTEGER, &nullIndicator, &length);
    if (nullIndicator == -1) {
        *indicator_result = -1;
        return;
    }

    /* Get the radius attribute. */
    FNC_GetStructuredAttribute(*circleUdt, "radius", &r, SIZEOF_INTEGER, &nullIndicator, &length);
    if (nullIndicator == -1) {
        *indicator_result = -1;
        return;
    }

    *result = 3.14 * r * r;

}

The following example implements transform functionality that transforms a VARCHAR(80) from the client to a circle_t type on the server. The character string contains x, y, radius and color attributes separated by a colon ( : ) delimiter:

x:y:r:color

The absence of a value indicates a null attribute. For example, the format for a null radius is:

x:y::color

/* File: c_tosql.c */

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void circle_t_ToSql( VARCHAR_LATIN *p1,
                     UDT_HANDLE    *resultCircleUdt,
                     char          sqlstate[6])
{
    char buf[81];
    char *x_str, *y_str, *r_str, *color;
    char *p;
    INTEGER x, y, r;
    int nullIndicator;
    int len;

    /* Make a copy of the input string since we want to modify it. */
    strcpy(buf, (char *)p1);

    /* Retrieve and set the x coordinate field */
    x_str = buf;
    p = strchr(x_str, ':');
    *p = '\0';
    if (x_str[0] == '\0') {
        nullIndicator = -1;
    }
    else {
        nullIndicator = 0;
        x = atoi(x_str);
    }
    p++;
    FNC_SetStructuredAttribute(*resultCircleUdt, "x", &x, nullIndicator, SIZEOF_INTEGER);

    /* Retrieve and set the y coordinate field */
    y_str = p;
    p = strchr(y_str, ':');
    *p = '\0';
    if (y_str[0] == '\0') {
        nullIndicator = -1;
    }
    else {
        nullIndicator = 0;
        y = atoi(y_str);
    }
    p++;
    FNC_SetStructuredAttribute(*resultCircleUdt, "y", &y, nullIndicator, SIZEOF_INTEGER);

    /* Retrieve and set the radius field */
    r_str = p;
    p = strchr(r_str, ':');
    *p = '\0';
    if (r_str[0] == '\0') {
        nullIndicator = -1;
    }
    else {
        nullIndicator = 0;
        r = atoi(r_str);
    }
    p++;
    FNC_SetStructuredAttribute(*resultCircleUdt, "radius", &r, nullIndicator, SIZEOF_INTEGER);

    /* Retrieve and set the color field */
    color = p;
    if (color[0] == '\0') {
        nullIndicator = -1;
    }
    else {
        nullIndicator = 0;
    }
    len = strlen(color);
    FNC_SetStructuredAttribute(*resultCircleUdt, "color", (VARCHAR_LATIN *)color, nullIndicator, SIZEOF_VARCHAR_LATIN(len));
}

The following example implements transform functionality that allows Teradata Database to export a circle_t type as a VARCHAR(80):

/* File: c_fromsql.c */

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void circle_t_FromSql( UDT_HANDLE    *circleUdt,
                       VARCHAR_LATIN *result,
                       char          sqlstate[6])
{
    INTEGER x, y, r;
    char y_str[11], r_str[11];
    VARCHAR_LATIN color[81];
    int nullIndicator;
    int length;

    /* Get each of the attributes and write them to the result string. */
    FNC_GetStructuredAttribute(*circleUdt, "x", &x, SIZEOF_INTEGER,
&nullIndicator, &length);
    if (nullIndicator == 0)
        sprintf((char *)result, "%d:", x);
    else
        strcpy((char *)result, ":");

    FNC_GetStructuredAttribute(*circleUdt, "y", &y, SIZEOF_INTEGER,
&nullIndicator, &length);
    if (nullIndicator == 0) {
        sprintf(y_str, "%d", y);
        strcat((char *)result, y_str);
    }
    strcat((char *)result, ":");

    FNC_GetStructuredAttribute(*circleUdt, "radius", &r, SIZEOF_INTEGER,
&nullIndicator, &length);
    if (nullIndicator == 0) {
        sprintf(r_str, "%d", r);
        strcat((char *)result, r_str);
    }
    strcat((char *)result, ":");

    FNC_GetStructuredAttribute(*circleUdt, "color", color,
SIZEOF_VARCHAR_LATIN_WITH_NULL(80), &nullIndicator, &length);
    if (nullIndicator == 0)
        strcat((char *)result, (char *)color);
}