17.00 - 17.05 - 例: FNC_GetDatasetInputLob - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1147-170K-JPN
Language
日本語 (日本)

セットアップ例

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

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_GetDatasetInputLobの使用例

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

getLobData.c:

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#include <stdio.h>
void getLobData ( DATASET_HANDLE *dataset_instance,
                  void *result,
                  char sqlstate[6])
{
   int max_length = 0;
   int in_line_length = 0; 
   int schema_length = 0;
	  int raw_data_length=0;
	  dataset_storage_et dataset_storage = DATASET_INVALID_EN;
   boolean_t dataLob = 0; 
   boolean_t schemaLob = 0;


   /* Get the info of the DATASET instance. */
   FNC_GetDatasetInfo((*dataset_instance), &max_length, &in_line_length, &schema_length, &raw_data_length, &dataset_storage, &dataLob, &schemaLob);
	
   if (dataLob == 1)
	  {
	    LOB_LOCATOR inLOB;
	    LOB_CONTEXT_ID id;
	    FNC_LobLength_t readlen, actualInputLength;
	    int trunc_err = 0;
	    BYTE *bufPtr = result;
     int buffer_size = 0;

		   FNC_GetDatasetInputLob((int)*dataset_instance, &inLOB);
     FNC_LobOpen(inLOB, &id, 0, 0);
     buffer_size = FNC_GetLobLength(inLOB);

     while (FNC_LobRead(id, bufPtr, buffer_size, &readlen) == 0 && !trunc_err)
     {
        bufPtr += readlen;
        memcpy(result,bufPtr,readlen);
     }
     FNC_LobClose(id);
	  }
}
以下はサンプル出力です。
/*if data is not a LOB, there will be no output*/

SELECT (getLobData (avroFile) RETURNS VARBYTE(1000)) FROM datasetTable;
>