16.20 - Example: FNC_GetDatasetInfo - 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)

Example Setup

This example references the following table and data.

CREATE TABLE datasetTable(
	id INTEGER, 
	avroFile DATASET STORAGE FORMAT Avro);

avro01.data:

7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2261222C2274797065223A22696E74227D5D7D0002 |1|base16
.import vartext file avro01.data
USING (avroData VARCHAR(1000), id varchar(10), encoding VARCHAR(20))
INSERT INTO datasetTable (cast(:id AS INTEGER),cast(TO_BYTES(:avroData, :encoding) AS DATASET STORAGE FORMAT AVRO));

Example Using FNC_GetDatasetInfo

This example uses FNC_GetDatasetInfo to retrieve information about a DATASET data type instance.

CREATE FUNCTION GetDatasetInfo ( a1 TD_ANYTYPE)  
RETURNS VARCHAR(1000)
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!GetDatasetInfo!GetDatasetInfo.c!F!GetDatasetInfo';

GetDatasetInfo.c:

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

#define buffer_size 512

void GetDatasetInfo(DATASET_HANDLE *input, 
				VARCHAR_LATIN *result,
				int  *inputNullIndicator,
				int  *outputNullIndicator,
				char sqlstate[6],
				SQL_TEXT extname[129],
				SQL_TEXT specific_name[129],
				SQL_TEXT error_message[257])
{
	int maxLength, inlineLength, schemaLength, rawDataLength = 0;
	dataset_storage_et storageFormat = 0;
	boolean_t dataLob, schemaLob = 0;
	
	FNC_GetDatasetInfo(*input, &maxLength, &inlineLength, &schemaLength, &rawDataLength, &storageFormat, &dataLob, &schemaLob);
	
	sprintf(result,"Max: %d, Inline: %d, SchemaLen: %d, RawDataLen: %d, Storage: %s, DataLob: %s, SchemaLob: %s\0", 
			maxLength, inlineLength, schemaLength, rawDataLength,
		   (storageFormat == DATASET_Avro_EN ? "Avro" : "Unknown"), 
		   (dataLob == 1 ? "Yes" : "No"),
		   (schemaLob == 1 ? "Yes" : "No"));
															
	sprintf(sqlstate, "00000\0");
	*outputNullIndicator = 0;
}

The following is sample output:

SELECT GetDatasetInfo(avroFile) FROM datasetTable;

> Max: 2097088000, Inline: 10000, SchemaLen: 70, RawDataLen: 1, Storage: Avro, DataLob: No, SchemaLob: No