從 SQL 呼叫作業系統功能：以 C 語言 UDF 實作檔案讀寫與程式呼叫

前言

有時 SQL 需直接存取資料庫伺服器上的檔案系統或執行外部程式。DB2 使用者自訂函數（UDF）支援 C、Java、SQL 等語言實作，本文以 C 語言為例，示範如何建立讀寫檔案、呼叫外部執行檔的 UDF，並詳述編譯部署流程。

建置 UDF 共享庫

1. 撰寫 C 原始碼 os_calls.c 實作目標函數。
2. 依平台建立匯出定義檔：
   • Windows：library.def 列出 LIBRARY 與 EXPORTS。
   • Linux/UNIX：library.exp 逐行列出函數名稱。
3. 使用實例目錄下 sqllib/samples/c/bldrtn 腳本編譯鏈接：

   bldrtn os_calls
   

   腳本會自動將共享庫複製至 sqllib/function 目錄。

案例一：讀取整個檔案為 CLOB

SQL 註冊

CREATE FUNCTION readFile(fileName VARCHAR(255))
RETURNS CLOB(1M)
SPECIFIC readFileToClob
EXTERNAL NAME 'os_calls!readFileToClob'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
NO SCRATCHPAD
ALLOW PARALLEL
NO FINAL CALL;

C 實作要點

• 以 fopen、fseek/ftell 取得檔案大小，驗證不超過 CLOB 最大長度。
• 一次 fread 讀入全部內容至 SQLUDF_CLOB 結構的 data 欄位，設定 length 與 null indicator。
• 錯誤時透過 SQLUDF_STATE、SQLUDF_MSGTX 回傳 SQLSTATE 與訊息。

測試

VALUES readFile('/home/user/test_file');  -- 回傳檔案內容
VALUES readFile('/home/user/not_exist');  -- SQLSTATE 38100: Could not open file

案例二：結構化檔案對應關聯表（Table Function）

檔案格式

固定 60 位元組/記錄：前 10 位元組整數（文字形式），後 50 位元組定長字串。

SQL 註冊

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;

C 實作關鍵

• 使用 scratchpad 保存 FILE* 指標與行號，跨 OPEN/FETCH/CLOSE 呼叫週期。
• SQLUDF_TF_OPEN：開啟檔案、初始化計數器。
• SQLUDF_TF_FETCH：逐筆讀取 10+50 位元組，解析整數與字串，EOF 時回傳 SQL_NODATA_EXCEPTION。
• SQLUDF_TF_CLOSE：關閉檔案釋放資源。

呼叫語法

SELECT * FROM TABLE(readFileTable('/home/user/datafile')) AS t;

案例三：寫入結構化檔案

SQL 註冊

CREATE FUNCTION writeFile(fileName VARCHAR(255), intValue INTEGER, strValue VARCHAR(50))
RETURNS INTEGER
SPECIFIC writeFile
EXTERNAL NAME 'os_calls!writeFileFromParams'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
DISALLOW PARALLEL
FINAL CALL;

C 實作關鍵

• FIRST_CALL：以 wb 模式開啟檔案，指標存入 scratchpad。
• NORMAL_CALL：驗證整數不超過 10 位元組、字串不超過 50 字元，以 fprintf 格式化寫入 60 位元組。
• FINAL_CALL / 錯誤路徑：關閉檔案。

批次寫入範例

SELECT writeFile('/home/user/outfile', id, name) FROM source_table;
-- 再以 readFileTable 驗證寫入內容

案例四：呼叫外部執行檔

同樣建立 systemCall UDF，傳入命令字串，C 端以 system() 或 popen() 執行並回傳狀態碼或輸出，適用於觸發備份、發送郵件等場景。

結語

透過 C 語言 UDF，SQL 能輕鬆跨越資料庫邊界存取檔案系統與作業系統功能。合理運用標量函數、表函數與 scratchpad 機制，即可在 SQL 層完成複雜的外部互動，簡化應用架構。