SQLRETURN sr;
char szDSN[]="myData";
char szUID[]="sa";
char szAuthStr[]="";
char szSql[]="SELECT * FROM Table1";
SQLCHAR sName[NAME_LEN], sSno[SNO_LEN];
SQLINTEGER cbSno = SQL_NTS, cbName = SQL_NTS;
//Allocate Environment
//Step 2 初始化环境
SQLHANDLE hOdbcEnv;
sr=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hOdbcEnv);
if (sr!=SQL_SUCCESS)
TRACE("Error in Allocating Environment!/n");
sr=SQLSetEnvAttr(hOdbcEnv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
// Step 3 :建立连接
SQLHANDLE hDbConn;
sr = SQLAllocHandle(SQL_HANDLE_DBC, hOdbcEnv, &hDbConn);
sr=SQLSetConnectAttr(hDbConn,SQL_ATTR_LOGIN_TIMEOUT,(void *) 5,0);
sr = SQLConnect(hDbConn, (UCHAR *) szDSN,SQL_NTS,
(UCHAR *) szUID,SQL_NTS,
(UCHAR *) szAuthStr,SQL_NTS);
if (!SQL_SUCCEEDED(sr)) //连接失败时返回错误值
return -1;
// Step 4 :初始化语句句柄
SQLHSTMT sqlstmt; //语句句柄
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &sqlstmt);
// Step 5 :两种方式执行语句 *
//执行SQL语句,将当前行的列捆绑到一个固定的存储区中,
// 如果SQLFetch(sqlstmt)返回SQL_NO_DATA_FOUND,说明没有数据了
// 行捆绑
sr = SQLExecDirect(sqlstmt,(UCHAR *) szSql,SQL_NTS);
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)SQL_BIND_BY_COLUMN,SQL_IS_INTEGER );
sr = SQLBindCol(sqlstmt, 1, SQL_C_CHAR,sName, NAME_LEN, &cbName);
sr = SQLBindCol(sqlstmt, 2, SQL_C_CHAR,sSno,SNO_LEN, &cbSno);
CHAR str[100] ;
sr=SQLFetch(sqlstmt);
while ( sr!=SQL_NO_DATA_FOUND) {
sprintf(str,"Name:%s /tSNO:%s/n",sName,sSno);
cout << (LPCTSTR)str << endl;
sr=SQLFetch(sqlstmt);
}
SQLFreeStmt(sqlstmt,SQL_CLOSE);
// 一次取回多行, 行方式捆绑
struct rowTag{
SQLCHAR Name[50];
SDWORD NameLen;
SQLCHAR Sno[50];
SDWORD SnoLen;
} myRows[10];
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &sqlstmt);
sr = SQLExecDirect(sqlstmt,(UCHAR *) szSql,SQL_NTS);
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_ARRAY_SIZE,(void *)10,SQL_IS_INTEGER );
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_BIND_TYPE,(void *)sizeof(myRows[0]),SQL_IS_INTEGER );
sr = SQLBindCol(sqlstmt, 1, SQL_C_CHAR,&myRows[0].Name, sizeof(myRows[0].Name), &myRows[0].NameLen);
sr = SQLBindCol(sqlstmt, 2, SQL_C_CHAR,&myRows[0].Sno, sizeof(myRows[0].Sno), &myRows[0].SnoLen);
sr=SQLFetch(sqlstmt);
for(int j=0;j<10;j++) {
sprintf(str,"Name:%s /tSNO:%s/n",myRows[j].Name,myRows[j].Sno);
cout << (LPCTSTR)str << endl;
}
SQLFreeStmt(sqlstmt,SQL_CLOSE);
//更新数据
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &sqlstmt);
//set number of rows to fetch
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_ARRAY_SIZE,(void *)10,SQL_IS_INTEGER );
//set size of a row
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_BIND_TYPE,(void *)sizeof(myRows[0]),SQL_IS_INTEGER );
//set cursor type
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_CURSOR_TYPE,(void *)SQL_CURSOR_DYNAMIC,SQL_IS_INTEGER );
//set curosr concurrency
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_CONCURRENCY,(void *)SQL_CONCUR_LOCK,SQL_IS_INTEGER );
//Execute SQL to retrieve a result set
sr = SQLExecDirect(sqlstmt,(UCHAR *) szSql,SQL_NTS);
//Bind columns
sr = SQLBindCol(sqlstmt, 1, SQL_C_CHAR,&myRows[0].Name, sizeof(myRows[0].Name), &myRows[0].NameLen);
sr = SQLBindCol(sqlstmt, 2, SQL_C_CHAR,&myRows[0].Sno, sizeof(myRows[0].Sno), &myRows[0].SnoLen);
sr=SQLFetch(sqlstmt);
sprintf((char *)myRows[2].Name,"test");
myRows[2].NameLen=SQL_NTS;
sprintf((char *)myRows[2].Sno,"XH00000");
myRows[2].SnoLen=SQL_NTS;
sr=SQLSetPos(sqlstmt,3,SQL_UPDATE,SQL_LOCK_NO_CHANGE);
//使用SQLBulOperations插入数据
SQLFreeStmt(sqlstmt,SQL_CLOSE);
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &sqlstmt);
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_CURSOR_TYPE,(void *)SQL_CURSOR_DYNAMIC,SQL_IS_INTEGER );
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_CONCURRENCY,(void *)SQL_CONCUR_LOCK,SQL_IS_INTEGER );
sr = SQLExecDirect(sqlstmt,(UCHAR *) szSql,SQL_NTS);
sr = SQLBindCol(sqlstmt, 1, SQL_C_CHAR,&myRows[0].Name, sizeof(myRows[0].Name), &myRows[0].NameLen);
sr = SQLBindCol(sqlstmt, 2, SQL_C_CHAR,&myRows[0].Sno, sizeof(myRows[0].Sno), &myRows[0].SnoLen);
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_ARRAY_SIZE,(void *)10,SQL_IS_INTEGER );
sr = SQLSetStmtAttr(sqlstmt,SQL_ATTR_ROW_BIND_TYPE,(void *)sizeof(myRows[0]),SQL_IS_INTEGER );
for (int i=0;i<10;i++){
sprintf((char *)myRows[i].Name,"test:%d",i+100);
myRows[i].NameLen=SQL_NTS;
sprintf((char *)myRows[i].Sno,"test:%d",i+100);
myRows[i].SnoLen=SQL_NTS;
}
sr=SQLBulkOperations(sqlstmt,SQL_ADD);
//删除数据
SQLHSTMT hstmt;
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
SQLCHAR mysqltext[200]="delete from Table1 where sn='589'";
sr=SQLExecDirect(hstmt,mysqltext,SQL_NTS);
if (sr!=SQL_SUCCESS)
cout << "Error on Deletet /n" << endl;
else
cout << " Delete Data OK!/n" << endl;
//插入数据
/* SQLHSTMT hstmt;
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
SQLCHAR mysqltext[200]="insert into Table1 values('测试1','589')";
sr=SQLExecDirect(hstmt,mysqltext,SQL_NTS);
if (sr!=SQL_SUCCESS)
cout << (LPCTSTR)"Error on Insert /n" << endl;
else
cout << " Insert Data OK!/n" << endl;
*/
//使用参数插入数据
/*SQLCHAR paramsqltext[200]="insert into Table1 values(?,?)";
char Param1[20]="ce1";
char Param2[20]="ce2";
SQLINTEGER Len1,Len2;
Len1=SQL_NTS;
Len2=SQL_NTS;
sr=SQLPrepare(hstmt,paramsqltext,SQL_NTS);
sr=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,Param1,sizeof(Param1),&Len1);
sr=SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,20,0,Param2,sizeof(Param2),&Len2);
for ( i=1;i<6;i++){
sprintf(Param1,"测试名称%d",i+100);
sprintf(Param2,"序号%d",i+1000);
sr=SQLExecute(hstmt);
if (sr!=SQL_SUCCESS)
cout << (LPCTSTR)"Error on Insert /n" << endl;
else
cout << " Insert Data OK!/n" << endl;
}
*/
//Discount
sr=SQLDisconnect(hDbConn);
if(hDbConn!=SQL_NULL_HANDLE)
SQLFreeHandle(SQL_HANDLE_DBC,hDbConn);
if(hOdbcEnv!=SQL_NULL_HANDLE)
SQLFreeHandle(SQL_HANDLE_ENV,hOdbcEnv);