SQLSetStmtAttr
函数定义:
Stmt是用来执行SQL语句的句柄,这个函数是用来设置她的属性的
SQLRETURN SQLSetStmtAttr(
SQLHSTMT , 这是由游标是完全的 keyset-driven游标. 如果大于 0, 那么就是混合的动态游标
SQL_ATTR_MAX_LENGTH
(ODBC 1.0)
指定驱动返回数据数量的最大值
SQL_ATTR_MAX_ROWS
(ODBC 1.0)
指定驱动返回的最大行数
SQL_ATTR_METADATA_ID
(ODBC 3.0)
确定catalog函数如何处理字符串变量.
If SQL_TRUE标识符
If SQL_FALSE非标识符
SQL_ATTR_NOSCAN
(ODBC 1.0)
驱动是否扫描字符串的转义序列:
SQL_NOSCAN_OFF 默认值,扫描
SQL_NOSCAN_ON 不扫描,直接向数据源发送请求
For more information, see Escape Sequences in ODBC.
SQL_ATTR_PARAM_BIND_OFFSET_PTR
(ODBC 3.0)
添加动态参数绑定指针,默认为null
SQL_ATTR_PARAM_BIND_TYPE
(ODBC 3.0)
动态参数的绑定方向
SQL_PARAM_BIND_BY_COLUMN 默认值,以列为方向
SQL_ATTR_PARAM_OPERATION_PTR
(ODBC 3.0)
在SQL statement执行时,忽略参数SQL_PARAM_PROCEED 参数不忽略 SQL_PARAM_IGNORE参数忽略
SQL_ATTR_PARAM_STATUS_PTR
(ODBC 3.0)
执行SQLExecute 或 SQLExecDirect后,一个指向每行参数的状态信息数组的指针.
只有当 PARAMSET_SIZE 大于 1 才可以使用.
取值:
SQL_PARAM_SUCCESS: 参数成功执行
SQL_PARAM_SUCCESS_WITH_INFO: 成功但是在数据结构的诊断中可能出错
SQL_PARAM_ERROR: 执行过程中出错
SQL_PARAM_UNUSED: 之前的参数可能造车此次之行参数被忽略而未使用 SQL_PARAM_IGNORE被 SQL_ATTR_PARAM_OPERATION_PTR制定的参数忽略
SQL_PARAM_DIAG_UNAVAILABLE: 驱动把参数集当做完整的一个单元,并不会产生错误信息的等级
For more information, see Using Arrays of Parameters.
SQL_ATTR_PARAMS_PROCESSED_PTR
(ODBC 3.0)
指向一个数组的指针用来记录执行过的参数集,包括执行错误的
For more information, see Using Arrays of Parameters.
SQL_ATTR_PARAMSET_SIZE
(ODBC 3.0)
指定每个参数的取值个数. If SQL_ATTR_PARAMSET_SIZE大于1, SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR of the APD 指向数组.
For more information, see Using Arrays of Parameters.
SQL_ATTR_QUERY_TIMEOUT
(ODBC 1.0)
在执行结果返回应用程序之前的等待时间,单位秒. 如果 ValuePtr为 0 (默认), 立即返回.
SQL_ATTR_RETRIEVE_DATA
(ODBC 2.0)
An SQLUINTEGER value:
SQL_RD_ON = SQLFetchScroll and, in ODBC 3.x, 当游标的位置到了指定的地方时SQLFetch 取回数据默认值
SQL_RD_OFF = SQLFetchScroll and, in ODBC 3.x, SQLFetch 不取回.
SQL_ATTR_ROW_ARRAY_SIZE
(ODBC 3.0)
当执行 SQLFetch or SQLFetchScroll和SQLBulkOperations返回行数.默认值是 1.
SQL_ATTR_ROW_BIND_OFFSET_PTR
(ODBC 3.0)
替换列绑定指针,非空
SQL_ATTR_ROW_BIND_TYPE
(ODBC 1.0)
当 SQLFetch , SQLFetchScroll 被使用时,确定绑定方向.
列方向绑定用 SQL_BIND_BY_COLUMN.
For more information, see Binding Columns for Use with Block Cursors.
SQL_ATTR_ROW_NUMBER
(ODBC 2.0)
当前结果集中的行数,如果不能确定或者没有驱动返回 0.
SQL_ATTR_ROW_OPERATION_PTR
(ODBC 3.0)
当bulk操作时,忽略行 SQL_ROW_PROCEED 行包括在bulk操作中
SQL_ROW_IGNORE 行被排除在bulk操作外
For more information, see Updating Rows in the Rowset with SQLSetPos andDeleting Rows in the Rowset with SQLSetPos.
SQL_ATTR_ROW_STATUS_PTR
(ODBC 3.0)
当 SQLFetch 或 SQLFetchScroll时行状态描述数组.
SQL_ATTR_ROWS_FETCHED_PTR
(ODBC 3.0)
SQLFetch 或 SQLFetchScroll后行数
SQL_ATTR_SIMULATE_CURSOR
(ODBC 2.0)
模拟位置更新删除操作只影响一行。
SQL_SC_NON_UNIQUE = 可能影响多行
SQL_SC_TRY_UNIQUE = 尽量影响一行
SQL_SC_UNIQUE = 只影响一行
SQL_ATTR_USE_BOOKMARKS
(ODBC 2.0)
是否用带bookmarks的游标:
SQL_UB_OFF = Off (the default)
SQL_UB_VARIABLE = 用,如果支持还会提供变长的bookmarks.
SQL_UB_FIXED 总是用变长的bookmarks
如果要使用带bookmarks的游标,需要先指定SQL_UB_VARIABLE 的属性再打开游标.
For more information, see Retrieving Bookmarks.
StringLength :输入变量
如果ValuePtr 为字符串,填入字符串长度或者SQL_NTS.
如果是二进制缓存填入 SQL_LEN_BINARY_ATTR(length) 的结果If ValuePtr is a pointer to a不是上述两个填入SQL_IS_POINTER.
如果是边长的数组根据情况填入SQL_IS_INTEGER 或者SQL_IS_UINTEGER
返回值:
返回值有四种:SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR.
查看详细错误信息可调用SQLGetDiagRec 函数(之后章节讲解)。
用法:
实在太多无法一一举例,暂且举部分
char sql2[1000]={0};
char str_rs_out[30]={0};
SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHSTMT hstmt = NULL;
SQLHDESC hdesc=NULL;
int arm_rs;
SQLPOINTER ptr= (char* )malloc(20);
SQLCHAR err_msg[512]={0};
SQLCHAR sqlstat[10]={0};
SQLINTEGER no_er=0;
rs = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
rs = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
rs = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
rs = SQLConnect(hdbc,(UCHAR*)szDSN,SQL_NTS,(UCHAR*)szUID ,SQL_NTS,(UCHAR*)szAuthStr,SQL_NTS);
rs = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
1. 设置语句句柄属性游标滚动
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_SCROLLABLE,SQL_NONSCROLLABLE,SQL_NTS);
2. 设置语句句柄属性游标滚动(错误示例)
rs =SQLSetStmtAttr(NULL,SQL_ATTR_CURSOR_SCROLLABLE,(SQLPOINTER)SQL_SCROLLABLE,SQL_NTS);
3. 设置语句句柄属性游标滚动
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_SCROLLABLE,(SQLPOINTER)SQL_SCROLLABLE,SQL_NTS);
4. 设置语句句柄属性游标滚动IPD
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_AUTO_IPD,(SQLPOINTER)SQL_TRUE,0);
rs1=SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,sqlstat,&no_er,err_msg,512,&len);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_AUTO_IPD,(SQLPOINTER)SQL_FALSE,0);
5. 设置语句句柄属性 SQL_ATTR_PARAM_STATUS_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_STATUS_PTR,(SQLPOINTER)NULL,SQL_NTS);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_STATUS_PTR,(SQLPOINTER)ptr,SQL_NTS);
int r1,r2,r3,r4;
r1= SQL_PARAM_SUCCESS;
r2=SQL_PARAM_SUCCESS_WITH_INFO;
r3=SQL_PARAM_ERROR;
r4= SQL_PARAM_UNUSED;
r1=SQL_PARAM_DIAG_UNAVAILABLE;
6. 设置语句句柄属性 PARAMS_PROCESSED_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMS_PROCESSED_PTR,(SQLPOINTER)ptr,SQL_NTS);//指向一个buffer 用以返回 参数集(sets of parameters)中被处理过的参数的数量
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAMS_PROCESSED_PTR,(SQLPOINTER)ptr,100,&rp);
7. 设置语句句柄属性 SQL_ATTR_PARAM_BIND_OFFSET_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_BIND_OFFSET_PTR,(SQLPOINTER)ptr,SQL_NTS);//about parameter binding : two parts 2.0不支持
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAM_BIND_OFFSET_PTR,(SQLPOINTER)ptr,100,&rp);
8. 设置语句句柄属性 SQL_ATTR_PARAM_BIND_TYPE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_BIND_TYPE,(SQLPOINTER)SQL_PARAM_BIND_BY_COLUMN,SQL_NTS);//the default value
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAM_BIND_TYPE,(SQLPOINTER)ptr,100,&rp);
9. 设置语句句柄属性 SQL_ATTR_PARAMSET_SIZE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER)1,SQL_NTS);//一个参数的值的个数,大于1 的就是描述的内容了
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER)ptr,100,&rp);
10. 设置语句句柄属性 SQL_ATTR_ROW_ARRAY_SIZE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)ptr,100,&rp);
11. 设置语句句柄属性 SQL_ATTR_ROW_NUMBER
char sql[1000]={0};
sprintf(sql,"select a,b,c fromd where item='%s' ","SQL");
rs =SQLExecDirect(hstmt,(UCHAR*)sql,strlen(sql));
SQLLEN cbLen1 ;
char ci1[100]={0};
SQLBindCol(hstmt, 1, SQL_C_CHAR, ci1, 100, &cbLen1);
rs=SQLFetch(hstmt);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_NUMBER,(SQLPOINTER)ptr,100,&rp);
memset(ptr,0x0,100);
rs1=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_OPERATION_PTR,(SQLPOINTER)ptr,100,&rp);
memset(ptr,0x0,100);
rs1=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_STATUS_PTR,(SQLPOINTER)ptr,100,&rp);
memset(ptr,0x0,100);
rs1=SQLGetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,(SQLPOINTER)ptr,100,&rp);
12. 设置语句句柄属性 SQL_ATTR_ROW_OPERATION_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_OPERATION_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_OPERATION_PTR,(SQLPOINTER)ptr,100,&rp);
13. 设置语句句柄属性 SQL_ATTR_ROW_STATUS_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_STATUS_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_STATUS_PTR,(SQLPOINTER)ptr,100,&rp);
14. 设置语句句柄属性 SQL_ATTR_ROWS_FETCHED_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,(SQLPOINTER)ptr,100,&rp);
15. 设置语句句柄属性 SQL_ATTR_SIMULATE_CURSOR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_SIMULATE_CURSOR,(SQLPOINTER)4,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_SIMULATE_CURSOR,(SQLPOINTER)ptr,100,&rp);
16. 设置语句句柄属性 SQL_ATTR_ASYNC_ENABLE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ASYNC_ENABLE,(SQLPOINTER)SQL_ASYNC_ENABLE_OFF,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ASYNC_ENABLE,(SQLPOINTER)ptr,100,&rp);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ASYNC_ENABLE,(SQLPOINTER)SQL_ASYNC_ENABLE_ON,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ASYNC_ENABLE,(SQLPOINTER)ptr,100,&rp);
17. 设置语句句柄属性 SQL_ATTR_CURSOR_SENSITIVITY
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_SENSITIVITY,(SQLPOINTER)SQL_UNSPECIFIED,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_CURSOR_SENSITIVITY,(SQLPOINTER)ptr,100,&rp);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_SENSITIVITY,(SQLPOINTER)1,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_CURSOR_SENSITIVITY,(SQLPOINTER)ptr,100,&rp);
18. 设置语句句柄属性 SQL_ATTR_CURSOR_TYPE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN,0);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)SQL_CURSOR_STATIC,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)ptr,100,&rp);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)SQL_CURSOR_FORWARD_ONLY,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,(SQLPOINTER)ptr,100,&rp);
19. 设置语句句柄属性 SQL_ATTR_MAX_LENGTH
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_MAX_LENGTH,(SQLPOINTER)128,0);//uint
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_MAX_LENGTH,(SQLPOINTER)ptr,100,&rp);
20. 设置语句句柄属性 SQL_ATTR_MAX_ROWS
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_MAX_ROWS,(SQLPOINTER)1280000,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_MAX_ROWS,(SQLPOINTER)ptr,100,&rp);
21. 设置语句句柄属性 SQL_ATTR_METADATA_ID
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)SQL_TRUE,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)ptr,100,&rp);
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)SQL_FALSE,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)ptr,100,&rp);
22. 设置语句句柄属性 SQL_ATTR_RETRIEVE_DATA
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_RETRIEVE_DATA,(SQLPOINTER)SQL_TRUE,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_RETRIEVE_DATA,(SQLPOINTER)ptr,100,&rp);
23. 设置语句句柄属性 SQL_ATTR_ROW_BIND_TYPE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)SQL_BIND_BY_COLUMN,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)ptr,100,&rp);
24. 设置语句句柄属性 SQL_ATTR_ROW_BIND_OFFSET_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_BIND_OFFSET_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_BIND_OFFSET_PTR,(SQLPOINTER)ptr,100,&rp);
25. 设置语句句柄属性 SQL_ATTR_QUERY_TIMEOUT
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER)3600,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER)ptr,100,&rp);
26. 设置语句句柄属性 SQL_ATTR_KEYSET_SIZE
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_KEYSET_SIZE,(SQLPOINTER)3600,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_KEYSET_SIZE,(SQLPOINTER)ptr,100,&rp);
27. 设置语句句柄属性 SQL_ATTR_NOSCAN
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_NOSCAN,(SQLPOINTER)1,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_NOSCAN,(SQLPOINTER)ptr,100,&rp);
28. 设置语句句柄属性 SQL_ATTR_ROW_OPERATION_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_OPERATION_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_ROW_OPERATION_PTR,(SQLPOINTER)ptr,100,&rp);
29. 设置语句句柄属性 SQL_ATTR_CONCURRENCY
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY,(SQLPOINTER)ptr,100,&rp);
30. 设置语句句柄属性 SQL_ATTR_FETCH_BOOKMARK_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_FETCH_BOOKMARK_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_FETCH_BOOKMARK_PTR,(SQLPOINTER)ptr,100,&rp);
31. 设置语句句柄属性 SQL_ATTR_USE_BOOKMARKS
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_USE_BOOKMARKS,(SQLPOINTER)1,0);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_USE_BOOKMARKS,(SQLPOINTER)ptr,100,&rp);
32. 设置语句句柄属性 SQL_ATTR_PARAM_OPERATION_PTR
rs =SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_OPERATION_PTR,(SQLPOINTER)ptr,SQL_NTS);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAM_OPERATION_PTR,(SQLPOINTER)ptr,100,&rp);
33. 设置语句句柄属性 SQL_ATTR_IMP_ROW_DESC
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_APP_ROW_DESC,(SQLPOINTER)&hdesc,4,&rp);
34. 设置语句句柄属性 SQL_ATTR_IMP_PARAM_DESC
sprintf(sql2,"insert into t1 values(?)");
rs1=SQLPrepare(hstmt,(SQLCHAR*)sql2,SQL_NTS);
clen=4;
rs1=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,10,0,&numattr_value,10,&clen);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_APP_PARAM_DESC,(SQLPOINTER)&hdesc,4,&rp);
35. 设置语句句柄属性 SQL_ATTR_APP_ROW_DESC
sprintf(sql2,"select * from t1;");
rs1 =SQLExecDirect(hstmt,(UCHAR*)sql2,strlen(sql2));
SQLLEN cbLen1 ;
char ci1[100]={0};
SQLBindCol(hstmt, 1, SQL_C_CHAR, ci1, 100, &cbLen1);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_ROW_DESC,(SQLPOINTER)&hdesc,4,&rp);
rs1++;
36. 设置语句句柄属性 SQL_ATTR_APP_PARAM_DESC
sprintf(sql2,"insert into t1 values(?)");
rs1=SQLPrepare(hstmt,(SQLCHAR*)sql2,SQL_NTS);
clen=4;
rs1=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,10,0,&numattr_value,10,&clen);
rs=SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_PARAM_DESC,(SQLPOINTER)&hdesc,100,&rp);
rs1++;