使用ODBC连接数据库[C++]

来源：互联网发布：出国申请时间表知乎编辑：程序博客网时间：2024/04/28 17:10

使用ODBC连接数据库

walrus 发表于 2006-6-21 22:05:00

  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);