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
SQLSetEnvAttrandSQLAllocHandle. -
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
SQLSetConnectAttrandSQLGetConnectAttr. -
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
SQLSetStmtAttrandSQLGetStmtAttr. -
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;
}