ODBC

ODBC（Open Database Connectivity）提供了一組標準的 API 來訪問資料庫管理系統（DBMS）。 這些 API 利用 SQL 來完成其大部分任務。 ODBC 是針對 C 的 API，不過很多的程式語言都有相關的 bindings。如果想使用 C 來寫 ODBC 程式， 可以從 ODBC from C Tutorial Part 1 開始學習如何撰寫。

The ODBC architecture has four components:

• Application Performs processing and calls ODBC functions to submit SQL statements and retrieve results.

• Driver Manager Loads and unloads drivers on behalf of an application. Processes ODBC function calls or passes them to a driver.

• Driver Processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. If necessary, the driver modifies an application's request so that the request conforms to syntax supported by the associated DBMS.

• Data Source Consists of the data the user wants to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS.

In ODBC there are four main handle types and you will need to know at least three to do anything useful:

• SQLHENV - environment handle

  This is the first handle you will need as everything else is effectively in the environment. Once you have an environment handle you can define the version of ODBC you require, enable connection pooling and allocate connection handles with SQLSetEnvAttr and SQLAllocHandle.

• SQLHDBC - connection handle

  You need one connection handle for each data source you are going to connect to. Like environment handles, connection handles have attributes which you can retrieve and set with SQLSetConnectAttr and SQLGetConnectAttr.

• SQLHSTMT - statement handle

  Once you have a connection handle and have connected to a data source you allocate statement handles to execute SQL or retrieve meta data. As with the other handles you can set and get statement attributes with SQLSetStmtAttr and SQLGetStmtAttr.

• SQLHDESC - descriptor handle

  Descriptor handles are rarely used by applications even though they are very useful for more complex operations. Descriptor handles will be covered in later tutorials.

如果要查詢目前 unixODBC 的版本，使用下列的指令：

odbcinst --version

Listing Installed Drivers and Data Sources

如果是使用 unixODBC，可以使用下列命令列出已安裝的資料來源：

odbcinst -q -s

如果自己寫一個程式列出來已安裝的資料來源：

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

int main() {
    SQLHENV env = NULL;
    char driver[256];
    char attr[256];
    SQLSMALLINT driver_ret;
    SQLSMALLINT attr_ret;
    SQLUSMALLINT direction;
    SQLRETURN ret;

    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (ret != SQL_SUCCESS) {
        printf("SQLAllocHandle failed.\n");
    }

    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

    direction = SQL_FETCH_FIRST;
    while (SQL_SUCCEEDED(ret = SQLDrivers(env, direction, driver,
                                          sizeof(driver), &driver_ret, attr,
                                          sizeof(attr), &attr_ret))) {
        direction = SQL_FETCH_NEXT;
        printf("%s - %s\n", driver, attr);
        if (ret == SQL_SUCCESS_WITH_INFO)
            printf("\tdata truncation\n");
    }

    if (env != NULL) {
        SQLFreeHandle(SQL_HANDLE_ENV, env);
    }
    return 0;
}

在 Windows 平台，需要連結 odbc32 library，使用 UnixODBC 平台則需要連結 libodbc (-lodbc) 才行。

接下來使用 PostgreSQL ODBC driver 測試，測試是否能夠正確連接到資料庫。

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type) {
    SQLINTEGER i = 0;
    SQLINTEGER native;
    SQLCHAR state[7];
    SQLCHAR text[256];
    SQLSMALLINT len;
    SQLRETURN ret;

    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len);
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    } while (ret == SQL_SUCCESS);
}

int main() {
    SQLHENV env = NULL;
    SQLHDBC dbc = NULL;
    SQLRETURN ret;
    SQLCHAR outstr[1024];
    SQLSMALLINT outstrlen;

    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return(1);
    }

    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return(1);
    }

    ret = SQLDriverConnect(dbc, NULL, "DSN=PostgreSQL;", SQL_NTS, outstr,
                           sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)) {
        printf("Connected\n");
        printf("Returned connection string was:\n\t%s\n", outstr);
        if (ret == SQL_SUCCESS_WITH_INFO) {
            printf("Driver reported the following diagnostics\n");
            extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
        }
        SQLDisconnect(dbc); /* disconnect from driver */
    } else {
        fprintf(stderr, "Failed to connect\n");
        extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
    }

    if (dbc)
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);

    if (env)
        SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}

下面列出目前資料庫中表格的資料。

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

int main() {
    SQLHENV env = NULL;
    SQLHDBC dbc = NULL;
    SQLHSTMT stmt = NULL;
    SQLRETURN ret;
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLCHAR buf[5][64];
    int row = 0;
    SQLLEN indicator[5];
    int i;

    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    ret = SQLDriverConnect(dbc, NULL, "DSN=PostgreSQL;", SQL_NTS, NULL, 0, NULL,
                           SQL_DRIVER_COMPLETE);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "Failed to connect\n");
        goto End;
    }

    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "Failed to connect\n");
        goto End;
    }

    SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
    SQLNumResultCols(stmt, &columns);
    for (i = 0; i < columns; i++) {
        SQLBindCol(stmt, i + 1, SQL_C_CHAR, buf[i], sizeof(buf[i]),
                   &indicator[i]);
    }

    /* Fetch the data */
    while (SQL_SUCCEEDED(SQLFetch(stmt))) {
        /* display the results that will now be in the bound area's */
        for (i = 0; i < columns; i++) {
            if (indicator[i] == SQL_NULL_DATA) {
                printf("  Column %u : NULL\n", i);
            } else {
                printf("  Column %u : %s\n", i, buf[i]);
            }
        }
    }

End:
    if (stmt)
        SQLFreeHandle(SQL_HANDLE_DBC, stmt);

    if (dbc)
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);

    if (env)
        SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}

接下來的程式使用 SQLExecDirect 與 SQLFetch 取得 PostgreSQL 的版本資訊。

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main() {
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;

    SQLRETURN ret;
    SQLCHAR Version[255];
    SQLLEN siVersion;

    char *sqlStatement = "Select version() as version";

    ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    ret = SQLDriverConnect(hdbc, NULL, "DSN=PostgreSQL;", SQL_NTS, NULL, 0,
                           NULL, SQL_DRIVER_COMPLETE);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "Failed to connect\n");
        goto End;
    }

    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLAllocHandle(SQL_HANDLE_STMT) failed.\n");
        goto End;
    }

    ret = SQLExecDirect(hstmt, sqlStatement, strlen(sqlStatement));
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLExecDirect() failed.\n");
        goto End;
    }

    while (1) {
        ret = SQLFetch(hstmt);
        if (ret == SQL_ERROR || ret == SQL_SUCCESS_WITH_INFO) {
            fprintf(stderr, "SQLFetch(hstmt) failed.\n");
            goto End;
        }

        if (ret == SQL_NO_DATA) {
            break;
        }

        if (ret == SQL_SUCCESS) {
            ret = SQLGetData(hstmt, 1, SQL_C_CHAR, Version, 255, &siVersion);
            printf("Versoin:\n%s\n", Version);
        }
    }

End:

    if (hstmt != SQL_NULL_HSTMT) {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        hstmt = SQL_NULL_HSTMT;
    }

    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        hdbc = SQL_NULL_HDBC;
    }

    if (henv != SQL_NULL_HENV) {
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
        hstmt = SQL_NULL_HSTMT;
    }

    return 0;
}

下面是 SQLPrepare/SQLExecute 的範例（包含使用 SQLBindParameter）。

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main() {
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;

    SQLRETURN ret;
    int RetParam = 1;
    SQLLEN cbRetParam = SQL_NTS;
    SQLCHAR Name[40];
    SQLLEN lenName = 0;
    SQLSMALLINT NumParams;

    char *sqlstr = NULL;

    ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (!SQL_SUCCEEDED(ret)) {
        printf("SQLAllocHandle failed.\n");
        return (1);
    }

    ret = SQLDriverConnect(hdbc, NULL, "DSN=PostgreSQL;", SQL_NTS, NULL, 0,
                           NULL, SQL_DRIVER_COMPLETE);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "Failed to connect\n");
        goto End;
    }

    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLAllocHandle(SQL_HANDLE_STMT) failed.\n");
        goto End;
    }

    sqlstr = "drop table if exists person";
    ret = SQLPrepare(hstmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLPrepare() failed.\n");
        goto End;
    }

    ret = SQLExecute(hstmt);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLExecute(drop) failed.\n");
        goto End;
    }

    sqlstr = "create table if not exists person (id integer, name varchar(40) "
             "not null)";
    ret = SQLPrepare(hstmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLPrepare(create) failed.\n");
        goto End;
    }

    ret = SQLExecute(hstmt);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLExecute() failed.\n");
        goto End;
    }

    ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
                           0, 0, &RetParam, 0, &cbRetParam);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLBindParameter(1) failed.\n");
        goto End;
    }

    ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 40,
                           0, Name, 40, &lenName);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLBindParameter(2) failed.\n");
        goto End;
    }

    ret = SQLPrepare(hstmt,
                     (SQLCHAR *)"insert into person (id, name) values (?, ?)",
                     SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLPrepare(insert) failed.\n");
        goto End;
    }

    SQLNumParams(hstmt, &NumParams);
    if (!SQL_SUCCEEDED(ret)) {
        fprintf(stderr, "SQLNumParams() failed.\n");
        goto End;
    }

    printf("Num params : %i\n", NumParams);
    strcpy(Name, "Orange");
    lenName = strlen(Name);

    ret = SQLExecute(hstmt);
    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        printf("Status : Success\n");
    } else {
        fprintf(stderr, "SQLExecute(insert) failed.\n");
    }

End:

    if (hstmt != SQL_NULL_HSTMT) {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        hstmt = SQL_NULL_HSTMT;
    }

    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        hdbc = SQL_NULL_HDBC;
    }

    if (henv != SQL_NULL_HENV) {
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
        hstmt = SQL_NULL_HSTMT;
    }

    return 0;
}

C++ Regular Expression

Posix Regular Expression

Posix Regular Expression 是 POSIX 所建立的其中一個標準（並且有 Basic 與 Extended 的分別）， 大多數有支援 POSIX 標準的 libc library 都有實作（包含 glibc）， 不過實作的細節可能在各個實作上會略有不同。因此，雖然 glibc 有內建，除非要相容於 GNU 工具程式的正規表示式， 否則不一定要使用 Posix Regular Expression。 對於 C 而言，最常被考慮的跨平台 Regular Expression library 為 PCRE 或者是 PCRE2。

下面是 1-9 位數不重複印出來的練習問題（在 Linux 測試，使用 glibc）：

#include <stdio.h>
#include <math.h>
#include <regex.h>

int main() {
    regex_t re;
    int number = 0;
    long max = 0;

    printf("Please give a number: ");
    scanf("%d", &number);

    if (number < 1 || number > 9) {
        printf("Out of range.\n");
        return (1);
    }

    max = round(pow(10, number)) - 1;
    regcomp(&re, "([0-9]).*\\1", REG_EXTENDED);
    for (long index = 1; index <= max; index++) {
        int value;
        char str[10];
        sprintf(str, "%ld", index);
        value = regexec(&re, str, 0, NULL, 0);

        if (value == REG_NOMATCH) {
            printf("%ld\n", index);
        }
    }

    regfree(&re);
    return 0;
}

PCRE 或者是 PCRE2 都有提供 Posix Regular Expression 相容的 API， 只是 Regular Expression 語法就沒有 Basic 與 Extended 的分別， 而是使用 PCRE 本身的語法。以 PCRE2 來說，只要改為 include pcre2posix.h， 連結時要加上 -lpcre2-posix 與 -lpcre2-8 即可。

#include <stdio.h>
#include <math.h>
#include <pcre2posix.h>

int main() {
    regex_t re;
    int number = 0;
    long max = 0;

    printf("Please give a number: ");
    scanf("%d", &number);

    if (number < 1 || number > 9) {
        printf("Out of range.\n");
        return (1);
    }

    max = round(pow(10, number)) - 1;
    regcomp(&re, "([0-9]).*\\1", 0);
    for (long index = 1; index <= max; index++) {
        int value;
        char str[10];
        sprintf(str, "%ld", index);
        value = regexec(&re, str, 0, NULL, 0);

        if (value == REG_NOMATCH) {
            printf("%ld\n", index);
        }
    }

    regfree(&re);
    return 0;
}

C++ Regular Expression

自 C++11 開始，C++ 標準函式庫提供了 Regular Expression library。

下面是 1-9 位數不重複印出來的練習問題：

#include <cmath>
#include <iostream>
#include <regex>
#include <string>

int main() {
    int number = 0;
    long max = 0;

    std::cout << "Please give a number: ";
    std::cin >> number;

    if (number < 1 || number > 9) {
        printf("Out of range.\n");
        return (1);
    }

    max = round(pow(10, number)) - 1;
    std::regex re("([0-9]).*\\1",  std::regex_constants::ECMAScript);
    for (long index = 1; index <= max; index++) {
        std::string s = std::to_string(index);

        std::smatch m;
        std::regex_search(s, m, re);

        if (m.empty()) {
            std::cout << index << std::endl;
        }
    }

    return 0;
}

GNU Make

GNU Make 是一個工具程式， 經由讀取 Makefile 或者是 makefile 的檔案（也可以使用 -f 指定），自動化建構軟體。 Makefile 是由很多相依性項目（dependencies）和規則（rules）所組成。

GNU Makefile 可以在各個程式語言使用，而不僅限於 C 或者是 C++。
不過因為通常是使用 C 或者是 C++，所以接下來使用 C 作為例子，考慮一個簡單的程式 hello.c：

void say(const char *name);

int main() {
    say("Orange");

    return 0;
}

以及 say procedure 的實作：

#include <stdio.h>

void say(const char *name) { printf("Hello, %s.\n", name); }

那麼在命令列編譯的指令如下：

gcc hello.c say.c -o hello

Makefile 的規則如下：

target [target ...]: [component ...]
	Tab ↹[command 1]
		.
		.
		.
	Tab ↹[command n]

Makefile 支援 Suffix rules，例子如下：

.SUFFIXES: .txt .html

# From .html to .txt
.html.txt:
    lynx -dump $<   >   $@

Makefile 支援 Pattern rules，例子如下：

# From %.html to %.txt
%.txt : %.html
    lynx -dump $< > $@

其中 $@ 或者是 $<, $^, $? 都是 Makefile 的巨集。$@ 代表工作目標的完整檔案名稱，$< 代表觸發建制目標的檔案名稱。 $^ 代表所有的依賴檔案，並以空格隔開這些檔名。$? 代表比目標還要新的依賴檔案列表。而 $* 代表工作目標的主檔名（也就是不包含副檔名）。

撰寫一個 Makefile 如下：

CC = /usr/bin/gcc
CFLAGS= -O2 -Wall
PROGRAM = hello
SRCS := $(wildcard *.c)
OBJS := $(patsubst %.c,%.o,$(SRCS))

RM = rm

all: $(PROGRAM)

$(PROGRAM): $(OBJS)
	$(CC) $(CFLAGS) $(OBJS) -o $@

%.o : %.c
	$(CC) $(CFLAGS) -c $< -o $@

clean:
	$(RM) $(PROGRAM) *.o

.PHONY: all clean