17.00 - Example: Using a UDF to Write a Message to an External Queue - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example creates a C scalar UDF named write_mq that writes a message to an external queue. The function calls an MQ access module, identical to the access module a TPump job or other utility might use when processing from a queue.

The SQL is a simple SELECT request that contains nothing in the select list but the scalar UDF and the arguments it expects. When this request is performed, it produces the message, 'Hello World,' which is placed on an MQ queue on the client:

    SELECT WriteMQ('queue.manager.1','QUEUE1','CHANNEL1/TCP/           153.64.119.177', 'Hello World');

The parameters passed are the queue manager (qmgr ), queue name (qnm ), client communication channel (channel), and the message itself (vcmsg), respectively.

The following is the DDL used to create the function.

    CREATE FUNCTION write_mq(
      qmgr    VARCHAR(256), 
      qnm     VARCHAR(256), 
      channel VARCHAR(256), 
      vcmsg   VARCHAR(32000))     
    RETURNS INTEGER
    LANGUAGE C 
    NO SQL 
    PARAMETER STYLE SQL 
    EXTERNAL NAME 'F:emruwmq:SI:cmqc:/usr/include/cmqc.h:SL:mqic
      :SL:mqmcs:SS:emruwmq:/home/rmh/projects/emruwmq/emruwmq.c';