/* 数据库表结构说明 */
colno colname coltype
0 AP_ID char(12)
1 AP_KEY_C char(32) for bit data //对该字段有特殊的约束 二进制保存
2 AP_KEY_V char(32)
/******************a.c*********************/
#include<stdio.h>
#include<string.h>
#include<sqlcli1.h>
int main()
{
SQLHANDLE EnvHandle = 0;
SQLHANDLE ConHandle = 0;
SQLHANDLE StmtHandle = 0;
SQLRETURN RetCode = SQL_SUCCESS;
SQLCHAR SQLStmt[255];
SQLCHAR str1[16];
SQLCHAR str2[33];
SQLCHAR str3[33];
SQLLEN *len = 0;
SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&EnvHandle);
if(EnvHandle != 0)
SQLSetEnvAttr(EnvHandle,SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,SQL_IS_UINTEGER);
if(EnvHandle != 0)
SQLAllocHandle(SQL_HANDLE_DBC,EnvHandle,&ConHandle);
if(ConHandle != 0)
RetCode = SQLConnect(ConHandle,(SQLCHAR *) "<数据库名>",
SQL_NTS , (SQLCHAR *) "<用户名>",
SQL_NTS , (SQLCHAR *) "<密码>",
SQL_NTS);
if(ConHandle != 0 && RetCode == SQL_SUCCESS)
SQLAllocHandle(SQL_HANDLE_STMT,ConHandle,&StmtHandle);
/*############ SELECT BEGIN ###########*/
strcpy((char *) SQLStmt,"SELECT AP_KEY_C , AP_KEY_V from YANGDB.YANG_TEST where AP_ID = ?") ;
RetCode = SQLPrepare(StmtHandle,SQLStmt,SQL_NTS);
RetCode = SQLBindParameter(StmtHandle,1,SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,sizeof(str1),0,str1,sizeof(str1),NULL);//1:sql语句中第一个'?'参数, 0: 在数据库中属于第一列(从0开始)
strcpy((char *) str1,"3");
memset((char *) str2,0x00,sizeof(str2));
memset((char *) str3, 0x00,sizeof(str3));
RetCode = SQLExecute(StmtHandle);
if(RetCode == SQL_ERROR)
printf("sql exec error\n");
if(RetCode == SQL_SUCCESS)
{
SQLBindCol(StmtHandle,1,SQL_C_BINARY,(SQLPOINTER) str2,sizeof(str2),NULL);//1:select语句中返回字段的顺序 SQL_C_BINARY:因为在数据库中是2进制表示(for bit data决定以2进制存储) 若结果是"1234",使用SQL_C_CHAR则会变成"31323334" asci(31)为'1'
SQLBindCol(StmtHandle,2,SQL_C_CHAR,(SQLPOINTER) str3,sizeof(str3),NULL); //普通的char类型,没有其他约束
while (RetCode != SQL_NO_DATA) {
RetCode = SQLFetch(StmtHandle);
if(RetCode != SQL_NO_DATA)
printf("%s %s\n",str2,str3);
}
}
/*############ SELECT END ###########*/
/*############ INSERT BEGIN ###########*/
strcpy((char *) SQLStmt,"INSERT INTO YANGDB.YANG_TEST ( AP_ID,AP_KEY_C , AP_KEY_V) VALUES ( ? , ? , ? )") ;
RetCode = SQLPrepare(StmtHandle,SQLStmt,SQL_NTS);
/*绑定参数*/
RetCode = SQLBindParameter(StmtHandle,1,SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,sizeof(str1),0,str1,sizeof(str1),NULL);
RetCode = SQLBindParameter(StmtHandle,2,SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,sizeof(str2),1,str2,sizeof(str2),NULL);
RetCode = SQLBindParameter(StmtHandle,3,SQL_PARAM_INPUT,SQL_C_CHAR, SQL_CHAR,sizeof(str3),2,str3,sizeof(str3),NULL);
/*赋初值*/
memcpy(str1,"1234",4);
memcpy(str2,"1234",4);
memcpy(str3,"1234",4);
RetCode = SQLExecute(StmtHandle);
if(RetCode == SQL_ERROR)
printf("sql exec error\n");
/*############ INSERT END ###########*/
/*############ 断开连接 ###########*/
RetCode = SQLEndTran(SQL_HANDLE_DBC,ConHandle,SQL_COMMIT);
if(StmtHandle != 0)
SQLFreeHandle(SQL_HANDLE_STMT,StmtHandle);
if(ConHandle != 0)
RetCode = SQLDisconnect(ConHandle);
if(ConHandle != 0)
SQLFreeHandle(SQL_HANDLE_DBC,ConHandle);
if(EnvHandle != 0)
SQLFreeHandle(SQL_HANDLE_ENV,EnvHandle);
return 0;
}
编译: AIX环境 cc -L/app/usr/xxx/sqllib/lib32
-I/app/usr/xxx/sqllib/include/ -ldb2 a.c -o test
执行: ./test
SQLBindParameterSQLBindParameter (
SQLHSTMT StatementHandle, //语句句柄
SQLUSMALLINT ParameterNumber,//语句中的参数标记编号(从 1 开始顺序计数)
SQLSMALLINT ParamType,/*参数类型。以下类型之一:
SQL_PARAM_INPUT
SQL_PARAM_INPUT_OUTPUT
SQL_PARAM_OUTPUT*/
SQLSMALLINT CType,//C语言对应的数据类型的参数。
SQLSMALLINT SqlType,//SQL 数据类型的参数
SQLULEN ColDef, //列或参数标记表达式的大小
SQLSMALLINT Scale,//列或参数标记表达式的小数位数
SQLPOINTER rgbValue,//指向参数数据缓冲区的指针。
SQLLEN cbValueMax,//rgbValue 缓冲区的长度
SQLLEN * StrLen_or_Ind //指向参数长度缓冲区的指针
);