例: FNC_GetDatasetSchemaLob - 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_GetDatasetSchemaLobの使用例

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

getSchemaLob.c:

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

#define BUFFER_SIZE 10000

void getSchemaLob(DATASET_HANDLE *input, 
                  INTEGER *schemaEncoding,
                  LOB_RESULT_LOCATOR *result,
                  int  *input1NullIndicator,
                  int  *input2NullIndicator,
                  int  *outputNullIndicator,
                  char sqlstate[6],
                  SQL_TEXT extname[129],
                  SQL_TEXT specific_name[129],
                  SQL_TEXT error_message[257])

{
   BYTE schemaBuf[BUFFER_SIZE] = {'\0'};
   int schemaBufLen = BUFFER_SIZE;
   LOB_LOCATOR schemaInputLob;
   LOB_CONTEXT_ID id;
   FNC_LobLength_t readlen, writelen, actualSchemaLength;
   int trunc_err = 0;
   BYTE nullTerminator = '\0';
	
   FNC_GetDatasetSchemaLob(*input,&schemaInputLob,*schemaEncoding);
					
   readlen=0;
   writelen=0;
   actualSchemaLength = 0;

   FNC_LobOpen(schemaInputLob, &id, 0, 0);

   while( FNC_LobRead(id, schemaBuf, schemaBufLen, &readlen) == 0 && !trunc_err )
   {		
      trunc_err = FNC_LobAppend(*result, schemaBuf, readlen, &writelen);
   }
   FNC_LobClose(id);
														
   sprintf(sqlstate, "00000\0");
   *outputNullIndicator = 0;
}

以下はサンプル出力です。

SELECT from_bytes((GetSchemaLob(avroFile,0) RETURNS BLOB),'ascii') 
FROM datasetTable; 

> {"type":"record","name":"rec_0","fields":[{"name":"a","type":"int"}]}