16.20 - UDM Parameter List - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

The first parameter of the C/C++ function for a UDM is always the handle of the UDT on which the UDM is invoked. The UDM uses the UDT handle to get the values of the UDT on which it is invoked.

The parameters that follow the UDT handle parameter correspond to the parameter list in the METHOD specification of the CREATE TYPE statement.

Use parameter style SQL to allow a method to pass nulls for arguments; otherwise, use parameter style TD_GENERAL.

When you use parameter style SQL, you must define indicator parameters for each parameter.

Syntax for Parameter Style TD_GENERAL

void  method_name  ( UDT_HANDLE  *udthandle,
                   type_1      *input_parameter_1,
                    ...,
                   type_n      *input_parameter_n,
                   result_type  *result,
                   char         sqlstate[6] )
{
     ...
}

Syntax for Parameter Style SQL

void  method_name ( UDT_HANDLE  *udthandle,
                   type_1      *input_parameter_1,
                    ...,
                   type_n      *input_parameter_n,
                   int         *indicator_parameter_1,
                    ...,
                   int         *indicator_parameter_n,
                   char         sqlstate[6],
                   SQL_TEXT     method_name[m],
                   SQL_TEXT     specific_method_name[l],
                   SQL_TEXT     error_message[p] )
{
     ...
}

where:

Parameter ... Specifies ... Input/Output
UDT_HANDLE * udthandle the handle of the UDT on which the UDM is invoked, also called the subject parameter. In
type_n * input_parameter_n the input parameters, where n is the number of parameters in the METHOD specification of the CREATE TYPE statement. If n = 0, no input parameters appear. The type is one of the C types in sqltypes_td.h that corresponds to the SQL data type of the input argument.

The maximum number of input parameters is 128.

In
result_type * result the result.

The result pointer points to a data area that is big enough to hold the result that the method returns, as defined by the RETURNS clause in the corresponding METHOD specification.

Out
int * indicator_parameter_n the indicator parameters (for methods where n > 0) corresponding to the input parameters, in the same order.
If the value of the indicator argument is...
  • -1, then the corresponding input argument is null.
  • 0, then the corresponding input argument is a value.
In
int * indicator_result the result indicator parameter corresponding to the result. Out
char sqlstate[6] the success, exception, or warning return. This is a pointer to a six-character C string, where the first five characters are ASCII and the last character is a C null character. The function can set sqlstate to an SQLSTATE exception or warning condition if the function detects an error.

The string is initialized to '00000', which corresponds to a success condition.

For more information on SQLSTATE values, see Returning SQLSTATE Values.

Out
SQL_TEXT method_name[m] the method name. This is a pointer to a C string. This is the same name as the method name specified by METHOD method_name in the CREATE TYPE statement.

The UDT can use this name to build error messages.

The ANSI SQL standard defines the maximum value for m as 128. Teradata Database allows a maximum of 30 characters for method names.

In
SQL_TEXT specific_method_name[l] the specific name of the external method being invoked, when more than one method has the same name. This is a pointer to a C string. This is the same name as the specific name specified by the SPECIFIC clause of the METHOD specification in the CREATE TYPE statement.

If the CREATE TYPE statement omits the SPECIFIC clause, this name is the same as the method name specified by METHOD method_name.

The UDT can use this name to build error messages.

The ANSI SQL standard defines the maximum value for l as 128. Teradata Database allows a maximum of 30 characters for method names.

In
SQL_TEXT error_message[p] the error message text. This is a pointer to a C string where the maximum value for p is 256. Out

Example: UDM with Parameter Style TD_GENERAL

Here is a code excerpt that shows how to declare a C function for a UDM that uses parameter style TD_GENERAL:

/*****  C source file name: to_inches.c  *****/
   
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
   
void meters_toInches( UDT_HANDLE  *meterUDT
                      FLOAT       *result,
                      char         sqlstate[6])
{
     ...
}

For a complete example of the C function, see UDM Code Examples.

The CREATE TYPE statement to create the UDT with which the method is associated looks like this:

CREATE TYPE meter AS FLOAT
FINAL
   INSTANCE METHOD toInches()
   RETURNS FLOAT
   SPECIFIC toInches
   NO SQL
   PARAMETER STYLE TD_GENERAL
   DETERMINISTIC
   LANGUAGE C;

The corresponding CREATE METHOD statement to install the UDM on the server looks like this:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME 'CS!toinches!udm_src/to_inches.c!F!meters_toInches';

Example: UDM with Parameter Style SQL

Here is an example of how to declare a C function for a UDM that uses parameter style SQL:

/*****  C source file name: to_inches.c  *****/
  
#define  SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
   
void meters_toInches( UDT_HANDLE  *meterUDT
                      FLOAT       *result,
                      int         *meterUDTIsNull,
                      int         *resultIsNull,
                      char         sqlstate[6])
                      SQL_TEXT     extname[129],
                      SQL_TEXT     specific_name[129],
                      SQL_TEXT     error_message[257] )
{
     ...
}

For a complete example of the C function, see UDM Code Examples.

The CREATE TYPE statement to create the UDT with which the method is associated looks like this:

CREATE TYPE meter AS FLOAT
FINAL
   INSTANCE METHOD toInches()
   RETURNS FLOAT
   SPECIFIC toInches
   NO SQL
   PARAMETER STYLE SQL
   DETERMINISTIC
   LANGUAGE C;

The corresponding CREATE METHOD statement to install the UDM on the server looks like this:

CREATE METHOD toInches()
RETURNS FLOAT
FOR meter
EXTERNAL NAME 'CS!toinches!udm_src/to_inches.c!F!meters_toInches';

Argument Behavior

FOR information on … SEE …
behavior when arguments are null Behavior When Using NULL as a Literal Argument .
truncation of character string arguments Truncation of Character String Arguments .
overflow and numeric arguments Overflow and Numeric Arguments .