例: FNC_GetDatasetResultLobとFNC_SetDatasetLob - Advanced SQL Engine - Teradata Database

セットアップ例

この例では、次のテーブルとデータを参照します。

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));

FNC_GetDatasetResultLobとFNC_SetDatasetLobの使用例

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

CreateDATASETLob.c:

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

#define buffer_size 64000

void CreateDATASETLob (DATASET_HANDLE *input, 
                       DATASET_HANDLE *result,
                       int  *inputNullIndicator,
                       int  *outputNullIndicator,
                       char sqlstate[6],
                       SQL_TEXT extname[129],
                       SQL_TEXT specific_name[129],
                       SQL_TEXT error_message[257])

{
	LOB_LOCATOR inLOB;
	LOB_RESULT_LOCATOR outLOB;
	BYTE buffer[buffer_size];
	LOB_CONTEXT_ID id;
	FNC_LobLength_t readlen, writelen, actualInputLength;
	int trunc_err = 0;
	FNC_GetDatasetInputLob(*input,&inLOB);
	FNC_GetDatasetResultLob(*result,&outLOB);
			
	readlen=0;
	writelen=0;
	actualInputLength = 0;

	FNC_LobOpen(inLOB, &id, 0, 0);
	while( FNC_LobRead(id, buffer, buffer_size, &readlen) == 0 && !trunc_err )
	{		
		trunc_err = FNC_LobAppend(outLOB, buffer, readlen, &writelen);
	}
	FNC_LobClose(id);
	
	FNC_SetDatasetLob(*result,outLOB);
	sprintf(sqlstate, "00000\0");
	*outputNullIndicator = 0;
}

次に、データのテキスト表現を示すサンプル出力を示します。

SELECT (CreateDATASETLob(avroFile) RETURNS DATASET STORAGE FORMAT Avro).toJSON()
FROM datasetTable;

>	{"a":1}