数组查询与单条查询编程的步骤差不多,只是在执行语句前要设置4个语句句柄属性,在绑定输出变量时使用变量数组,先看一看需要哪四个句柄属性。
第一个属性告诉语句使用列数组绑定,属性名称为SQL_ATTR_ROW_BIND_TYPE,属性值为SQL_BIND_BY_COLUMN,例如:
SQLSetStmtAttr(stmth, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
第二个属性告诉语句绑定的数组大小,就是绑定的行数,属性名称为SQL_ATTR_ROW_ARRAY_SIZE,属性值为10,例如:
SQLSetStmtAttr(stmth, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)10, 0);
第三个属性告诉语句返回每一行的状态,属性名称为SQL_ATTR_ROW_STATUS_PTR,属性值为一个SQLUSMALLINT类型的数组,比如叫status[10],例如:
SQLSetStmtAttr(stmth, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)status, 0);
第四个属性告诉语句返回每次从结果集中取回的数据条数,属性名称为SQL_ATTR_ROWS_FETCHED_PTR,属性值为一个SQLULEN类型的整数,不是数组,比如叫做num_fetched,例如:
SQLSetStmtAttr(stmth, SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER)&num_fetched, 0);
在使用SQLBindCol()函数绑定输出变量时,TargetValuePtr参数使用数组绑定,StrLen_or_IndPtr参数也使用数组绑定,例如绑定一个整数数组id[10],返回长度和指示变量数组len_id1[10],调用函数如下:
SQLBindCol(stmth, 1, SQL_C_ULONG, (SQLPOINTER)id, 0, len_ind1);
下面看一个完整的例子,还是从前面创建的表test_tab1中查询数据,使用数组操作。
#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include "sql.h"
#include "sqlext.h"
#include "sqltypes.h"
SQLHANDLE envh; /* env handle */
SQLHANDLE dbch; /* connect handle */
SQLHANDLE stmth; /* statement handle */
int main(int argc, char *argv[])
{
int i;
int conn = 0;
SQLRETURN rc;
SQLULEN num_fetched;
SQLUSMALLINT status[10];
SQLLEN len_ind1[10];
SQLLEN len_ind2[10];
SQLLEN len_ind3[10];
SQLLEN len_ind4[10];
SQLLEN len_ind5[10];
SQLINTEGER id[10];
char dsn_str[32];
char usrname[32];
char passwd[32];
char sqltxt[128];
char f1[10][32];
char f2[10][32];
char f3[10][32];
char f4[10][32];
if (argc < 3) {
fprintf(stderr, "usage: %s dsn username password\n", argv[0]);
return (-1);
}
strncpy(dsn_str, argv[1], 32);
dsn_str[31] = '\0';
strncpy(usrname, argv[2], 32);
usrname[31] = '\0';
strncpy(passwd, argv[3], 32);
passwd[31] = '\0';
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envh);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Allocate environment handle error.\n");
return (-1);
}
rc = SQLSetEnvAttr(envh, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set ODBC version error.\n");
goto free_exit;
}
rc = SQLAllocHandle(SQL_HANDLE_DBC, envh, &dbch);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Allocate DB connection handle error.\n");
goto free_exit;
}
rc = SQLSetConnectAttr(dbch, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set connection timeout value error.\n");
goto free_exit;
}
rc = SQLConnect(dbch, (SQLCHAR *)dsn_str, SQL_NTS, (SQLCHAR *)usrname, SQL_NTS,
(SQLCHAR *)passwd, SQL_NTS);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Connect to DB error.\n");
goto free_exit;
}
conn = 1;
fprintf(stdout, "connect DB ok ......\n");
rc = SQLAllocHandle(SQL_HANDLE_STMT, dbch, &stmth);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Allocate statment handle error.\n");
goto free_exit;
}
sprintf(sqltxt, "select id, f1, f2, f3, f4 from test_tab1");
rc = SQLPrepare(stmth, (SQLCHAR *)sqltxt, SQL_NTS);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Prepare statment error.\n");
goto free_exit;
}
/* 使用列数组绑定 */
rc = SQLSetStmtAttr(stmth, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set statment attribute error.\n");
goto free_exit;
}
/* 设置数组大小为10,每次取回10条数据 */
rc = SQLSetStmtAttr(stmth, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)10, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set statement attribute error.\n");
goto free_exit;
}
/* 设置返回每条数据的状态数组 */
rc = SQLSetStmtAttr(stmth, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)status, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set statement attribute error.\n");
goto free_exit;
}
/* 设置返回每次取回数据的条数 */
rc = SQLSetStmtAttr(stmth, SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER)&num_fetched, 0);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Set statement attribute error.\n");
goto free_exit;
}
/* 执行语句 */
rc = SQLExecute(stmth);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Execute statment error.\n");
goto free_exit;
}
/* 绑定第一列,id使用数组,len_ind1也使用数组 */
rc = SQLBindCol(stmth, 1, SQL_C_ULONG, (SQLPOINTER)id, 0, len_ind1);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Bind column 1 error.\n");
goto free_exit;
}
rc = SQLBindCol(stmth, 2, SQL_C_CHAR, (SQLPOINTER)f1, 32, len_ind2);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Bind column 2 error.\n");
goto free_exit;
}
rc = SQLBindCol(stmth, 3, SQL_C_CHAR, (SQLPOINTER)f2, 32, len_ind3);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Bind column 3 error.\n");
goto free_exit;
}
rc = SQLBindCol(stmth, 4, SQL_C_CHAR, (SQLPOINTER)f3, 32, len_ind4);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Bind column 4 error.\n");
goto free_exit;
}
rc = SQLBindCol(stmth, 5, SQL_C_CHAR, (SQLPOINTER)f4, 32, len_ind5);
if (rc != SQL_SUCCESS) {
fprintf(stderr, "Bind column 5 error.\n");
goto free_exit;
}
while (1) {
rc = SQLFetch(stmth);
if (rc == SQL_NO_DATA) {
fprintf(stderr, "No data in result set, break.\n");
break;
} else if (rc == SQL_ERROR) {
fprintf(stderr, "Fetch data error.\n");
goto free_exit;
}
fprintf(stdout, "%lu rows fetched.\n", num_fetched);
for (i=0; i<num_fetched; i++) {
fprintf(stdout, "id=%d, f1=%s, f2=%s, f3=%s, f4=%s\n",
id[i], f1[i], f2[i], f3[i], f4[i]);
}
}
SQLFreeHandle(SQL_HANDLE_STMT, stmth);
SQLDisconnect(dbch);
SQLFreeHandle(SQL_HANDLE_DBC, dbch);
SQLFreeHandle(SQL_HANDLE_ENV, envh);
return (0);
free_exit:
if (stmth != NULL) {
SQLFreeHandle(SQL_HANDLE_STMT, stmth);
}
if (conn) {
SQLDisconnect(dbch);
}
if (dbch != NULL) {
SQLFreeHandle(SQL_HANDLE_DBC, dbch);
}
if (envh != NULL) {
SQLFreeHandle(SQL_HANDLE_ENV, envh);
}
return (-1);
}
访问www.tomcoding.com网站,可以下载Oracle内部数据结构的文档,还有DUL,exp/imp,logminer及ASM工具的源代码。