-
开发过程
在正确设置了KingbaseES ODBC 数据源以后，应用程序使用 KingbaseES ODBC API 的步骤如下： -
调用 SQLAllocHandle 函数分配环境句柄。
-
调用 SQLAllocHandle 函数分配连接句柄。
-
利用分配后的连接句柄调用 SQLConnect，SQLBrowseConnect 或者 SQLDriverConnect 连接到数据源。它指定数据源名以及完成连接所需的任何附加信息。
-
处理一个或多个 SQL 语句：
应用程序将 SQL 文本串放置于缓冲区中。
如果语句包括参数标记，则将它设置参数值。
如果语句返回结果集，应用程序则为语句赋值游标名或者使用驱动程序缺省的游标名。应用程序为“SQLPrepare 或 SQLExecute”提交语句。
如果语句创建了结果集，应用程序则询问该结果集属性，例如列数，以及特定列的名称、类型等。它为结果集中的每一列分配存储并取回结果。
如果语句引起错误，应用程序则从驱动程序中提取错误信息并采取相应行动。 -
通过提交或重新操作来结束每一个事务。
-
当应用程序与数据源之间的相互作用结束时，终止连接，释放相应的句柄。函数执行序列如下：
i. SQLAllocHandle（SQL_HANDLE_ENV，NULL，&henv）<=> SQLAllocEnv
ii. SQLAllocHandle（SQL_HANDLE_DBC，henv，&hdbc）<=> SQLAllocConnect
iii. SQLConnect
iv. SQLAllocHandle（SQL_HANDLE_STMT，hdbc，&hstmt）<=> SQLAllocStmt
v. 处理 SQL 语句(SQLPrepare 此步可以省略但在执行中必须调用 SQLExecDirect)
vi. 如果有绑定参数则进行绑定赋值（SQLBindParameter）
vii. 可以通过 SQLGetDescField, SQLColAttribute, SQLDescribeCol 等函数来获取信息（可以省略）
viii. 通过 SQLExecute 或 SQLExecDirect 执行函数
ix. 获取结果(SQLFetch, SQLBindCol, SQLGetData…)
x. SQLFreeHandle（SQL_HANDLE_STMT，hstmt）<=> SQLFreeStmt
xi. SQLDisconnect
xii. SQLFreeHandle（SQL_HANDLE_DBC，hdbc）<=> SQLFreeConnect
xiii. SQLFreeHandle（SQL_HANDLE_ENV，henv）<=> SQLFreeEnv -
用例
#define CHECK_STMT_RESULT(rc, msg, hstmt)
if (!SQL_SUCCEEDED(rc))
{
print_diag(msg, SQL_HANDLE_STMT, hstmt);
exit(1);
}#define CHECK_CONN_RESULT(rc, msg, hconn)
if (!SQL_SUCCEEDED(rc))
{
print_diag(msg, SQL_HANDLE_DBC, hconn);
exit(1);
}void
test_connect_ext(char *extraparams)
{
SQLRETURN ret;
SQLCHAR str[1024];
SQLSMALLINT strl;
char dsn[1024];
const char * const test_dsn = get_test_dsn();
char *envvar;if ((envvar = getenv(“COMMON_CONNECTION_STRING_FOR_REGRESSION_TEST”)) != NULL && envvar[0] != ‘\0’)
{
if (NULL == extraparams)
snprintf(dsn, sizeof(dsn), “DSN=%s;%s”, test_dsn, envvar);
else
snprintf(dsn, sizeof(dsn), “DSN=%s;%s;%s;%s”,
test_dsn, extraparams, envvar, extraparams);
}
else
snprintf(dsn, sizeof(dsn), “DSN=%s;%s”,
test_dsn, extraparams ? extraparams : “”);SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);//SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
SQLAllocConnect(env, &conn);ret = SQLDriverConnect(conn, NULL, (SQLCHAR*)dsn, SQL_NTS,
str, sizeof(str), &strl, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmte);
printf(“connected\n”);
}
else {
print_diag(“SQLDriverConnect failed.”, SQL_HANDLE_DBC, conn);
exit(1);
}
}int Batch_inset_all_sucess()
{
SQLRETURN rc;
HSTMT hstmt = SQL_NULL_HSTMT;
char *sql;int i;
SQLUINTEGER int_array[ARRAY_SIZE];
char *param1_blob;
char *param1_clob;
char *param1_float;
char *param1_timestamp;
char *param1_char;
char *param1_varchar;
SQLLEN nCount = 0;SQLLEN int_ind_array[ARRAY_SIZE];
SQLLEN str_ind_array[ARRAY_SIZE];
SQLUSMALLINT status_array[ARRAY_SIZE];
SQLULEN nprocessed;struct timeval start, end;
long mtime, seconds, useconds;test_connect_ext(“BatchInsertSize_Ext=1”);
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag(“failed to allocate stmt handle”, SQL_HANDLE_DBC, conn);
exit(1);
}rc = SQLExecDirect(hstmt, (SQLCHAR *)“drop table if exists tmptable”, SQL_NTS);
CHECK_STMT_RESULT(rc, “SQLExecDirect failed while creating temp table”, hstmt);sql = “CREATE TABLE tmptable (c1 int4 primary key, c2 varchar, c3 char(2), c4 NUMBER(4,2), c5 NUMBER(4, 2), c6 timestamp, c7 blob, c8 bytea, c9 char(4), c10 char(4), c11 char(4), c12 char(4), c13 char(4))”;
rc = SQLExecDirect(hstmt, (SQLCHAR *)sql, SQL_NTS);
CHECK_STMT_RESULT(rc, “SQLExecDirect failed while creating temp table”, hstmt);rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, “SQLFreeStmt failed”, hstmt);int nVarcharLength = 0;
int nCharLength = 0;
int nLength_time = 0;
int nLength_lob = 0;param1_blob = (char *)malloc(ARRAY_SIZE * sizeof(char) * 4097);
memset(param1_blob, 0, ARRAY_SIZE * sizeof(char) * 4097);param1_clob = (char *)malloc(ARRAY_SIZE * sizeof(char) * 5);
memset(param1_clob, 0, ARRAY_SIZE * sizeof(char) * 5);param1_float = (char*)malloc(ARRAY_SIZE * 5);
memset(param1_float, 0, ARRAY_SIZE * 5 * sizeof(char));param1_timestamp = (char*)malloc(ARRAY_SIZE * 21 * sizeof(char));
memset(param1_timestamp, 0, ARRAY_SIZE * 21 * sizeof(char));param1_varchar = (char*)malloc(ARRAY_SIZE * 5 * sizeof(char));
memset(param1_varchar, 0, ARRAY_SIZE * 5 * sizeof(char));param1_char = (char*)malloc(ARRAY_SIZE * 3 * sizeof(char));
memset(param1_char, 0, ARRAY_SIZE * 3 * sizeof(char));for (i = 0; i < ARRAY_SIZE; i++)
{int_array[i] = i + 1; int_ind_array[i] = 0; char *str_array2 = (char *)malloc(5); char *str_array3 = (char *)malloc(4097); char *str_array4 = (char *)malloc(5); char a = 'a' + i % 26; memset(str_array2, 0, 5); memset(str_array2, 'a', 4); memset(str_array3, 0, 4097); memset(str_array3, 'a', 4096); memset(str_array4, 0, 5); memset(str_array4, 'b', 4); str_ind_array[i] = SQL_NTS; strncpy(param1_varchar + nVarcharLength, (const char *)str_array2, 5); strncpy(param1_blob + nLength_lob, (const char *)str_array3, 4097); strncpy(param1_clob + nVarcharLength, (const char *)str_array4, 5); strncpy(param1_float + nVarcharLength, "3.40", 4); strncpy(param1_timestamp + nLength_time, "2017-02-23 11:34:46", 20); strncpy(param1_char + nCharLength, "cc", 2); nVarcharLength += 5; nCharLength += 3; nLength_lob += 4097; nLength_time += 21;}
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, status_array, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &nprocessed, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)ARRAY_SIZE, 0);SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
int_array, 0, int_ind_array);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_char, 3, str_ind_array);
SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_FLOAT, 20, 0,
param1_float, 0, str_ind_array);
SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_FLOAT, 20, 0,
param1_float, 0, str_ind_array);
SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TIMESTAMP, 20, 0,
param1_timestamp, 21, str_ind_array);
SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0,
param1_blob, 4097, str_ind_array);
SQLBindParameter(hstmt, 8, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0,
param1_clob, 5, str_ind_array);
SQLBindParameter(hstmt, 9, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);
SQLBindParameter(hstmt, 10, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);
SQLBindParameter(hstmt, 11, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);
SQLBindParameter(hstmt, 12, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);
SQLBindParameter(hstmt, 13, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
param1_varchar, 5, str_ind_array);sql = “insert INTO tmptable VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”;
rc = SQLPrepare(hstmt, (SQLCHAR*)sql, SQL_NTS);
rc = SQLExecute(hstmt);if (rc < 0)
return nCount;CHECK_STMT_RESULT(rc, “SQLExecDirect failed”, hstmt);
free(param1_blob);
free(param1_float);
free(param1_timestamp);
free(param1_char);
free(param1_varchar);rc = SQLRowCount(hstmt, &nCount);
test_disconnect();
return nCount;
}void DML_Insert_test()
{
int nCount = Batch_inset_all_sucess(1);
if (nCount == ARRAY_SIZE)
{
printf(“Test DML insert data all success\r\n”);
}
}
参考
《ODBC指南》，
491
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
