從 SQL 進行操作系統調用

在編譯並安裝了包含 UDF 的共享庫之後，讓我們執行一些非常基本的成功和錯誤用例的測試：

/home/stolze　$　echo　"test"　>　test_file　
/home/stolze　$　db2　"VALUES　readFile(　'/home/stolze/test_file'　)"　
1　
--------------------------------------------------------　
test　
　1　record(s)　selected.　
　　
/home/stolze　$　db2　"VALUES　readFile(　'/home/stolze/test_file123'　)"　
1　
--------------------------------------------------------　
SQL0443N　Routine　"READFILE"　(specific　name　"READFILETOCLOB")　has　
returned　an　error　SQLSTATE　with　diagnostic　text　"Could　not　open　
file　/home/stolze/test_file123".　SQLSTATE=38100　

此後，就可以在 SQL 語句中象使用其它函數一樣使用這個 UDF，例如在下列 INSERT 中：

/home/stolze　$　db2　"CREATE　TABLE　test_table　(　text　CLOB(2M)　)"　
DB20000I　The　SQL　statement　completed　successfully.　
/home/stolze　$　db2　"INSERT　INTO　test_table　VALUES　(　readFile(　'/home/stolze/test_file'　)　)"　
DB20000I　The　SQL　statement　completed　successfully.　
/home/stolze　$　db2　"SELECT　SUBSTR(text,　2,　3)　FROM　test_table"　
1　
---　
est　
　1　record(s)　selected.　

使用表函數

接下來將說明如何將具有良好定義結構的文件表示為關系表。在這種情況下，我將其作為 opaque 數據，實現了從文件內容到表的映射，而不是簡單地將整個文件復制到單個 LOB 中。

對於我們的示例，假定文件有下列結構：

該文件由一系列統一的結構化記錄組成。

每個記錄總共包含 60 字節。

前 10 個字節包含（以文本表示的）32 位帶符號的整數值。

後 50 個字節表示一個定長字符串。

從文件結構到關系結構的簡單映射就是把文件中的所有記錄作為一個具有兩列的表返回。第一列的類型是 INTEGER，第二列的類型是 VARCHAR(50)。文件中的每個記錄將成為表中的一行。注：其它文件可能采用不同的文件格式，每種格式都需要屬於自己的專門映射。您需要為支持每種映射而實現單獨的函數。

定義了映射之後，讓我們創建數據庫系統中的函數。 清單 5向您展示了創建的方法。

清單 5. 創建讀取文件並將它表示為表的函數

CREATE　FUNCTION　readFileTable(fileName　VARCHAR(256))　
　　RETURNS　TABLE　(　intCol　INTEGER,　string　VARCHAR(50)　)　
　　SPECIFIC　readFileToTable　
　　EXTERNAL　NAME　'os_calls!readFileToTable'　
　　LANGUAGE　C　
　　PARAMETER　STYLE　SQL　
　　DETERMINISTIC　
　　NOT　FENCED　
　　RETURNS　NULL　ON　NULL　INPUT　
　　NO　SQL　
　　NO　EXTERNAL　ACTION　
　　SCRATCHPAD　
　　NO　FINAL　CALL　
　　DISALLOW　PARALLEL;　
　　
GRANT　EXECUTE　ON　FUNCTION　readFileTable　TO　PUBLIC;　

清單 6展示了實現從文件格式到關系表映射的 C 代碼。

清單 6. 將文件內容表示為表的 C 代碼

#include　<stdio.h>　
#include　<sqludf.h>　
#include　<sqlstate.h>　
　
void　SQL_API_FN　readFileToTable(　
　　　　SQLUDF_VARCHAR　*fileName,　　/*　input　*/　
　　　　SQLUDF_INTEGER　*intCol,　　　/*　output　*/　
　　　　SQLUDF_VARCHAR　*charCol,　　　/*　output　*/　
　　　　/*　null　indicators　*/　
　　　　SQLUDF_NULLIND　*fileName_ind,　
　　　　SQLUDF_NULLIND　*intCol_ind,　
　　　　SQLUDF_NULLIND　*charCol_ind,　
　　　　SQLUDF_TRAIL_ARGS_ALL)　
{　
　　size_t　readCnt　=　0;　
　　char　intValue[10+1]　=　{　'\\0'　};　
　　char　strValue[50+1]　=　{　'\\0'　};　
　　　
　　struct　scratchMap　{　
　　　　FILE　*f;　
　　　　int　rowNumber;　
　　};　
　　　
　　/*　map　the　scratchpad　*/　
　　struct　scratchMap　*scratch　=　
　　　　(struct　scratchMap　*)SQLUDF_SCRAT->data;　
　　　
　　*intCol_ind　=　-1;　
　　*charCol_ind　=　-1;　
　　　
　　switch　(SQLUDF_CALLT)　{　
　　　case　SQLUDF_TF_OPEN:　
　　　　　/*　open　file　and　store　the　pointer　on　the　scratchpad　*/　
　　　　　scratch->f　=　fopen(fileName,　"r");　
　　　　　if　(!scratch->f)　{　
　　　　　　　strcpy(SQLUDF_MSGTX,　"Could　not　open　file　");　
　　　　　　　strncat(SQLUDF_MSGTX,　fileName,　SQLUDF_MSGTEXT_LEN　-　
　　　　　　　　　　　strlen(SQLUDF_MSGTX)-1);　
　　　　　　　strncpy(SQLUDF_STATE,　"38200",　SQLUDF_SQLSTATE_LEN);　
　　　　　　　return;　
　　　　　}　
　　　　　scratch->rowNumber　=　0;　
　　　　　　
　　　　　break;　
　　　　　　
　　　case　SQLUDF_TF_FETCH:　
　　　　　/*　count　the　row　*/　
　　　　　scratch->rowNumber++;　
　　　　　　
　　　　　/*　read　the　integer　*/　
　　　　　readCnt　=　fread(intValue,　1,　10,　scratch->f);　
　　　　　if　(readCnt　==　0)　{　
　　　　　　　/*　end　of　file　reached　*/　
　　　　　　　/*　end　of　file　reached　*/　
　　　　　　　strncpy(SQLUDF_STATE,　SQL_NODATA_EXCEPTION,　
　　　　　　　　　　　SQLUDF_SQLSTATE_LEN);　
　　　　　　　return;　
　　　　　}　
　　　　　else　if　(readCnt　!=　10)　{　
　　　　　　　sprintf(SQLUDF_MSGTX,　"Could　not　read　int　value　"　
　　　　　　　　　　　"in　line　%d",　scratch->rowNumber);　
　　　　　　　strncpy(SQLUDF_STATE,　"38201",　SQLUDF_SQLSTATE_LEN);　
　　　　　　　return;　
　　　　　}　
　　　　　intValue[10]　=　'\\0';　
　　　　　if　(sscanf(intValue,　"%d",　intCol)　!=　1)　{　
　　　　　　　sprintf(SQLUDF_MSGTX,　"Invalid　integer　value　%s　"　
　　　　　　　　　　　"　in　row　%d",　intValue,　scratch->rowNumber);　
　　　　　　　strncpy(SQLUDF_STATE,　"38202",　SQLUDF_SQLSTATE_LEN);　
　　　　　　　return;　
　　　　　}　
　　　　　*intCol_ind　=　0;　
　　　　　　
　　　　　/*　read　the　string　(allow　truncations　at　EOF)　*/　
　　　　　readCnt　=　fread(strValue,　1,　50,　scratch->f);　
　　　　　strValue[readCnt]　=　'\\0';　
　　　　　strcpy(charCol,　strValue);　
　　　　　*charCol_ind　=　0;　
　　　　　　
　　　　　break;　
　　　　　　
　　　case　SQLUDF_TF_CLOSE:　
　　　　　/*　close　the　file　*/　
　　　　　fclose(scratch->f);　
　　　　　scratch->f　=　NULL;　
　　　　　scratch->rowNumber　=　0;　
　　}　
}