odbcsampleview.cpp

用VC++,ODBC的API函数访问数据库

// ODBCSAMPLEView.cpp : implementation of the CODBCSAMPLEView class
//

#include "stdafx.h"
#include "ODBCSAMPLE.h"

#include "ODBCSAMPLEDoc.h"
#include "ODBCSAMPLEView.h"

#include "TableDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

#define MAXBUFLEN   255

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView

IMPLEMENT_DYNCREATE(CODBCSAMPLEView, CListView)

BEGIN_MESSAGE_MAP(CODBCSAMPLEView, CListView)
	//{{AFX_MSG_MAP(CODBCSAMPLEView)
	ON_COMMAND(ID_MENUITEM32771, OnLogin)
	ON_COMMAND(ID_EXECUTE, OnExecute)
	ON_COMMAND(ID_MYTEST, OnSqlBrowserConn)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CListView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CListView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CListView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView construction/destruction


CODBCSAMPLEView::CODBCSAMPLEView()
{
	  m_henv1 = SQL_NULL_HENV;
    m_hdbc1 = SQL_NULL_HDBC;
	  m_bConn = 0;
}

CODBCSAMPLEView::~CODBCSAMPLEView()
{
	if(m_hdbc1) 
	{   
		SQLDisconnect(m_hdbc1);		
		SQLFreeHandle(SQL_HANDLE_DBC,m_hdbc1);
    	m_hdbc1=NULL;
	}
	if(m_henv1) 
	{   SQLFreeHandle(SQL_HANDLE_ENV,m_henv1);
	    m_henv1=NULL;
	}
}

BOOL CODBCSAMPLEView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs

	return CListView::PreCreateWindow(cs);
}

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView drawing

void CODBCSAMPLEView::OnDraw(CDC* pDC)
{
	CODBCSAMPLEDoc* pDoc = GetDocument();
	ASSERT_VALID(pDoc);
	// TODO: add draw code for native data here
}

void CODBCSAMPLEView::OnInitialUpdate()
{
	CListView::OnInitialUpdate();

	CRect		rect;

	m_pCtrList = &GetListCtrl( );
    m_pCtrList->GetWindowRect(&rect);

	
	m_pCtrList->ModifyStyle(0,LVS_REPORT ,0);
	m_pCtrList->SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES|LVS_EX_ONECLICKACTIVATE|LVS_EX_UNDERLINEHOT);

	m_pCtrList->InsertColumn(0,"第一列",LVCFMT_CENTER,rect.Width()/5,-1 );
	m_pCtrList->InsertColumn(1,"第二列",LVCFMT_CENTER,rect.Width()/5,-1 );
	m_pCtrList->InsertColumn(2,"第三列",LVCFMT_CENTER,rect.Width()/4,-1 );
	m_pCtrList->InsertColumn(3,"第四列",LVCFMT_CENTER,2*rect.Width()/5,-1 );
}

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView printing

BOOL CODBCSAMPLEView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CODBCSAMPLEView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add extra initialization before printing
}

void CODBCSAMPLEView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add cleanup after printing
}

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView diagnostics

#ifdef _DEBUG
void CODBCSAMPLEView::AssertValid() const
{
	CListView::AssertValid();
}

void CODBCSAMPLEView::Dump(CDumpContext& dc) const
{
	CListView::Dump(dc);
}

CODBCSAMPLEDoc* CODBCSAMPLEView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CODBCSAMPLEDoc)));
	return (CODBCSAMPLEDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CODBCSAMPLEView message handlers

void CODBCSAMPLEView::OnLogin() 
{
	if(m_bConn == 1)
	{
		return;
	}

	SQLHSTMT     hstmt1 = SQL_NULL_HSTMT;

	SQLCHAR			ConnStrOut[MAXBUFLEN];
	SQLSMALLINT		cbConnStrOut = 0;
	RETCODE			retcode;

	retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &m_henv1);

	// Notify ODBC that this is an ODBC 3.0 application.
	retcode = SQLSetEnvAttr(m_henv1, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER)SQL_OV_ODBC3,
                     SQL_IS_INTEGER);

	// Allocate an ODBC connection handle and connect.
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv1, &m_hdbc1);

	// Make connection without data source. Ask that driver not
	// prompt if insufficient information. Driver returns
	// SQL_ERROR and application prompts user
	// for missing information. Window handle not needed for
	// SQL_DRIVER_NOPROMPT.

	//retcode = SQLConnect(m_hdbc, (SQLCHAR*) DSN, SQL_NTS, (SQLCHAR*) UI, SQL_NTS, (SQLCHAR*) PSW, SQL_NTS);
	

	retcode = SQLDriverConnect(m_hdbc1,		// Connection handle
				          m_hWnd,		      	// Window handle
                  NULL ,				  	// Input connect string
                  SQL_NTS,					// Null-terminated string
                  ConnStrOut,				// Address of output buffer
                  MAXBUFLEN,				// Size of output buffer
                  &cbConnStrOut,		// Address of output length
                  SQL_DRIVER_PROMPT);

	if(retcode == SQL_SUCCESS ||retcode == SQL_SUCCESS_WITH_INFO)
	{	
		AfxMessageBox("与数据源连接成功");
		m_bConn = 1;
	}

    if(hstmt1)
	{	SQLFreeHandle(SQL_HANDLE_STMT,hstmt1);
    	hstmt1=NULL;
	}	

}

void CODBCSAMPLEView::GetError(SQLHSTMT hstmt)
{
  unsigned char	sqlstate[32];
	char * MessageText = new char[800];
	CString			temp;
	int	            RecNumber;
	long            NativeErrorPtr;
	short           TextLengthPtr;
	SQLRETURN		rc;
	memset(sqlstate,'\0',sizeof(sqlstate));
	memset(MessageText,'\0',800);
	NativeErrorPtr = 0;
	TextLengthPtr = 0;
	RecNumber = 1;


	rc = SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,RecNumber,sqlstate,&NativeErrorPtr,(unsigned char*)MessageText,800,&TextLengthPtr);
	temp = MessageText;

	::MessageBox(NULL,temp,_T("提示信息"),MB_OK);
	delete[] MessageText;
}

void CODBCSAMPLEView::OnExecute() 
{
	CString			strSQL;
	SQLRETURN		rc;
	SDWORD			cb;

	char					szCon1[128]="";
	char					szCon2[128]="";
	char					szCon3[128]="";
  char					szCon4[128]="";

	int                     iSubItem;//lsitCtrl子项数
	int                     iActualItem;//listCtrl项数
	LV_ITEM                 lvitem; //listCtrl标项结构
	lvitem.pszText=         new char[128];

	SQLHSTMT     hstmt = SQL_NULL_HSTMT;

	m_pCtrList = &GetListCtrl( );

	if(m_bConn == 0)
	{
		AfxMessageBox("请建立连接先!");
		return;
	}

//--------------------------------------------
	SQLINTEGER	 cbImageParam,cbC = 0;
	SQLPOINTER	 pToken;
	SQLUINTEGER  sC;
	long	cb1[2];
	cb1[0] = 0;
	cb1[1] = SQL_LEN_DATA_AT_EXEC(0);
	char buf[3];
	strcpy(buf,"a");

	// 测试更新二进制数据字段
	
	strSQL = _T("UPDATE aaa SET c=?, b=? where a = 1");

	cbImageParam = SQL_LEN_DATA_AT_EXEC(0);

	rc = SQLAllocStmt(m_hdbc1,&hstmt);
	rc = SQLPrepare(hstmt, (UCHAR *)LPCTSTR(strSQL), SQL_NTS);

	rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT     ,0,0, &sC    ,0, &(cb1[0]));
	rc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,1,0, (PTR)2 ,0, &(cb1[1]));

//	SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,0,0, (PTR)2   ,0, &(cbLen[1]));
	
//	rc = SQLBindParameter(hstmt,1,
//	SQL_PARAM_INPUT, SQL_C_BINARY, 
//	SQL_LONGVARBINARY,0,0, (PTR)1,0,&cbImageParam);

//    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
//          SQL_C_BINARY, SQL_LONGVARBINARY,
 //         0, 0, (SQLPOINTER)1, 0, &cbImageParam);
	
	GetError(hstmt);
	
	sC = 6;

	rc = SQLExecute(hstmt);
	GetError(hstmt);
	while (rc == SQL_NEED_DATA) 
	  {
		  rc = SQLParamData(hstmt, &pToken);
		  if(rc==SQL_NEED_DATA)
		  {
//			  len=lstn*sizeof(TIC_DOT);
			  SQLPutData(hstmt, buf, strlen(buf));
		  }
	  }

//--------------------------------------------

	// 取表名
	strSQL = _T("select * from ");
	CTableDlg	dlg;
	if(IDOK == dlg.DoModal())
	strSQL  +=  dlg.m_strTable;

	m_pCtrList->DeleteAllItems();

	rc = SQLAllocStmt(m_hdbc1,&hstmt);
	rc = SQLExecDirect(hstmt,(unsigned char *)(LPCTSTR)strSQL,SQL_NTS);
//////////////
	SQLBindCol(hstmt,1,SQL_C_CHAR,szCon1,128,&cb);
	SQLBindCol(hstmt,2,SQL_C_CHAR,szCon2,128,&cb);
	SQLBindCol(hstmt,3,SQL_C_CHAR,szCon3,128,&cb);
	SQLBindCol(hstmt,4,SQL_C_CHAR,szCon4,128,&cb);

	rc=SQLFetch(hstmt);

	while(SQL_SUCCESS==rc)
	{
    	for (iSubItem = 0; iSubItem < 4; iSubItem++)
		{
	     lvitem.mask = LVIF_TEXT | (iSubItem == 0? LVIF_IMAGE : 0);
		   lvitem.iItem = (iSubItem == 0)? m_pCtrList->GetItemCount() : iActualItem;
		   lvitem.iSubItem = iSubItem;
		   if(iSubItem == 0)
		   {
		     strcpy(lvitem.pszText,szCon1); 
		     
		   }
 
		   else if(iSubItem == 1)
		   {
		     strcpy(lvitem.pszText ,szCon2);
			  
		   }
		   else if(iSubItem == 2)
		   {
		     strcpy(lvitem.pszText,szCon3);
		       
		   }
		   else if(iSubItem == 3)
		   {
		     strcpy(lvitem.pszText,szCon4);
		   }
	       if (iSubItem == 0)
		   iActualItem = m_pCtrList->InsertItem(&lvitem); 
		   else
         m_pCtrList->SetItem(&lvitem); 
		}//END FOR

        rc=SQLFetch(hstmt);

	}//END WHILE
         delete lvitem.pszText;    
//////////////

    if(hstmt)
	{	SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
    	hstmt = NULL;
	}	
}

/*
 if(SQL_SUCCESS != SQLAllocStmt(PtWkAi->hdbc, &hstmt))
		  return(0);
	  rt_TransactBegin(PtWkAi,0);  //0=开始一个嵌套事务
	  rt_my_GetTableName(PtWkAi,PtWkAi->AType,0,szTblName,SQL_TBLNAME_LEN);
	  cbLen[0]=0;
	  cbLen[1]=SQL_LEN_DATA_AT_EXEC(0);
	  cbLen[2]=SQL_NTS;
	  cbLen[3]=0;
	  dtype=PtWkAi->AType;
	  ptSQL ="UPDATE MAPGISDBA.MapGisInf SET ticN=?,ticDat=?";
	  ptSQL+=" WHERE grpName=? AND dtype=?";
	  SQLPrepare(hstmt, (UCHAR *)LPCTSTR(ptSQL), SQL_NTS);
	  SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT     ,0,0, &lstn    ,0, &(cbLen[0]));
	  SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,0,0, (PTR)2   ,0, &(cbLen[1]));
	  SQLBindParameter(hstmt,3,SQL_PARAM_INPUT, SQL_C_CHAR  , SQL_VARCHAR      ,0,0, szTblName,0, &(cbLen[2]));
	  SQLBindParameter(hstmt,4,SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT     ,0,0, &dtype   ,0, &(cbLen[3]));
	  retcode=SQLExecute(hstmt);
	  while (retcode == SQL_NEED_DATA) 
	  {
		  retcode=SQLParamData(hstmt, &pToken);
		  if(retcode==SQL_NEED_DATA)
		  {
			  len=lstn*sizeof(TIC_DOT);
			  SQLPutData(hstmt, lst, len);
		  }
	  }
	  rt_TransactCommit(PtWkAi,0);  //0=不用强制提交，即允许事务嵌套
	  SQLFreeStmt(hstmt,SQL_DROP);
*/	 

void CODBCSAMPLEView::OnSqlBrowserConn() 
{
  SQLHSTMT     hstmt1 = SQL_NULL_HSTMT;
  unsigned char	*sqlstate;
  char szTemp[32];
	SQLCHAR			ConnStrOut[MAXBUFLEN];
	SQLSMALLINT		cbConnStrOut = 0;
	RETCODE			retcode;

	retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &m_henv1);
            
	// Notify ODBC that this is an ODBC 3.0 application.
	retcode = SQLSetEnvAttr(m_henv1, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER)SQL_OV_ODBC3,
                     SQL_IS_INTEGER);

	// Allocate an ODBC connection handle and connect.
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv1, &m_hdbc1);

  strcpy(szTemp,"DRIVER={SQL Server};");
  sqlstate=(unsigned char*)szTemp;
  SQLBrowseConnect(m_hdbc1,sqlstate, SQL_NTS, ConnStrOut,
       sizeof(ConnStrOut), &cbConnStrOut);

  strcpy(szTemp,"server=xxb;database=northwind;id=sa;pwd=sa");
    sqlstate=(unsigned char*)szTemp;

  SQLBrowseConnect(m_hdbc1,sqlstate, SQL_NTS, ConnStrOut,
       sizeof(ConnStrOut), &cbConnStrOut);
}

void CODBCSAMPLEView::OnUpdate(CView* pSender, LPARAM lHint, CObject* pHint) 
{	
}