33.7. 使用描述符范圍 · PostgreSQL 中文文檔 9.3

# 33.7\. 使用描述符范圍一個SQL描述符范圍是處理`SELECT`, `FETCH`或者 `DESCRIBE`語句結果的更復雜的方法。一個SQL描述符范圍把一行數據里的數據和元數據項組合到一個數據結構中。元數據在執行動態SQL語句時特別有用，那里的結果列的屬性可能不能提前知道。 PostgreSQL提供了兩種使用描述符范圍的方法：命名的SQL描述符范圍和C結構SQLDAs。 ## 33.7.1\. 命名SQL描述符范圍一個命名SQL描述符范圍由一個頭組成，包含有關整個描述符的信息，一個或多個項描述符范圍，基本上每個描述結果行中的一個字段。在你使用SQL描述符范圍之前，你需要分配一個： ``` EXEC SQL ALLOCATE DESCRIPTOR _identifier_; ``` 標示符用作描述符范圍的"變量名"。當你不再需要這個描述符，你應該釋放它： ``` EXEC SQL DEALLOCATE DESCRIPTOR _identifier_; ``` 要使用一個描述符范圍，在一個`INTO`子句的存儲目標里聲明它，而不是列出宿主變量： ``` EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc; ``` 如果結果集是空，描述符范圍將包含來自查詢的元數據，即字段名稱。為了尚未執行的預備查詢，`DESCRIBE`語句可用于獲得結果集的元數據： ``` EXEC SQL BEGIN DECLARE SECTION; char *sql_stmt = "SELECT * FROM table1"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :sql_stmt; EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; ``` PostgreSQL 9.0之前，`SQL`關鍵字是可選的，所以使用`DESCRIPTOR`和`SQL DESCRIPTOR` 產生命名SQL描述符范圍。現在，它是強制性的，省略`SQL`關鍵詞產生SQLDA描述符范圍，參閱[Section 33.7.2](#calibre_link-2007)。在`DESCRIBE`和`FETCH`語句中， `INTO`和`USING`關鍵字使用類似：它們產生結果集合和描述符范圍的元數據。現在，我們應該如何從描述符范圍里獲取數據？你可以把描述符范圍看作是一個有著命名字段的結構。要從頭檢索字段數值并且把它存儲到一個宿主變量里，使用下面的命令： ``` EXEC SQL GET DESCRIPTOR _name_ :_hostvar_ = _field_; ``` 目前只定義了一個頭字段：`_COUNT_`，這個字段告訴我們有幾個項描述符范圍存在（也就是說，在結果里包含多少個字段）。宿主變量需要是一個整數類型。要從項描述符范圍里獲取一個字段，使用下面的命令： ``` EXEC SQL GET DESCRIPTOR _name_ VALUE _num_ :_hostvar_ = _field_; ``` `_num_`可以是一個字符整數或者一個包含整數的宿主變量。可能的字段有: `CARDINALITY` (integer) 結果集中的行數 `DATA` 實際數據項（因此，這個字段的數據類型依賴于這個查詢） `DATETIME_INTERVAL_CODE` (integer) 當`TYPE`是`9`的時候，那么 `DATETIME_INTERVAL_CODE`將有`DATE`的`1`值， `TIME`的`2`值， `TIMESTAMP`的`3`值， `TIME WITH TIME ZONE`的`4`值或者 `TIMESTAMP WITH TIME ZONE`的`5`值。 `DATETIME_INTERVAL_PRECISION` (integer) 未實現。 `INDICATOR` (integer) 描述符（標識一個空值或者一個截斷的值） `KEY_MEMBER` (integer) 未實現 `LENGTH` (integer) 字符中數據長度 `NAME` (string) 字段名稱 `NULLABLE` (integer) 未實現 `OCTET_LENGTH` (integer) 字節數據的字符表示的長度 `PRECISION` (integer) 精度（類型`numeric`） `RETURNED_LENGTH` (integer) 字符中數據長度 `RETURNED_OCTET_LENGTH` (integer) 字節數據的字符表示的長度 `SCALE` (integer) 比例（類型`numeric`） `TYPE` (integer) 字段數據類型的數值代碼在`EXECUTE`, `DECLARE`和`OPEN` 語句中，`INTO`和`USING`關鍵字的作用是不同的。描述符范圍可以手動的編譯，為一個查詢或者游標提供輸入參數，并且`USING SQL DESCRIPTOR` `_name_`是傳遞輸入參數給一個參數化查詢的一種方式。編譯命名SQL描述符范圍的語句如下： ``` EXEC SQL SET DESCRIPTOR _name_ VALUE _num_ _field_ = :_hostvar_; ``` PostgreSQL支持檢索更多的在一個`FETCH`語句中的記錄和存儲在宿主變量中的數據，在這種情況下假設變量是一個數組。例如： ``` EXEC SQL BEGIN DECLARE SECTION; int id[5]; EXEC SQL END DECLARE SECTION; EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA; ``` ## 33.7.2\. SQLDA描述符范圍 SQLDA描述符范圍是一個C語言結構，它過去常常獲取結果集和查詢的元數據。一個結構存儲來自結果集中的一條記錄。 ``` EXEC SQL include sqlda.h; sqlda_t *mysqlda; EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda; ``` 注意省略`SQL`關鍵字。 [Section 33.7.1](#calibre_link-2008)中的`INTO`和`USING`關鍵字的使用情況的段落有個例外，也能適用于這里。在`DESCRIBE`語句中，如果使用了`INTO`關鍵字，則`DESCRIPTOR`關鍵字完全省略。 ``` EXEC SQL DESCRIBE prepared_statement INTO mysqlda; ``` 使用SQLDA程序流是： 1. 準備一個查詢，并且為它聲明一個游標。 2. 為結果行聲明SQLDA。 3. 為輸入參數聲明SQLDA，并且初始化它們(內存分配，參數設置)。 4. 打開具有輸入SQLDA的游標 5. 從游標中抓取行，并且將它們存儲到輸出SQLDA中。 6. 從輸出SQLDA中讀取值到宿主變量中 (如果有必要使用轉換)。 7. 關閉游標。 8. 自由內存區域分配給輸入SQLDA。 ### 33.7.2.1\. SQLDA數據結構 SQLDA使用三個數據結構類型：`sqlda_t`，`sqlvar_t`, 和`struct sqlname`。 > **Tip:** PostgreSQL的SQLDA與IBM DB2通用數據庫中的一個有類似的數據結構。所以DB2的SQLDA上的一些技術信息可以更好的幫助理解PostgreSQL的。 #### 33.7.2.1.1\. sqlda_t 結構結構類型`sqlda_t`是實際SQLDA的類型。它擁有一條記錄。并且在鏈表中使用`desc_next` 字段指針可以連接兩個或更多個`sqlda_t`結構，因此代表行的有序集合。因此，當抓取兩個或更多行時，應用程序通過每個`sqlda_t`節點隨后`desc_next`指針可以讀取它們。 `sqlda_t`的定義是： ``` struct sqlda_struct { char sqldaid[8]; long sqldabc; short sqln; short sqld; struct sqlda_struct *desc_next; struct sqlvar_struct sqlvar[1]; }; typedef struct sqlda_struct sqlda_t; ``` 該字段的意思是： `sqldaid` 它包含文本字符串`"SQLDA "`。 `sqldabc` 它包含字節中分配空間的大小。 `sqln` 它包含一個參數化查詢的情況下的輸入參數數，使用`USING`關鍵字被傳遞給`OPEN`, `DECLARE`或者 `EXECUTE`語句。在這種情況下它被作為`SELECT`, `EXECUTE`或者`FETCH`語句的輸出使用。它的值和`sqld`語句是一樣的。 `sqld` 它包含結果集中的字段數量。 `desc_next` 如果查詢返回多條記錄，那么返回多個鏈接SQLDA結構，并且`desc_next`持有指向列表中下一項的指針。 `sqlvar` 這是結構集中列數組。 #### 33.7.2.1.2\. sqlvar_t結構結構類型`sqlvar_t`持有列值和元數據比如類型和長度。該類型的定義是： ``` struct sqlvar_struct { short sqltype; short sqllen; char *sqldata; short *sqlind; struct sqlname sqlname; }; typedef struct sqlvar_struct sqlvar_t; ``` 該字段的含義是： `sqltype` 包含該字段的類型標識符。對于該值，參閱`ecpgtype.h`中的`enum ECPGttype`。 `sqllen` 包含該字段的二進制長度。比如4字節的`ECPGt_int`。 `sqldata` 指向該數據。關于數據的格式在[Section 33.4.4](#calibre_link-2009) 中描述。 `sqlind` 指向空指示器。0表示非空，-1表示空。 `sqlname` 該字段名稱。 #### 33.7.2.1.3\. struct sqlname結構 `struct sqlname`結構持有列名。它作為`sqlvar_t`結構成員被使用。該結構定義是： ``` #define NAMEDATALEN 64 struct sqlname { short length; char data[NAMEDATALEN]; }; ``` 該字段含義是： `length` 包含該字段名長度。 `data` 包含實際字段名。 ### 33.7.2.2\. 使用SQLDA檢索結果集通過SQLDA檢索查詢結果集的一般步驟是： 1. 聲明`sqlda_t`結構用來接收結果集。 2. 執行`FETCH`/`EXECUTE`/`DESCRIBE`命令用來處理指定已聲明SQLDA的查詢。 3. 通過查看`sqln`檢查結果集中的記錄數，`sqlda_t`結構成員。 4. 從`sqlvar[0]`, `sqlvar[1]`等中獲得每列的值，`sqlda_t`結構成員 5. 通過`desc_next`指針轉到下一行(`sqlda_t`結構)，`sqlda_t`結構成員。 6. 你需要重復以上步驟這是一個通過SQLDA檢索結果集的例子。首先，聲明一個`sqlda_t`結構以接收結果集。 ``` sqlda_t *sqlda1; ``` 接下來，在命令中聲明SQLDA。這是`FETCH`命令實例。 ``` EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; ``` 在鏈接表后運行循環以檢索行。 ``` sqlda_t *cur_sqlda; for (cur_sqlda = sqlda1; cur_sqlda != NULL; cur_sqlda = cur_sqlda->desc_next) { ... } ``` 在循環中，運行另外一個循環以檢索行中的每列數據（`sqlvar_t`結構）。 ``` for (i = 0; i < cur_sqlda->sqld; i++) { sqlvar_t v = cur_sqlda->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; ... } ``` 為了得到列值，檢查`sqltype`值，`sqlvar_t`結構成員。然后，切換適當方式，依賴于列類型，從宿主變量`sqlvar`字段拷貝數據。 ``` char var_buf[1024]; switch (v.sqltype) { case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen)); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; ... } ``` ### 33.7.2.3\. 使用SQLDA傳遞查詢參數使用SQLDA傳遞輸入參數給預備查詢的一般步驟是： 1. 創建預備查詢(預備語句) 2. 作為輸入SQLDA聲明sqlda_t結構。 3. 為了輸入SQLDA分配內存區域(作為sqlda_t結構)。 4. 在已分配內存中設置（拷貝）輸入值。 5. 打開具有聲明輸入SQLDA的游標。這有個例子。首先，創建一個預備語句。 ``` EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :query; ``` 下一步，為SQLDA分配內存，并且在`sqln`中設置輸入參數數， `sqlda_t`結構成員變量。當預備查詢需要兩個或更多個輸入參數的時候，應用程序必須分配額外內存空間，它是通過(nr. of params - 1) * sizeof(sqlvar_t)計算的。這里顯示的是為兩個輸入參數分配內存空間的例子。 ``` sqlda_t *sqlda2; sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* number of input variables */ ``` 內存分配后，存儲參數值到`sqlvar[]`數組。（當該SQLDA正在接收結果集時，這是用于檢索列值的相同數組。）在這個例子中，輸入參數是有字符串類型的`"postgres"`，以及有整數類型的`1`。 ``` sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; int intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *) &intval; sqlda2->sqlvar[1].sqllen = sizeof(intval); ``` 打開游標并且聲明事先準備的SQLDA，將輸入參數傳遞給預備語句。 ``` EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; ``` 最后，使用輸入SQLDA之后，必須顯式釋放已分配內存空間，不像用于接收查詢結果的SQLDA。 ``` free(sqlda2); ``` ### 33.7.2.4\. 使用SQLDA示例應用程序這是一個示例程序，描述了如何獲取數據庫訪問統計，通過輸入參數聲明，來自系統表。這個應用程序連接兩個系統表，數據庫OID上的pg_database和 pg_stat_database，并且讀取、顯示由兩個輸入參數（`postgres`和OID `1`）檢索的數據庫統計。首先，為輸入聲明SQLDA，以及為輸出聲明SQLDA。 ``` EXEC SQL include sqlda.h; sqlda_t *sqlda1; /*輸出描述符*/ sqlda_t *sqlda2; /*輸入描述符*/ ``` 下一步，連接數據庫，準備語句，并且為預備語句聲明游標。 ``` int main(void) { EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1 USER testuser; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; ``` 接下來，為輸入參數將一些值放在輸入SQLDA中。為輸入SQLDA分配內存，并且設置輸入參數數到`sqln`。存儲類型，值以及值長度到`sqltype`，`sqldata`中，并且將`sqllen`放在`sqlvar`結構中。 ``` /*為輸入參數創建SQLDA結構 */ sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /*輸入變量數*/ sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *)&intval; sqlda2->sqlvar[1].sqllen = sizeof(intval); ``` 在建立輸入SQLDA后，打開具有輸入SQLDA的一個游標。 ``` /*打開具有輸入參數的游標。*/ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; ``` 從已打開的游標中讀取行到輸出SQLDA。（一般來說，你必須在循環中反復調用`FETCH`，為了讀取結果集中的所有行。） ``` while (1) { sqlda_t *cur_sqlda; /*分配描述符給游標*/ EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; ``` 接下來，從SQLDA中檢索已讀取記錄，通過下面`sqlda_t`結構中的連接表。 ``` for (cur_sqlda = sqlda1 ; cur_sqlda != NULL ; cur_sqlda = cur_sqlda->desc_next) { ... ``` 讀取第一條記錄中的每一列。列數被存儲在`sqld`中，第一列的實際數據被存儲在`sqlvar[0]`，`sqlda_t`結構的兩個成員中。 ``` /* 輸出行中每一列*/ for (i = 0; i < sqlda1->sqld; i++) { sqlvar_t v = sqlda1->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; strncpy(name_buf, v.sqlname.data, v.sqlname.length); name_buf[v.sqlname.length] = '\0'; ``` 目前，該列數據被存儲在變量`v`中。拷貝每個數據到宿主變量，為了列類型查看`v.sqltype`。 ``` switch (v.sqltype) { int intval; double doubleval; unsigned long long int longlongval; case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen)); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; ... default: ... } printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); } ``` 在處理完所有記錄之后關閉游標，并且斷開數據庫連接。 ``` EXEC SQL CLOSE cur1; EXEC SQL COMMIT; EXEC SQL DISCONNECT ALL; ``` 在[Example 33-1](#calibre_link-2010)中顯示了整個程序。 **Example 33-1\. SQLDA程序示例** ``` #include <stdlib.h> #include <string.h> #include <stdlib.h> #include <stdio.h> #include <unistd.h> EXEC SQL include sqlda.h; sqlda_t *sqlda1; /*輸出描述符*/ sqlda_t *sqlda2; /*輸入描述符*/ EXEC SQL WHENEVER NOT FOUND DO BREAK; EXEC SQL WHENEVER SQLERROR STOP; int main(void) { EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; int intval; unsigned long long int longlongval; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO uptimedb AS con1 USER uptime; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; /*為輸入參數創建SQLDA結構*/ sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* a number of input variables */ sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *) &intval; sqlda2->sqlvar[1].sqllen = sizeof(intval); /*打開具有輸入參數的游標*/ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; while (1) { sqlda_t *cur_sqlda; /*分配描述符給游標*/ EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; for (cur_sqlda = sqlda1 ; cur_sqlda != NULL ; cur_sqlda = cur_sqlda->desc_next) { int i; char name_buf[1024]; char var_buf[1024]; /*輸出行中每一列*/ for (i=0 ; i<cur_sqlda->sqld ; i++) { sqlvar_t v = cur_sqlda->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; strncpy(name_buf, v.sqlname.data, v.sqlname.length); name_buf[v.sqlname.length] = '\0'; switch (v.sqltype) { case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) ); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; case ECPGt_long_long: /* bigint */ memcpy(&longlongval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%lld", longlongval); break; default: { int i; memset(var_buf, 0, sizeof(var_buf)); for (i = 0; i < sqllen; i++) { char tmpbuf[16]; snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]); strncat(var_buf, tmpbuf, sizeof(var_buf)); } } break; } printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); } printf("\n"); } } EXEC SQL CLOSE cur1; EXEC SQL COMMIT; EXEC SQL DISCONNECT ALL; return 0; } ``` 該例子輸出應該看起來像下面這樣（一些數字有所不同）。 ``` oid = 1 (type: 1) datname = template1 (type: 1) datdba = 10 (type: 1) encoding = 0 (type: 5) datistemplate = t (type: 1) datallowconn = t (type: 1) datconnlimit = -1 (type: 5) datlastsysoid = 11510 (type: 1) datfrozenxid = 379 (type: 1) dattablespace = 1663 (type: 1) datconfig = (type: 1) datacl = {=c/uptime,uptime=CTc/uptime} (type: 1) datid = 1 (type: 1) datname = template1 (type: 1) numbackends = 0 (type: 5) xact_commit = 113606 (type: 9) xact_rollback = 0 (type: 9) blks_read = 130 (type: 9) blks_hit = 7341714 (type: 9) tup_returned = 38262679 (type: 9) tup_fetched = 1836281 (type: 9) tup_inserted = 0 (type: 9) tup_updated = 0 (type: 9) tup_deleted = 0 (type: 9) oid = 11511 (type: 1) datname = postgres (type: 1) datdba = 10 (type: 1) encoding = 0 (type: 5) datistemplate = f (type: 1) datallowconn = t (type: 1) datconnlimit = -1 (type: 5) datlastsysoid = 11510 (type: 1) datfrozenxid = 379 (type: 1) dattablespace = 1663 (type: 1) datconfig = (type: 1) datacl = (type: 1) datid = 11511 (type: 1) datname = postgres (type: 1) numbackends = 0 (type: 5) xact_commit = 221069 (type: 9) xact_rollback = 18 (type: 9) blks_read = 1176 (type: 9) blks_hit = 13943750 (type: 9) tup_returned = 77410091 (type: 9) tup_fetched = 3253694 (type: 9) tup_inserted = 0 (type: 9) tup_updated = 0 (type: 9) tup_deleted = 0 (type: 9) ```