# 【教學筆記 05】以 C 語言開發資料庫存取程式 (PostgreSQL ECPG)
內容：以 C 語言開發資料庫存取程式 (ECPG)
---
[目錄](https://hackmd.io/Y7i9O4hCQu6xOJ94__PhNg)
[教學筆記 01：編碼、檔案、斷行字元、Excel、其他基礎知識介紹](https://hackmd.io/7-FajauqT62vXpxOViWzBw)
[教學筆記 02：Linux 指令介紹 ](https://hackmd.io/9D_WXaT3TsCaBrsOQKYdTw)
[教學筆記 03：awk 指令介紹](https://hackmd.io/PHZRjtQMRi2v9Z-u6bD6Tg)
[教學筆記 04：文件圖表表達方式的經驗分享](https://hackmd.io/xVM9lnFBSneA6uhIzdkYyA)
教學筆記 05：以 C 語言開發資料庫存取程式 (ECPG)
[推薦書籍](https://hackmd.io/t7T5FxfmT3Kih-b7PRUZrA)
[補充教材](https://hackmd.io/w-BNpl_TSuysG4_qaGtmFg)
---
### 一、Embedded SQL：
* https://zh.wikipedia.org/wiki/%E5%B5%8C%E5%85%A5%E5%BC%8FSQL
* https://en.wikipedia.org/wiki/Embedded_SQL
* 官網說明：https://www.postgresql.org/docs/9.6/ecpg.html
* 下載：
* 本教學所使用的全部範例：[pgc_example.7z](https://drive.google.com/file/d/1SIt-8ws_6OxHXnoh8R67ObkdSVKKZ3qn/view?usp=sharing)
* 很久以前寫的 esql 教學筆記：FVAS批次程式開發ABC(全)-1060905.pptx *<連結已移除>*
* 編譯方式：
```graphviz
digraph nodestyling {
node [color=lightblue2 style=filled]
rankdir=LR
"job.pgc" -> "job.c" [label = "ecpg"]
"job.c" -> "job" [label = "gcc -o"]
"job.c" -> "job.o" [label = "gcc -c"]
"job.o" -> "job" [label = "gcc -o"]
}
```
1. 編譯指令1：
```
ecpg job.pgc
gcc -I[include_path] -L[library_path] -lecpg -o job job.c
```
2. 編譯指令2：
```
ecpg job.pgc
gcc -I[include_path] -c job.c
gcc -L[library_path] -lecpg -o job job.o
```
---
### ※ 20231229 補充：在 Windows 作業系統中建立開發 PostgreSQL ECPG 程式的環境：
1. 安裝 Windows 版本的 PostgreSQL，就會包含所需的 library
例如：C:\Program Files\PostgreSQL\15\lib 中的 libecpg.dll、libecpg.lib
2. 安裝 MinGW gcc 編譯器，盡量抓較新的版本 (不要抓太舊的版本，否則可能會編譯失敗)
下載網址：https://github.com/niXman/mingw-builds-binaries/releases
本範例下載：x86_64-13.2.0-release-win32-seh-msvcrt-rt_v11-rev0.7z
(抓回來解壓縮後不須安裝即可使用，建議將 bin 目錄設定在 PATH 環境變數中)
3. 撰寫 ECPG 程式，副檔名須為 .pgc
4. 編譯指令1：
```shell script
ecpg job.pgc
gcc -I"C:\\Program Files\\PostgreSQL\\15\\include" -L"C:\\Program Files\\PostgreSQL\\15\\lib" -o job job.c -lecpg
```
5. 編譯指令2：
```shell script
ecpg job.pgc
gcc -I"C:\\Program Files\\PostgreSQL\\15\\include" -c job.c
gcc -L"C:\\Program Files\\PostgreSQL\\15\\lib" -o job job.o "C:\\Program Files\\PostgreSQL\\15\\lib\\libecpg.lib"
或
gcc -L"C:\\Program Files\\PostgreSQL\\15\\lib" -o job job.o "C:\\Program Files\\PostgreSQL\\15\\lib\\libecpg.dll"
```
---
### 二、資料集：
* 資料集使用北風資料庫 (Northwind database for Postgres)，並來自於下列網址：https://github.com/pthom/northwind_psql
* 建立資料集使用之SQL：[northwind.sql](https://drive.google.com/file/d/1E5P0JDRJ82RSxQrCqchkTPQdcb6joTBO/view?usp=sharing)
* ER model：
https://i.imgur.com/vSdQPgf.png
---
### 三、開發環境：
* 開發環境準備：
* OS：Linux 作業系統
* DB：PostgreSQL 資料庫
* 程式語言：PostgreSQL Embedded SQL + C 語言
* 編譯器：ecpg preprocessor + gcc compiler
* 資料集：北風資料庫 (northwind)
* 對於財稅案開發人員而言，可進入乙測環境 Greenplum 主機進行開發。 (北風資料庫已建立)
* 對於非本案開發人員，建議自行於 Linux 主機安裝 PostgreSQL 資料庫，並建立北風資料庫的資料集之後，再依據本教學範例來開發。
---
### 範例1：由表格輸出資料
* 參考：投影片講義 P.8
* 題目：請輸出表格 categories 中，category_id、category_name 兩個欄位的內容。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example01.ec
```C=
#include <stdio.h>
#include <stdlib.h>
void main()
{
exec sql begin declare section;
int category_id; /* host variable */
char category_name[16]; /* host variable */
exec sql end declare section;
exec sql connect to 'postgres'; /* 連接資料庫 postgres */
exec sql declare catCur cursor for
select category_id, category_name from northwind.categories;
exec sql open catCur;
for(;;) {
exec sql fetch catCur into :category_id, :category_name;
if(sqlca.sqlcode == 100) break; /* 檢查是否已經無資料 */
printf("category_id=%d, category_name=%s\n", category_id, category_name);
}
exec sql close catCur;
}
```
* 編譯指令：
```
ecpg example01.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example01 example01.c
或
ecpg example01.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -c example01.c
gcc -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example01 example01.o
```
### 範例2：由表格輸出資料 (儲存至struct中再輸出)
* 參考：投影片講義 P.9 ~ 10
* 題目：與範例1相同，但先將資料儲存至struct中再輸出。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example02.ec
```C=
#include <stdio.h>
#include <stdlib.h>
exec sql begin declare section;
struct _categories {
int category_id; /* host variable */
char category_name[16]; /* host variable */
};
typedef struct _categories CATEGORIES;
CATEGORIES *CATEGORIESs = NULL; /* 起始位置 */
static int CATEGORIES_TOT = 0; /* 資料筆數 */
exec sql end declare section;
void main()
{
exec sql begin declare section;
CATEGORIES *cPt = NULL; /* 走訪指標 */
exec sql end declare section;
int i;
exec sql connect to 'postgres'; /* 連接資料庫 postgres */
exec sql select count(*) into :CATEGORIES_TOT from northwind.categories; /* 計算資料筆數 */
CATEGORIESs = calloc(CATEGORIES_TOT, sizeof(CATEGORIES)); /* 配置適當連續記憶體，且初始值為0 */
if(CATEGORIES_TOT != 0 && CATEGORIESs == NULL) {printf("calloc error!\n"); exit(1);}
/* 資料載入 */
exec sql declare catCur cursor for
select category_id, category_name from northwind.categories;
exec sql open catCur;
for(cPt = CATEGORIESs;;cPt++) {
exec sql fetch catCur into :cPt->category_id, :cPt->category_name;
if(sqlca.sqlcode == 100) break; /* 檢查是否已經無資料 */
}
exec sql close catCur;
/* 資料走訪 */
for(i = 0, cPt = CATEGORIESs; i < CATEGORIES_TOT; i++, cPt++) {
printf("category_id=%d, category_name=%s\n", cPt->category_id, cPt->category_name);
}
}
```
* 編譯指令：
```
ecpg example02.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example02 example02.c
```
### 範例3：快速排序
* 參考：投影片講義 P.11
* 題目：請輸出表格 categories 中，category_id、category_name 兩個欄位的內容，並依據 category_id 排序 (請勿在 SQL 中加入 order by category_id)。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example03.ec
```C=
#include <stdio.h>
#include <stdlib.h>
int comp_func(const void *, const void *); /* 函式原型(function prototype) */
exec sql begin declare section;
struct _categories {
int category_id; /* host variable */
char category_name[16]; /* host variable */
};
typedef struct _categories CATEGORIES;
CATEGORIES *CATEGORIESs = NULL; /* 起始位置 */
static int CATEGORIES_TOT = 0; /* 資料筆數 */
exec sql end declare section;
void main()
{
exec sql begin declare section;
CATEGORIES *cPt = NULL; /* 走訪指標 */
exec sql end declare section;
int i;
exec sql connect to 'postgres'; /* 連接資料庫 postgres */
exec sql select count(*) into :CATEGORIES_TOT from northwind.categories; /* 計算資料筆數 */
CATEGORIESs = calloc(CATEGORIES_TOT, sizeof(CATEGORIES)); /* 配置適當連續記憶體，且初始值為0 */
if(CATEGORIES_TOT != 0 && CATEGORIESs == NULL) {printf("calloc error!\n"); exit(1);}
/* 資料載入 */
exec sql declare catCur cursor for
select category_id, category_name from northwind.categories;
exec sql open catCur;
for(cPt = CATEGORIESs;;cPt++) {
exec sql fetch catCur into :cPt->category_id, :cPt->category_name;
if(sqlca.sqlcode == 100) break; /* 檢查是否已經無資料 */
}
exec sql close catCur;
qsort(CATEGORIESs, CATEGORIES_TOT, sizeof(CATEGORIES), comp_func); /* 資料排序 */
/* 資料走訪 */
for(i = 0, cPt = CATEGORIESs; i < CATEGORIES_TOT; i++, cPt++) {
printf("category_id=%d, category_name=%s\n", cPt->category_id, cPt->category_name);
}
}
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
```
* 編譯指令：
```
ecpg example03.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example03 example03.c
```
### 範例4：二元搜尋
* 參考：投影片講義 P.12
* 題目：請寫出下列查詢功能：
* 輸入：畫面中可輸入任意 category_id。
* 輸出：畫面中輸出所對應的 category_id、category_name。
* 條件：請使用二元搜尋(bsearch)。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example04.ec
```C=
#include <stdio.h>
#include <stdlib.h>
int comp_func(const void *, const void *); /* 函式原型(function prototype) */
exec sql begin declare section;
struct _categories {
int category_id; /* host variable */
char category_name[16]; /* host variable */
};
typedef struct _categories CATEGORIES;
CATEGORIES *CATEGORIESs = NULL; /* 起始位置 */
static int CATEGORIES_TOT = 0; /* 資料筆數 */
exec sql end declare section;
void main()
{
exec sql begin declare section;
CATEGORIES *cPt = NULL; /* 走訪指標 */
exec sql end declare section;
int category_id;
exec sql connect to 'postgres'; /* 連接資料庫 postgres */
exec sql select count(*) into :CATEGORIES_TOT from northwind.categories; /* 計算資料筆數 */
CATEGORIESs = calloc(CATEGORIES_TOT, sizeof(CATEGORIES)); /* 配置適當連續記憶體，且初始值為0 */
if(CATEGORIES_TOT != 0 && CATEGORIESs == NULL) {printf("calloc error!\n"); exit(1);}
/* 資料載入 */
exec sql declare catCur cursor for
select category_id, category_name from northwind.categories;
exec sql open catCur;
for(cPt = CATEGORIESs;;cPt++) {
exec sql fetch catCur into :cPt->category_id, :cPt->category_name;
if(sqlca.sqlcode == 100) break; /* 檢查是否已經無資料 */
}
exec sql close catCur;
qsort(CATEGORIESs, CATEGORIES_TOT, sizeof(CATEGORIES), comp_func); /* 資料排序 */
/* 資料搜尋 */
printf("請輸入欲查詢的 category_id：\n");
scanf("%d", &category_id);
cPt = bsearch(&category_id, CATEGORIESs, CATEGORIES_TOT, sizeof(CATEGORIES), comp_func); /* 資料搜尋 */
if(cPt == NULL) printf("category_id 不存在！\n");
else printf("category_id=%d, category_name=%s\n", cPt->category_id, cPt->category_name);
}
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
```
* 編譯指令：
```
ecpg example04.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example04 example04.c
```
### 範例5：資料串接
* 參考：投影片講義 P.16 ~ 18
* 題目：請輸出表格 products 中，其所有 product_id、product_name，以及所對應的 category_id、category_name (請勿在 SQL 中將 products 與 categories 兩者 join)。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example05.ec
```C=
#include <stdio.h>
#include <stdlib.h>
void categories_load();
void products_load();
int comp_func(const void *, const void *);
exec sql begin declare section;
struct _categories {
int category_id;
char category_name[16];
};
typedef struct _categories CATEGORIES;
CATEGORIES *CATEGORIESs = NULL;
static int CATEGORIES_TOT = 0;
struct _products {
int product_id, category_id;
char product_name[41];
CATEGORIES *cPt; /* 指向 CATEGORIES */
};
typedef struct _products PRODUCTS;
PRODUCTS *PRODUCTSs = NULL;
static int PRODUCTS_TOT = 0;
exec sql end declare section;
/*----------------------------------------------------------------------------*/
void main()
{
PRODUCTS *pPt = NULL;
int i;
exec sql connect to 'postgres';
categories_load(); /* 載入 categories */
products_load(); /* 載入 products */
for(i = 0, pPt = PRODUCTSs; i < PRODUCTS_TOT; i++, pPt++) {
printf("product_id=%d, product_name=%s, category_id=%d, category_name=%s\n", pPt->product_id, pPt->product_name, pPt->cPt->category_id, pPt->cPt->category_name);
}
}
/*----------------------------------------------------------------------------*/
void categories_load()
{
exec sql begin declare section;
CATEGORIES *cPt = NULL;
exec sql end declare section;
exec sql select count(*) into :CATEGORIES_TOT from northwind.categories;
CATEGORIESs = calloc(CATEGORIES_TOT, sizeof(CATEGORIES));
if(CATEGORIES_TOT != 0 && CATEGORIESs == NULL) {printf("categories_load() calloc error!\n"); exit(1);}
/* 資料載入 */
exec sql declare catCur cursor for
select category_id, category_name from northwind.categories;
exec sql open catCur;
for(cPt = CATEGORIESs;;cPt++) {
exec sql fetch catCur into :cPt->category_id, :cPt->category_name;
if(sqlca.sqlcode == 100) break; /* 檢查是否已經無資料 */
}
exec sql close catCur;
qsort(CATEGORIESs, CATEGORIES_TOT, sizeof(CATEGORIES), comp_func); /* 資料排序 */
}
/*----------------------------------------------------------------------------*/
void products_load()
{
exec sql begin declare section;
PRODUCTS *pPt = NULL;
exec sql end declare section;
exec sql select count(*) into :PRODUCTS_TOT from northwind.products;
PRODUCTSs = calloc(PRODUCTS_TOT, sizeof(PRODUCTS));
if(PRODUCTS_TOT != 0 && PRODUCTSs == NULL) {printf("products_load() calloc error!\n"); exit(1);}
exec sql declare prodCur cursor for
select product_id, product_name, category_id from northwind.products;
exec sql open prodCur;
for(pPt = PRODUCTSs;;pPt++) {
exec sql fetch prodCur into :pPt->product_id, :pPt->product_name, :pPt->category_id;
if(sqlca.sqlcode == 100) break;
pPt->cPt = bsearch(&pPt->category_id, CATEGORIESs, CATEGORIES_TOT, sizeof(CATEGORIES), comp_func); /* 資料搜尋 */
if(pPt->cPt == NULL) {printf("product_id=%d, category_id=%d no data!\n", pPt->product_id, pPt->category_id); exit(1);}
}
exec sql close prodCur;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example05.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example05 example05.c
```
### 範例6：qsort 與 bsearch 的限制
* 參考：投影片講義 P.19 ~ 24
* 題目：請輸出表格 orders 中，取其 order_date >= 1998/05/01 的訂單資料，在表格 order_details 中的總訂單金額 (請勿在 SQL 中將 orders 與 order_details 兩者 join)。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example06.ec
```C=
#include <stdio.h>
#include <stdlib.h>
void order_details_load();
void orders_load(char *);
int comp_func(const void *, const void *);
exec sql begin declare section;
struct _order_details {
int order_id;
double unit_price, quantity;
};
typedef struct _order_details ORDER_DETAILS;
ORDER_DETAILS *ORDER_DETAILSs = NULL;
static int ORDER_DETAILS_TOT = 0;
struct _orders {
int order_id;
ORDER_DETAILS *odPt;
};
typedef struct _orders ORDERS;
ORDERS *ORDERSs = NULL;
static int ORDERS_TOT = 0;
char sqlStm[1000]; /* 用來產生自訂 SQL */
exec sql end declare section;
/*----------------------------------------------------------------------------*/
void main()
{
ORDER_DETAILS *odPt = NULL;
ORDERS *oPt = NULL;
double sum = 0;
int i;
exec sql connect to 'postgres';
order_details_load();
orders_load("19980501"); /* 限制 19980501 之後的訂單 */
for(i = 0, oPt = ORDERSs; i < ORDERS_TOT; i++, oPt++) {
odPt = bsearch(&oPt->order_id, ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料搜尋 (但不一定會搜尋到相同 order_id 的第一筆) */
if(odPt != NULL) {
while(oPt->order_id == (odPt-1)->order_id)
odPt--;
while(oPt->order_id == odPt->order_id) {
sum += odPt->unit_price * odPt->quantity;
odPt++;
}
printf("order_id=%d, 金額總計=%f\n", oPt->order_id, sum);
sum = 0;
}
}
}
/*----------------------------------------------------------------------------*/
void order_details_load()
{
exec sql begin declare section;
ORDER_DETAILS *odPt = NULL;
exec sql end declare section;
sprintf(sqlStm, "select count(*) from northwind.order_details;");
exec sql prepare odCnt from :sqlStm;
exec sql execute odCnt into :ORDER_DETAILS_TOT;
printf("SQL=%s, ORDER_DETAILS_TOT=%d\n", sqlStm, ORDER_DETAILS_TOT);
ORDER_DETAILSs = calloc(ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS));
if(ORDER_DETAILS_TOT != 0 && ORDER_DETAILSs == NULL) {printf("calloc error!\n"); exit(1);}
sprintf(sqlStm, "select order_id, unit_price, quantity from northwind.order_details;");
printf("SQL=%s\n", sqlStm);
exec sql prepare odQry from :sqlStm;
exec sql declare odCur cursor for odQry;
exec sql open odCur;
for(odPt = ORDER_DETAILSs;;odPt++) {
exec sql fetch odCur into :odPt->order_id, :odPt->unit_price, :odPt->quantity;
if(sqlca.sqlcode == 100) break;
}
exec sql close odCur;
qsort(ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料排序 */
}
/*----------------------------------------------------------------------------*/
void orders_load(order_date)
exec sql begin declare section;
char *order_date;
exec sql end declare section;
{
exec sql begin declare section;
ORDERS *oPt = NULL;
exec sql end declare section;
exec sql select count(*) into :ORDERS_TOT from northwind.orders where order_date >= :order_date;
ORDERSs = calloc(ORDERS_TOT, sizeof(ORDERS));
if(ORDERS_TOT != 0 && ORDERSs == NULL) {printf("calloc error!\n"); exit(1);}
exec sql declare oCur cursor for
select order_id from northwind.orders where order_date >= :order_date;
exec sql open oCur;
for(oPt = ORDERSs;;oPt++) {
exec sql fetch oCur into :oPt->order_id;
if(sqlca.sqlcode == 100) break;
}
exec sql close oCur;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example06.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example06 example06.c
```
* 範例6的缺點：
1. 須自行比對資料，將走訪指標移動到適當位置
2. 移動次數須自行計算
* 以上產生另一個想法：改寫 bsearch
* bsearchFirst()：回傳相同key值的第一筆位置 (投影片講義 P.23)
```C=
/*---------------------------------------------------------------------------*/
/* bsearchFirst()： */
/* 與 bsearch() 用法相同，但若尋找到多筆相同 key 值之紀錄時， */
/* 會傳回第一筆具有此 key 值之紀錄，若找不到則傳回 NULL */
/*---------------------------------------------------------------------------*/
void *bsearchFirst(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
return ((void *)p);
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return (NULL);
}
```
* bsearchRows()：回傳相同key值的筆數 (投影片講義 P.24)
```C=
/*---------------------------------------------------------------------------*/
/* bsearchRows()： */
/* 以 bsearch() 方法尋找具有相同 key 值之紀錄的筆數，若找不到則傳回 0 */
/*---------------------------------------------------------------------------*/
int bsearchRows(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp, cnt=1;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
while(!(*compar)(key, p+size)) {p+=size; cnt++;}
return cnt;
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return 0;
}
```
### 範例7：qsort 與 bsearch 的限制 (以新方式改寫)
* 參考：投影片講義 P.25 ~ 26
* 題目：與範例6相同，但請使用 bsearchFirst()、bsearchRows() 方法完成。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example07.ec
```C=
#include <stdio.h>
#include <stdlib.h>
void order_details_load();
void orders_load(char *);
int comp_func(const void *, const void *);
void *bsearchFirst(const void *, const void *, size_t, size_t, int(*)(const void *, const void *));
int bsearchRows(const void *, const void *, size_t, size_t, int (*)(const void *, const void *));
exec sql begin declare section;
struct _order_details {
int order_id;
double unit_price, quantity;
};
typedef struct _order_details ORDER_DETAILS;
ORDER_DETAILS *ORDER_DETAILSs = NULL;
static int ORDER_DETAILS_TOT = 0;
struct _orders {
int order_id;
ORDER_DETAILS *odPt;
};
typedef struct _orders ORDERS;
ORDERS *ORDERSs = NULL;
static int ORDERS_TOT = 0;
char sqlStm[1000]; /* 用來產生自訂 SQL */
exec sql end declare section;
/*----------------------------------------------------------------------------*/
void main()
{
ORDER_DETAILS *odPt = NULL;
ORDERS *oPt = NULL;
double sum = 0;
int i, j, cnt;
exec sql connect to 'postgres';
order_details_load();
orders_load("19980501"); /* 限制 19980501 之後的訂單 */
for(i = 0, oPt = ORDERSs; i < ORDERS_TOT; i++, oPt++) {
odPt = bsearchFirst(&oPt->order_id, ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料搜尋 (會搜尋到相同 order_id 的第一筆) */
cnt = bsearchRows(&oPt->order_id, ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 計算相同 order_id 的筆數 */
if(odPt != NULL) {
for(j = 0; j < cnt; j++, odPt++)
sum += odPt->unit_price * odPt->quantity;
printf("order_id=%d, 金額總計=%f\n", oPt->order_id, sum);
sum = 0;
}
}
}
/*----------------------------------------------------------------------------*/
void order_details_load()
{
exec sql begin declare section;
ORDER_DETAILS *odPt = NULL;
exec sql end declare section;
sprintf(sqlStm, "select count(*) from northwind.order_details;");
exec sql prepare odCnt from :sqlStm;
exec sql execute odCnt into :ORDER_DETAILS_TOT;
printf("SQL=%s, ORDER_DETAILS_TOT=%d\n", sqlStm, ORDER_DETAILS_TOT);
ORDER_DETAILSs = calloc(ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS));
if(ORDER_DETAILS_TOT != 0 && ORDER_DETAILSs == NULL) {printf("calloc error!\n"); exit(1);}
sprintf(sqlStm, "select order_id, unit_price, quantity from northwind.order_details;");
printf("SQL=%s\n", sqlStm);
exec sql prepare odQry from :sqlStm;
exec sql declare odCur cursor for odQry;
exec sql open odCur;
for(odPt = ORDER_DETAILSs;;odPt++) {
exec sql fetch odCur into :odPt->order_id, :odPt->unit_price, :odPt->quantity;
if(sqlca.sqlcode == 100) break;
}
exec sql close odCur;
qsort(ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料排序 */
}
/*----------------------------------------------------------------------------*/
void orders_load(order_date)
exec sql begin declare section;
char *order_date;
exec sql end declare section;
{
exec sql begin declare section;
ORDERS *oPt = NULL;
exec sql end declare section;
exec sql select count(*) into :ORDERS_TOT from northwind.orders where order_date >= :order_date;
ORDERSs = calloc(ORDERS_TOT, sizeof(ORDERS));
if(ORDERS_TOT != 0 && ORDERSs == NULL) {printf("calloc error!\n"); exit(1);}
exec sql declare oCur cursor for
select order_id from northwind.orders where order_date >= :order_date;
exec sql open oCur;
for(oPt = ORDERSs;;oPt++) {
exec sql fetch oCur into :oPt->order_id;
if(sqlca.sqlcode == 100) break;
}
exec sql close oCur;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
/*---------------------------------------------------------------------------*/
/* bsearchFirst()： */
/* 與 bsearch() 用法相同，但若尋找到多筆相同 key 值之紀錄時， */
/* 會傳回第一筆具有此 key 值之紀錄，若找不到則傳回 NULL */
/*---------------------------------------------------------------------------*/
void *bsearchFirst(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
return ((void *)p);
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return (NULL);
}
/*---------------------------------------------------------------------------*/
/* bsearchRows()： */
/* 以 bsearch() 方法尋找具有相同 key 值之紀錄的筆數，若找不到則傳回 0 */
/*---------------------------------------------------------------------------*/
int bsearchRows(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp, cnt=1;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
while(!(*compar)(key, p+size)) {p+=size; cnt++;}
return cnt;
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return 0;
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example07.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example07 example07.c
```
### 範例8：不定數量的記憶體配置方式 (載入時不須排序)
* 參考：投影片講義 P.31 ~ 35
* 題目：改寫範例7，令表格 order_details 的數量視為不固定。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example08.ec
```C=
#include <stdio.h>
#include <stdlib.h>
void order_details_load();
void orders_load(char *);
int comp_func(const void *, const void *);
int upper_power_of_two(const int);
void *order_details_mem_insert(void *, void *, int *, const int);
void *bsearchFirst(const void *, const void *, size_t, size_t, int(*)(const void *, const void *));
int bsearchRows(const void *, const void *, size_t, size_t, int (*)(const void *, const void *));
exec sql begin declare section;
struct _order_details {
int order_id;
double unit_price, quantity;
};
typedef struct _order_details ORDER_DETAILS;
ORDER_DETAILS *ORDER_DETAILSs = NULL;
static int ORDER_DETAILS_TOT = 0;
struct _orders {
int order_id;
ORDER_DETAILS *odPt;
};
typedef struct _orders ORDERS;
ORDERS *ORDERSs = NULL;
static int ORDERS_TOT = 0;
char sqlStm[1000]; /* 用來產生自訂 SQL */
exec sql end declare section;
/*----------------------------------------------------------------------------*/
void main()
{
ORDER_DETAILS *odPt = NULL;
ORDERS *oPt = NULL;
double sum = 0;
int i, j, cnt;
exec sql connect to 'postgres';
order_details_load();
orders_load("19980501"); /* 限制 19980501 之後的訂單 */
for(i = 0, oPt = ORDERSs; i < ORDERS_TOT; i++, oPt++) {
odPt = bsearchFirst(&oPt->order_id, ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料搜尋 (會搜尋到相同 order_id 的第一筆) */
cnt = bsearchRows(&oPt->order_id, ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 計算相同 order_id 的筆數 */
if(odPt != NULL) {
for(j = 0; j < cnt; j++, odPt++)
sum += odPt->unit_price * odPt->quantity;
printf("order_id=%d, 金額總計=%f\n", oPt->order_id, sum);
sum = 0;
}
}
}
/*----------------------------------------------------------------------------*/
void order_details_load()
{
exec sql begin declare section;
ORDER_DETAILS data;
exec sql end declare section;
sprintf(sqlStm, "select order_id, unit_price, quantity from northwind.order_details;");
printf("SQL=%s\n", sqlStm);
exec sql prepare odQry from :sqlStm;
exec sql declare odCur cursor for odQry;
exec sql open odCur;
for(;;) {
exec sql fetch odCur into :data.order_id, :data.unit_price, :data.quantity;
if(sqlca.sqlcode == 100) break;
ORDER_DETAILSs = (ORDER_DETAILS *) order_details_mem_insert(&data, ORDER_DETAILSs, &ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS));
}
exec sql close odCur;
qsort(ORDER_DETAILSs, ORDER_DETAILS_TOT, sizeof(ORDER_DETAILS), comp_func); /* 資料排序 */
}
/*----------------------------------------------------------------------------*/
void orders_load(order_date)
exec sql begin declare section;
char *order_date;
exec sql end declare section;
{
exec sql begin declare section;
ORDERS *oPt = NULL;
exec sql end declare section;
exec sql select count(*) into :ORDERS_TOT from northwind.orders where order_date >= :order_date;
ORDERSs = calloc(ORDERS_TOT, sizeof(ORDERS));
if(ORDERS_TOT != 0 && ORDERSs == NULL) {printf("calloc error!\n"); exit(1);}
exec sql declare oCur cursor for
select order_id from northwind.orders where order_date >= :order_date;
exec sql open oCur;
for(oPt = ORDERSs;;oPt++) {
exec sql fetch oCur into :oPt->order_id;
if(sqlca.sqlcode == 100) break;
}
exec sql close oCur;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return (*(int *)val1 - *(int *)val2);
}
/*----------------------------------------------------------------------------*/
/* 回傳最小大於等於 c 之 2^n */
/*----------------------------------------------------------------------------*/
int upper_power_of_two(const int c)
{
int v = c;
v--;
v |= v >> 1;
v |= v >> 2;
v |= v >> 4;
v |= v >> 8;
v |= v >> 16;
v++;
return v;
}
/*----------------------------------------------------------------------------*/
/* 於 base 中新增一大小為 size 之 data，若 base 的空間不足，
則 realloc base 之空間至最小大於本身之 2^n bytes。
data 新增於 base 之最後一筆，不排序，新增後將總數量 num 加 1 */
/*----------------------------------------------------------------------------*/
void *order_details_mem_insert(void *data, void *base, int *num, const int size)
{
int pow2;
char *new_base;
pow2 = upper_power_of_two(*num);
if(pow2 == (*num)) {
if((*num) == 0) pow2 = 1;
else pow2 *= 2;
new_base = realloc(base, pow2 * size);
if((new_base == NULL)) {
free(base);
printf("new_base (re)allocating memory error.\n");
exit(1);
}
}
else new_base = base;
memcpy(&(new_base[(*num) * size]), data, size);
(*num)++;
return new_base;
}
/*---------------------------------------------------------------------------*/
/* bsearchFirst()： */
/* 與 bsearch() 用法相同，但若尋找到多筆相同 key 值之紀錄時， */
/* 會傳回第一筆具有此 key 值之紀錄，若找不到則傳回 NULL */
/*---------------------------------------------------------------------------*/
void *bsearchFirst(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
return ((void *)p);
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return (NULL);
}
/*---------------------------------------------------------------------------*/
/* bsearchRows()： */
/* 以 bsearch() 方法尋找具有相同 key 值之紀錄的筆數，若找不到則傳回 0 */
/*---------------------------------------------------------------------------*/
int bsearchRows(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp, cnt=1;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
while(!(*compar)(key, p+size)) {p+=size; cnt++;}
return cnt;
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return 0;
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example08.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example08 example08.c
```
### 範例9：不定數量的記憶體配置方式 (載入時須排序) (異常版)
* 參考：投影片講義 P.36 ~ 42
* 題目：依據投影片講義 P.36 所述，將檔案中的資料，不重複地填入資料庫表格 northwind.my_table 中 (利用第一個設計方式)
* 執行方式：依序執行 1.txt、2.txt 兩個檔案
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example09.ec
```C=
#include <stdio.h>
#include <stdlib.h>
#define dbErr() dbErr_log(__FILE__, __LINE__)
void my_table_load();
void input_file_load(char *);
int getfCount(char *);
int comp_func(const void *, const void *);
void dbErr_log(char *, int);
exec sql whenever sqlerror call dbErr; /* 若出現任何資料庫異常，則呼叫 dbErr */
exec sql begin declare section;
/* table: my_table */
struct _my_table {
char key[11];
};
typedef struct _my_table MY_TABLE;
MY_TABLE *MY_TABLEs = NULL;
static int MY_TABLE_TOT = 0;
/* 輸入檔 */
struct _input_file {
char key[11];
};
typedef struct _input_file INPUT_FILE;
INPUT_FILE *INPUT_FILEs = NULL;
static int INPUT_FILE_TOT = 0;
exec sql end declare section;
/*----------------------------------------------------------------------------*/
void main(int argc, char *argv[])
{
exec sql begin declare section;
INPUT_FILE *ifPt = NULL;
exec sql end declare section;
MY_TABLE *myPt = NULL, *tmpPt = NULL;
int i, j;
exec sql connect to 'postgres';
printf("一、載入資料：\n");
my_table_load();
printf("載入table (my_table)：筆數=%d.\n 資料：", MY_TABLE_TOT);
for(i = 0, myPt = MY_TABLEs; i < MY_TABLE_TOT; i++, myPt++) printf("%s ", myPt->key);
printf("\n");
input_file_load(argv[1]);
printf("載入檔案：檔名=%s, 筆數=%d.\n 資料：", argv[1], INPUT_FILE_TOT);
for(i = 0, ifPt = INPUT_FILEs; i < INPUT_FILE_TOT; i++, ifPt++) printf("%s ", ifPt->key);
printf("\n");
printf("二、新增資料：\n");
for(i = 0, ifPt = INPUT_FILEs; i < INPUT_FILE_TOT; i++, ifPt++) {
myPt = bsearch(ifPt->key, MY_TABLEs, MY_TABLE_TOT, sizeof(MY_TABLE), comp_func);
if(myPt == NULL) {
exec sql insert into northwind.my_table values (:ifPt->key);
printf("(步驟%02d).新增==>insert into northwind.my_table values ('%s');\n", i+1, ifPt->key);
printf(" 記憶體內容：");
for(j = 0, tmpPt = MY_TABLEs; j < MY_TABLE_TOT; j++, tmpPt++) printf("%s ", tmpPt->key);
printf("\n");
}
else
printf("(步驟%02d).不須新增==>資料已存在：%s.\n", i+1, ifPt->key);
}
exec sql commit work; /* transaction commit */
}
/*----------------------------------------------------------------------------*/
void my_table_load()
{
exec sql begin declare section;
MY_TABLE *myPt = NULL;
exec sql end declare section;
exec sql select count(*) into :MY_TABLE_TOT from northwind.my_table;
MY_TABLEs = calloc(MY_TABLE_TOT, sizeof(MY_TABLE));
if(MY_TABLE_TOT != 0 && MY_TABLEs == NULL) {printf("calloc error!\n"); exit(1);}
exec sql declare myCur cursor for
select key from northwind.my_table;
exec sql open myCur;
for(myPt = MY_TABLEs;;myPt++) {
exec sql fetch myCur into :myPt->key;
if(sqlca.sqlcode == 100) break;
}
exec sql close myCur;
qsort(MY_TABLEs, MY_TABLE_TOT, sizeof(MY_TABLE), comp_func);
}
/*----------------------------------------------------------------------------*/
void input_file_load(char *file_name)
{
INPUT_FILE *ifPt = NULL;
FILE *filePt = NULL;
char rec[16] = "\0";
INPUT_FILE_TOT = getfCount(file_name);
switch(INPUT_FILE_TOT) {
case -1:
printf("%s not found.\n", file_name);
exit(1);
case 0:
return; /* 無資料則不須處理 */
default:
INPUT_FILEs = calloc(INPUT_FILE_TOT, sizeof(INPUT_FILE));
if(INPUT_FILEs == NULL) {
printf("memory allocation error.\n");
exit(1);
}
break;
}
filePt = fopen(file_name, "r");
if(filePt == NULL) {
printf("%s read error.\n", file_name);
exit(1);
}
ifPt = INPUT_FILEs;
while((fgets(rec, 12, filePt)) != NULL) {
strncpy(ifPt->key, &rec[0], 1);
ifPt++;
}
}
/*----------------------------------------------------------------------------*/
int getfCount(char *fName)
{
FILE *fPtr;
char recordLine[4096];
int fCount = 0;
if((fPtr = fopen(fName, "r")) == NULL) return -1;
while((fgets(recordLine, 4096, fPtr)) != NULL ) fCount++;
fclose(fPtr);
return fCount;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return(strcmp((char *) val1, (char *) val2));
}
/*----------------------------------------------------------------------------*/
void dbErr_log(char *ecfName, int errLine)
{
char errmsg[70];
int msg_len;
printf("DB ERROR==>異常代碼：%d, 程式檔名：%s.\n", sqlca.sqlcode, ecfName);
printf("sqlerrmc：%s\n", sqlca.sqlerrm.sqlerrmc);
printf("sqlstate：%s, sqlerrml：%d\n", sqlca.sqlstate, sqlca.sqlerrm.sqlerrml);
exec sql rollback work;
exit(1);
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example09.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example09 example09.c
```
### 範例10：不定數量的記憶體配置方式 (載入時須排序) (正常版)
* 參考：投影片講義 P.43 ~ 49
* 題目：依據範例9，利用第二個設計方式 (投影片講義 P.43) 來修正異常。
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example10.ec
```C=
#include <stdio.h>
#include <stdlib.h>
#define dbErr() dbErr_log(__FILE__, __LINE__)
void my_table_load();
void input_file_load(char *);
int getfCount(char *);
int comp_func(const void *, const void *);
int upper_power_of_two(const int);
void dbErr_log(char *, int);
exec sql whenever sqlerror call dbErr; /* 若出現任何資料庫異常，則呼叫 dbErr */
exec sql begin declare section;
/* table: my_table */
struct _my_table {
char key[11];
};
typedef struct _my_table MY_TABLE;
MY_TABLE *MY_TABLEs = NULL;
static int MY_TABLE_TOT = 0;
/* 輸入檔 */
struct _input_file {
char key[11];
};
typedef struct _input_file INPUT_FILE;
INPUT_FILE *INPUT_FILEs = NULL;
static int INPUT_FILE_TOT = 0;
exec sql end declare section;
MY_TABLE *my_table_mem_insert(MY_TABLE *, MY_TABLE *, int *, const int);
/*----------------------------------------------------------------------------*/
void main(int argc, char *argv[])
{
exec sql begin declare section;
INPUT_FILE *ifPt = NULL;
exec sql end declare section;
MY_TABLE *myPt = NULL, data, *tmpPt = NULL;
int i, j;
exec sql connect to 'postgres';
printf("一、載入資料：\n");
my_table_load();
printf("載入table (my_table)：筆數=%d.\n 資料：", MY_TABLE_TOT);
for(i = 0, myPt = MY_TABLEs; i < MY_TABLE_TOT; i++, myPt++) printf("%s ", myPt->key);
printf("\n");
input_file_load(argv[1]);
printf("載入檔案：檔名=%s, 筆數=%d.\n 資料：", argv[1], INPUT_FILE_TOT);
for(i = 0, ifPt = INPUT_FILEs; i < INPUT_FILE_TOT; i++, ifPt++) printf("%s ", ifPt->key);
printf("\n");
printf("二、新增資料：\n");
for(i = 0, ifPt = INPUT_FILEs; i < INPUT_FILE_TOT; i++, ifPt++) {
myPt = bsearch(ifPt->key, MY_TABLEs, MY_TABLE_TOT, sizeof(MY_TABLE), comp_func);
if(myPt == NULL) {
exec sql insert into northwind.my_table values (:ifPt->key);
printf("(步驟%02d).新增==>insert into northwind.my_table values ('%s');\n", i+1, ifPt->key);
strncpy(data.key, ifPt->key, strlen(ifPt->key)); /* 設定新資料 */
MY_TABLEs = my_table_mem_insert(&data, MY_TABLEs, &MY_TABLE_TOT, sizeof(MY_TABLE));
printf(" 記憶體內容：");
for(j = 0, tmpPt = MY_TABLEs; j < MY_TABLE_TOT; j++, tmpPt++) printf("%s ", tmpPt->key);
printf("\n");
}
else
printf("(步驟%02d).不須新增==>資料已存在：%s.\n", i+1, ifPt->key);
}
exec sql commit work; /* transaction commit */
}
/*----------------------------------------------------------------------------*/
void my_table_load()
{
exec sql begin declare section;
MY_TABLE *myPt = NULL;
exec sql end declare section;
int pow2 = 0;
exec sql select count(*) into :MY_TABLE_TOT from northwind.my_table;
pow2 = upper_power_of_two(MY_TABLE_TOT); /* 配置數量須為 2^n，使稍後可以新增資料 */
MY_TABLEs = calloc(pow2, sizeof(MY_TABLE));
if(pow2 != 0 && MY_TABLEs == NULL) {printf("calloc error!\n"); exit(1);}
exec sql declare myCur cursor for
select key from northwind.my_table;
exec sql open myCur;
for(myPt = MY_TABLEs;;myPt++) {
exec sql fetch myCur into :myPt->key;
if(sqlca.sqlcode == 100) break;
}
exec sql close myCur;
qsort(MY_TABLEs, MY_TABLE_TOT, sizeof(MY_TABLE), comp_func);
}
/*----------------------------------------------------------------------------*/
void input_file_load(char *file_name)
{
INPUT_FILE *ifPt = NULL;
FILE *filePt = NULL;
char rec[16] = "\0";
INPUT_FILE_TOT = getfCount(file_name);
switch(INPUT_FILE_TOT) {
case -1:
printf("%s not found.\n", file_name);
exit(1);
case 0:
return; /* 無資料則不須處理 */
default:
INPUT_FILEs = calloc(INPUT_FILE_TOT, sizeof(INPUT_FILE));
if(INPUT_FILEs == NULL) {
printf("memory allocation error.\n");
exit(1);
}
break;
}
filePt = fopen(file_name, "r");
if(filePt == NULL) {
printf("%s read error.\n", file_name);
exit(1);
}
ifPt = INPUT_FILEs;
while((fgets(rec, 12, filePt)) != NULL) {
strncpy(ifPt->key, &rec[0], 1);
ifPt++;
}
}
/*----------------------------------------------------------------------------*/
int getfCount(char *fName)
{
FILE *fPtr;
char recordLine[4096];
int fCount = 0;
if((fPtr = fopen(fName, "r")) == NULL) return -1;
while((fgets(recordLine, 4096, fPtr)) != NULL ) fCount++;
fclose(fPtr);
return fCount;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return(strcmp((char *) val1, (char *) val2));
}
/*----------------------------------------------------------------------------*/
/* 回傳最小大於等於 c 之 2^n */
/*----------------------------------------------------------------------------*/
int upper_power_of_two(const int c)
{
int v = c;
v--;
v |= v >> 1;
v |= v >> 2;
v |= v >> 4;
v |= v >> 8;
v |= v >> 16;
v++;
return v;
}
/*----------------------------------------------------------------------------*/
/* 將 data 填入 MY_TABLEs 中，MY_TABLEs 之資料以 key 欄位排序
若發生 key 相同情形時，該資料不重複填入 MY_TABLEs 中。 */
/*----------------------------------------------------------------------------*/
MY_TABLE *my_table_mem_insert(MY_TABLE *data, MY_TABLE *base, int *num, const int size)
{
MY_TABLE *p, *new_base, *tmp_base;
int lim, cmp, pow2, pn, bn=0;
tmp_base = base;
for (lim = *num; lim != 0; lim >>= 1) {
p = tmp_base + (lim >> 1);
pn = bn + (lim >> 1);
cmp = strcmp(data->key, p->key);
if (cmp == 0) return base;
if (cmp > 0) { /* key > p: move right */
tmp_base = p + 1;
bn = pn + 1;
lim--;
} /* else move left */
}
pow2 = upper_power_of_two(*num);
if(pow2 == (*num)) {
if((*num) == 0) pow2 = 1;
else pow2 *= 2;
new_base = realloc(base, pow2 * size);
if((new_base == NULL)) {
free(base);
printf("new_base (re)allocating memory error.\n");
exit(1);
}
}
else new_base = base;
(*num)++;
/* 若填入之資料不是最後一筆，則需 memmove 後面的資料 */
if(bn != (*num) - 1) memmove(&new_base[bn+1], &new_base[bn], ((*num) - bn - 1) * size);
memcpy(&new_base[bn], data, size);
return new_base;
}
/*----------------------------------------------------------------------------*/
void dbErr_log(char *ecfName, int errLine)
{
char errmsg[70];
int msg_len;
printf("DB ERROR==>異常代碼：%d, 程式檔名：%s.\n", sqlca.sqlcode, ecfName);
printf("sqlerrmc：%s\n", sqlca.sqlerrm.sqlerrmc);
printf("sqlstate：%s, sqlerrml：%d\n", sqlca.sqlstate, sqlca.sqlerrm.sqlerrml);
exec sql rollback work;
exit(1);
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example10.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example10 example10.c
```
### ※ 記憶體配置策略總整理
* 參考：投影片講義 P.50
1. 數量經常固定：
* 例如：介面檔/table的欄位長度
* 使用：
* 陣列
* 數量很大時：calloc (值會初始化為0)、malloc (值不會初始化)
2. 數量可能不固定，但在執行期間固定，也很容易計算
* 例如：公用表格筆數、檔案資料筆數
* 使用：calloc (值會初始化為0)、malloc (值不會初始化)
3. 數量在執行期間不固定 (不定數量的記憶體配置方式)
* 例如：筆數不好算/不想算、記憶體中的資料於執行時期必須隨時變動
* 利用 realloc 重新配置
* 記憶體配置數量 = 2^N，或
* 記憶體配置數量 = 目前已配置記憶體數量 × 2
* 有限度地浪費空間，最多不會浪費大於已配置記憶體一倍的空間。
### 範例11：排序欄位於彙總時的應用
* 參考：投影片講義 P.52 ~ 55
* 題目：參考投影片講義 P.54，利用多層key欄位的方式：
1. 以姓名彙總金額
2. 以姓名+費用代號彙總金額
* 限制：程式中只能 qsort 一次
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example11.ec
```C=
#include <stdio.h>
#include <stdlib.h>
struct _ama_rec {
char key[20], key2[20];
char name[7], fee_item[4];
int pid, amount;
};
typedef struct _ama_rec AMA_REC;
int comp_func(const void *, const void *);
static int AMA_REC_TOT = 0;
/*----------------------------------------------------------------------------*/
void main()
{
int i, sum = 0;
AMA_REC *aPt = NULL;
AMA_REC data[] = {
{"", "", "小華", "D01", 1, 100},
{"", "", "小華", "D01", 2, 200},
{"", "", "小華", "D02", 1, 300},
{"", "", "小華", "D02", 2, 400},
{"", "", "小明", "D01", 1, 500},
{"", "", "小明", "D01", 2, 600},
{"", "", "小明", "D02", 1, 700},
{"", "", "小明", "D02", 2, 800}
};
AMA_REC_TOT = sizeof(data) / sizeof(AMA_REC); /* 計算筆數 */
for(i = 0, aPt = data; i < AMA_REC_TOT; i++, aPt++) {
sprintf(aPt->key, "%s.%s", aPt->name, aPt->fee_item); /* 設定 key */
sprintf(aPt->key2, "%s", aPt->name); /* 設定 key2 */
}
qsort(data, AMA_REC_TOT, sizeof(AMA_REC), comp_func);
printf("一、以姓名彙總：\n");
for(i = 0, aPt = data; i < AMA_REC_TOT; i++, aPt++) {
sum += aPt->amount;
if(strcmp(aPt->key2, (aPt+1)->key2) != 0) {
printf(" name=%s, sum(amount)=%d.\n", aPt->name, sum);
sum = 0;
}
}
printf("二、以姓名+費用代號彙總：\n");
for(i = 0, aPt = data; i < AMA_REC_TOT; i++, aPt++) {
sum += aPt->amount;
if(strcmp(aPt->key, (aPt+1)->key) != 0) {
printf(" name=%s, fee_item=%s, sum(amount)=%d.\n", aPt->name, aPt->fee_item, sum);
sum = 0;
}
}
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *val1, const void *val2)
{
return(strcmp((char *) val1, (char *) val2));
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example11.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example11 example11.c
```
### 範例12：排序欄位於彙總時的應用 (改寫 compare function)
* 參考：投影片講義 P.56 ~ 58
* 題目：參考投影片講義 P.56，改寫 compare function，配合二元搜尋協助彙總：
1. 以小華彙總金額
2. 以小明+D02 彙總金額
* 限制：
1. 程式中只能 qsort 一次
2. 結構中只能有一個 key
3. 以姓名+費用代號+產品代號排序
* 原始碼位置：/home/gpadmin/cfchen/ecpg/example12.ec
```C=
#include <stdio.h>
#include <stdlib.h>
struct _ama_rec {
char key[20];
char name[7], fee_item[4];
int pid, amount;
};
typedef struct _ama_rec AMA_REC;
void *bsearchFirst(const void *, const void *, size_t, size_t, int(*)(const void *, const void *));
int bsearchRows(const void *, const void *, size_t, size_t, int (*)(const void *, const void *));
int comp_func(const void *, const void *);
int comp_func2(const void *, const void *);
int comp_func3(const void *, const void *);
static int AMA_REC_TOT = 0;
/*----------------------------------------------------------------------------*/
void main()
{
int i, cnt, sum = 0;
AMA_REC *aPt = NULL;
AMA_REC data[] = {
{"", "小華", "D01", 1, 100},
{"", "小華", "D01", 2, 200},
{"", "小華", "D02", 1, 300},
{"", "小華", "D02", 2, 400},
{"", "小明", "D01", 1, 500},
{"", "小明", "D01", 2, 600},
{"", "小明", "D02", 1, 700},
{"", "小明", "D02", 2, 800}
};
AMA_REC_TOT = sizeof(data) / sizeof(AMA_REC); /* 計算筆數 */
for(i = 0, aPt = data; i < AMA_REC_TOT; i++, aPt++) {
sprintf(aPt->key, "%s.%s.%03d", aPt->name, aPt->fee_item, aPt->pid); /* 設定 key */
}
qsort(data, AMA_REC_TOT, sizeof(AMA_REC), comp_func);
/* 小華 */
cnt = bsearchRows("小華", data, AMA_REC_TOT, sizeof(AMA_REC), comp_func2);
aPt = bsearchFirst("小華", data, AMA_REC_TOT, sizeof(AMA_REC), comp_func2);
for(i = 0; i < cnt; i++, aPt++) sum += aPt->amount;
printf("小華：sum(amount)=%d.\n", sum);
sum = 0;
/* 小明+D02 */
cnt = bsearchRows("小明.D02", data, AMA_REC_TOT, sizeof(AMA_REC), comp_func3);
aPt = bsearchFirst("小明.D02", data, AMA_REC_TOT, sizeof(AMA_REC), comp_func3);
for(i = 0; i < cnt; i++, aPt++) sum += aPt->amount;
printf("小明.D02：sum(amount)=%d.\n", sum);
sum = 0;
}
/*----------------------------------------------------------------------------*/
int comp_func(const void *str1, const void *str2)
{
return(strcmp((char *) str1, (char *) str2));
}
/*----------------------------------------------------------------------------*/
/* 協助二元搜尋法比對姓名(name) */
/*----------------------------------------------------------------------------*/
comp_func2(const void *str1, const void* str2)
{
char k1[20]="\0";
char s[40]="\0";
sscanf(str2, "%[^.]", k1);
sprintf(s, "%s", k1);
return(strcmp((char *) str1, (char *) s));
}
/*----------------------------------------------------------------------------*/
/* 協助二元搜尋法比對姓名 + 費用代號 (name + fee_item) */
/*----------------------------------------------------------------------------*/
comp_func3(const void *str1, const void* str2)
{
char k1[20]="\0", k2[20]="\0";
char s[40]="\0";
sscanf(str2, "%[^.].%[^.]", k1, k2);
sprintf(s, "%s.%s", k1, k2);
return(strcmp((char *) str1, (char *) s));
}
/*---------------------------------------------------------------------------*/
/* bsearchFirst()： */
/* 與 bsearch() 用法相同，但若尋找到多筆相同 key 值之紀錄時， */
/* 會傳回第一筆具有此 key 值之紀錄，若找不到則傳回 NULL */
/*---------------------------------------------------------------------------*/
void *bsearchFirst(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
return ((void *)p);
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return (NULL);
}
/*---------------------------------------------------------------------------*/
/* bsearchRows()： */
/* 以 bsearch() 方法尋找具有相同 key 值之紀錄的筆數，若找不到則傳回 0 */
/*---------------------------------------------------------------------------*/
int bsearchRows(const void *key, const void *base0, size_t nmemb, size_t size, int (*compar)(const void *, const void *))
{
const char *base = base0;
int lim, cmp, cnt=1;
const char *p;
for (lim = nmemb; lim != 0; lim >>= 1) {
p = base + (lim >> 1) * size;
cmp = (*compar)(key, p);
if (cmp == 0) {
while(!(*compar)(key, p-size)) p-=size;
while(!(*compar)(key, p+size)) {p+=size; cnt++;}
return cnt;
}
if (cmp > 0) { /* key > p: move right */
base = (char *)p + size;
lim--;
} /* else move left */
}
return 0;
}
/*----------------------------------------------------------------------------*/
```
* 編譯指令：
```
ecpg example12.pgc
gcc -I/usr/local/greenplum-db-6.13.0/include -L/usr/local/greenplum-db-6.13.0/lib -lecpg -o example12 example12.c
```
---
### Reference：
* C/C++ 語言新手十三誡
https://www.ptt.cc/bbs/C_and_CPP/M.1465304337.A.9F2.html
* C Traps and Pitfalls
http://www.literateprogramming.com/ctraps.pdf
* C 語言的指標頭腦體操：
https://stackoverflow.com/questions/8208021/how-to-increment-a-pointer-address-and-pointers-value/8208106#8208106
Sign in with Wallet
Connect another wallet