一个例子
这一章,我们要把我们已学的知识集合起来。具体来讲,我们来写一个使用ODBC APIs的程序.为简单起见,这个程序中我使用Microsoft的Access数据库(Microsoft Access 97) .
下载例子源程序.
注意:如果你使用的windows.inc 是1.18及其以下版本,在开始编译之前要修改其中的一个小bug.在windows.inc中查找 "SQL_NULL_HANDLE"，将得到下面这行:
SQL_NULL_HANDLE equ 0L
将0后面的"L"删除,象这样:
SQL_NULL_HANDLE equ 0
这个程序是一个基于对话框的程序，有一个简单的菜单.当用户选择"connect"时,它将试图连接test.mdb数据库,如果连接成功,将显示由ODBC驱动程序返回的完整连接字符串.接下来,用户可选择"View All Records"命令,程序会使用listview control来显示数据库中的所有数据.用户还可以选择"Query"命令来查询特定的记录.例子程序将会显示一个小对话框提示用户输入想找的人名.当用户按下OK钮或回车键,程序将执行一个查询来查找符合条件的记录.当用户完成对数据库的操作时,可以选择"disconnect"命令与数据库断开连接.
现在看一下源程序:
.386.model flat,stdcallinclude \masm32\include\windows.incinclude \masm32\include\kernel32.incinclude \masm32\include\odbc32.incinclude \masm32\include\comctl32.incinclude \masm32\include\user32.incincludelib \masm32\lib\odbc32.libincludelib \masm32\lib\comctl32.libincludelib \masm32\lib\kernel32.libincludelib \masm32\lib\user32.libIDD_MAINDLG equ 101IDR_MAINMENU equ 102IDC_DATALIST equ 1000IDM_CONNECT equ 40001IDM_DISCONNECT equ 40002IDM_QUERY equ 40003IDC_NAME equ 1000IDC_OK equ 1001IDC_CANCEL equ 1002IDM_CUSTOMQUERY equ 40004IDD_QUERYDLG equ 102DlgProc proto hDlg:DWORD, uMsg:DWORD, wParam:DWORD, lParam:DWORDQueryProc proto hDlg:DWORD, uMsg:DWORD, wParam:DWORD, lParam:DWORDSwitchMenuState proto :DWORDODBCConnect proto :DWORDODBCDisconnect proto :DWORDRunQuery proto :DWORD.data?hInstance dd ?hEnv dd ?hConn dd ?hStmt dd ?Conn db 256 dup(?)StrLen dd ?hMenu dd ? ; 主菜单句柄hList dd ? ; listview control句柄TheName db 26 dup(?)TheSurname db 26 dup(?)TelNo db 21 dup(?)NameLength dd ?SurnameLength dd ?TelNoLength dd ?SearchName db 26 dup(?)ProgPath db 256 dup(?)ConnectString db 1024 dup(?).dataSQLStatement db "select * from main",0WhereStatement db " where name=?",0strConnect db "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=",0DBName db "test.mdb",0ConnectCaption db "Complete Connection String",0Disconnect db "Disconnect successful",0AppName db "ODBC Test",0AllocEnvFail db "Environment handle allocation failed",0AllocConnFail db "Connection handle allocation failed",0SetAttrFail db "Cannot set desired ODBC version",0NoData db "You must type the name in the edit box",0ExecuteFail db "Execution of SQL statement failed",0ConnFail db "Connection attempt failed",0AllocStmtFail db "Statement handle allocation failed",0Heading1 db "Name",0Heading2 db "Surname",0Heading3 db "Telephone No.",0.codestart:invoke GetModuleHandle, NULLmov hInstance,eaxcall GetProgramPathinvoke DialogBoxParam, hInstance, IDD_MAINDLG,0,addr DlgProc,0invoke ExitProcess,eaxinvoke InitCommonControlsDlgProc proc hDlg:DWORD, uMsg:DWORD, wParam:DWORD, lParam:DWORD.if uMsg==WM_INITDIALOGinvoke GetMenu, hDlgmov hMenu,eaxinvoke GetDlgItem, hDlg, IDC_DATALISTmov hList,eaxcall InsertColumn.elseif uMsg==WM_CLOSEinvoke GetMenuState, hMenu, IDM_CONNECT,MF_BYCOMMAND.if eax==MF_GRAYEDinvoke ODBCDisconnect, hDlg.endifinvoke EndDialog,hDlg, 0.elseif uMsg==WM_COMMAND.if lParam==0mov eax,wParam.if ax==IDM_CONNECTinvoke ODBCConnect,hDlg.elseif ax==IDM_DISCONNECTinvoke ODBCDisconnect,hDlg.elseif ax==IDM_QUERYinvoke RunQuery,hDlg.elseif ax==IDM_CUSTOMQUERYinvoke DialogBoxParam, hInstance, IDD_QUERYDLG,hDlg, addr QueryProc, 0.endif.endif.elsemov eax,FALSEret.endifmov eax,TRUEretDlgProc endp GetProgramPath procinvoke GetModuleFileName, NULL,addr ProgPath,sizeof ProgPathstdmov edi,offset ProgPathadd edi,sizeof ProgPath-1mov al,"\"mov ecx,sizeof ProgPathrepne scasbcldmov byte ptr [edi+2],0retGetProgramPath endpSwitchMenuState proc Flag:DWORD.if Flag==TRUE invoke EnableMenuItem, hMenu, IDM_CONNECT, MF_GRAYEDinvoke EnableMenuItem, hMenu, IDM_DISCONNECT, MF_ENABLEDinvoke EnableMenuItem, hMenu, IDM_QUERY, MF_ENABLEDinvoke EnableMenuItem, hMenu, IDM_CUSTOMQUERY, MF_ENABLED.elseinvoke EnableMenuItem, hMenu, IDM_CONNECT, MF_ENABLEDinvoke EnableMenuItem, hMenu, IDM_DISCONNECT, MF_GRAYEDinvoke EnableMenuItem, hMenu, IDM_QUERY, MF_GRAYEDinvoke EnableMenuItem, hMenu, IDM_CUSTOMQUERY, MF_GRAYED.endifretSwitchMenuState endpODBCConnect proc hDlg:DWORDinvoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_NULL_HANDLE, addr hEnv.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke SQLSetEnvAttr, hEnv,SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3,0.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke SQLAllocHandle, SQL_HANDLE_DBC, hEnv, addr hConn.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke lstrcpy,addr ConnectString,addr strConnectinvoke lstrcat,addr ConnectString, addr ProgPathinvoke lstrcat, addr ConnectString,addr DBNameinvoke SQLDriverConnect, hConn, hDlg, addr ConnectString, sizeof ConnectString, addr Conn, sizeof Conn,addr StrLen, SQL_DRIVER_COMPLETE.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke SwitchMenuState,TRUEinvoke MessageBox,hDlg, addr Conn,addr ConnectCaption,MB_OK+MB_ICONINFORMATION.elseinvoke SQLFreeHandle, SQL_HANDLE_DBC, hConninvoke SQLFreeHandle, SQL_HANDLE_ENV, hEnvinvoke MessageBox, hDlg, addr ConnFail, addr AppName, MB_OK+MB_ICONERROR.endif.elseinvoke SQLFreeHandle, SQL_HANDLE_ENV, hEnvinvoke MessageBox, hDlg, addr AllocConnFail, addr AppName, MB_OK+MB_ICONERROR.endif.elseinvoke SQLFreeHandle, SQL_HANDLE_ENV, hEnvinvoke MessageBox, hDlg, addr SetAttrFail, addr AppName, MB_OK+MB_ICONERROR.endif.elseinvoke MessageBox, hDlg, addr AllocEnvFail, addr AppName, MB_OK+MB_ICONERROR .endifretODBCConnect endpODBCDisconnect proc hDlg:DWORDinvoke SQLDisconnect, hConninvoke SQLFreeHandle, SQL_HANDLE_DBC, hConninvoke SQLFreeHandle, SQL_HANDLE_ENV, hEnvinvoke SwitchMenuState, FALSEinvoke ShowWindow,hList, SW_HIDEinvoke MessageBox,hDlg,addr Disconnect, addr AppName,MB_OK+MB_ICONINFORMATIONretODBCDisconnect endpInsertColumn procLOCAL lvc:LV_COLUMNmov lvc.imask,LVCF_TEXT+LVCF_WIDTHmov lvc.pszText,offset Heading1mov lvc.lx,150invoke SendMessage,hList, LVM_INSERTCOLUMN,0,addr lvcmov lvc.pszText,offset Heading2invoke SendMessage,hList, LVM_INSERTCOLUMN, 1 ,addr lvc mov lvc.pszText,offset Heading3invoke SendMessage,hList, LVM_INSERTCOLUMN, 3 ,addr lvc ret InsertColumn endpFillData procLOCAL lvi:LV_ITEMLOCAL row:DWORDinvoke SQLBindCol, hStmt,1,SQL_C_CHAR, addr TheName, sizeof TheName,addr NameLengthinvoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheSurname, sizeof TheSurname,addr SurnameLengthinvoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TelNo, sizeof TelNo,addr TelNoLengthmov row,0.while TRUEmov byte ptr ds:[TheName],0mov byte ptr ds:[TheSurname],0mov byte ptr ds:[TelNo],0invoke SQLFetch, hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOmov lvi.imask,LVIF_TEXT+LVIF_PARAMpush rowpop lvi.iItem mov lvi.iSubItem,0mov lvi.pszText, offset TheNamepush rowpop lvi.lParaminvoke SendMessage,hList, LVM_INSERTITEM,0, addr lvimov lvi.imask,LVIF_TEXTinc lvi.iSubItemmov lvi.pszText,offset TheSurnameinvoke SendMessage,hList,LVM_SETITEM, 0,addr lviinc lvi.iSubItemmov lvi.pszText,offset TelNoinvoke SendMessage,hList,LVM_SETITEM, 0,addr lviinc row.else.break.endif.endwretFillData endpRunQuery proc hDlg:DWORDinvoke ShowWindow, hList, SW_SHOWinvoke SendMessage, hList, LVM_DELETEALLITEMS,0,0invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke SQLExecDirect, hStmt, addr SQLStatement, sizeof SQLStatement.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke FillData.elseinvoke ShowWindow, hList, SW_HIDEinvoke MessageBox,hDlg,addr ExecuteFail, addr AppName, MB_OK+MB_ICONERROR.endifinvoke SQLCloseCursor, hStmtinvoke SQLFreeHandle, SQL_HANDLE_STMT, hStmt.elseinvoke ShowWindow, hList, SW_HIDEinvoke MessageBox,hDlg,addr AllocStmtFail, addr AppName, MB_OK+MB_ICONERROR.endifretRunQuery endpQueryProc proc hDlg:DWORD, uMsg:DWORD, wParam:DWORD, lParam:DWORD .if uMsg==WM_CLOSEinvoke SQLFreeHandle, SQL_HANDLE_STMT, hStmtinvoke EndDialog, hDlg,0.elseif uMsg==WM_INITDIALOGinvoke ShowWindow, hList, SW_SHOWinvoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke lstrcpy, addr Conn, addr SQLStatementinvoke lstrcat, addr Conn, addr WhereStatementinvoke SQLBindParameter,hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,25,0, addr SearchName,25,addr StrLeninvoke SQLPrepare, hStmt, addr Conn, sizeof Conn.elseinvoke ShowWindow, hList, SW_HIDEinvoke MessageBox,hDlg,addr AllocStmtFail, addr AppName, MB_OK+MB_ICONERRORinvoke EndDialog, hDlg,0.endif .elseif uMsg==WM_COMMANDmov eax, wParamshr eax,16.if ax==BN_CLICKEDmov eax,wParam.if ax==IDC_OKinvoke GetDlgItemText, hDlg, IDC_NAME, addr SearchName, 25.if ax==0invoke MessageBox, hDlg,addr NoData, addr AppName, MB_OK+MB_ICONERRORinvoke GetDlgItem, hDlg, IDC_NAMEinvoke SetFocus, eax.elseinvoke lstrlen,addr SearchNamemov StrLen,eaxinvoke SendMessage, hList, LVM_DELETEALLITEMS,0,0invoke SQLExecute, hStmtinvoke FillDatainvoke SQLCloseCursor, hStmt.endif.elseinvoke SQLFreeHandle, SQL_HANDLE_STMT, hStmtinvoke EndDialog, hDlg,0.endif.endif.elsemov eax,FALSEret.endifmov eax,TRUEretQueryProc endpend start
分析
start:invoke GetModuleHandle, NULLmov hInstance,eaxcall GetProgramPath
当程序开始时,将获得实例句柄并获得所在路径.默认情况下数据库 test.mdb应与程序处于同一文件夹.
GetProgramPath procinvoke GetModuleFileName, NULL,addr ProgPath,sizeof ProgPathstdmov edi,offset ProgPathadd edi,sizeof ProgPath-1mov al,"\"mov ecx,sizeof ProgPathrepne scasbcldmov byte ptr [edi+2],0retGetProgramPath endp
GetProgramPath调用GetModuleFileName来获得程序的全路径名.接着在路径中查找最后一个"\"符",通过将文件名的第一个字符置为0获得(truncate)" 文件名. 因此我们在ProgPath中获得了程序的路径名.
然后程序将用DialogBoxParam显示主对话框.当主对话框第一次被载入时,它将获得菜单句柄和listview control句柄.接下来在listview control中插入三列(因为我们已经知道结果集将包含三列.因为是我们先建的表.)
现在,它就等待用户的动作了.如果用户在菜单中选择"connect",将会调用ODBCConnect函数.
ODBCConnect proc hDlg:DWORDinvoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_NULL_HANDLE, addr hEnv.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
它做的第一件事是调用SQLAllocHandle来分配一个环境句柄.
invoke SQLSetEnvAttr, hEnv,SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3,0.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
获得环境句柄后,程序调用SQLSetEnvAttr来表示将要使用ODBC 3.x的语法.
invoke SQLAllocHandle, SQL_HANDLE_DBC, hEnv, addr hConn.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
如果一切顺利,程序将通过调用SQLAllocHandle获得连接句柄来实现连接.
invoke lstrcpy,addr ConnectString,addr strConnectinvoke lstrcat,addr ConnectString, addr ProgPathinvoke lstrcat, addr ConnectString,addr DBName
接着填写连接字符串.完整的连接字符串将被用在ConnectionString
invoke SQLDriverConnect, hConn, hDlg, addr ConnectString, sizeof ConnectString, addr Conn, sizeof Conn,addr StrLen, SQL_DRIVER_COMPLETE.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke SwitchMenuState,TRUEinvoke MessageBox,hDlg, addr Conn,addr ConnectCaption,MB_OK+MB_ICONINFORMATION
当连接字符串完成,程序将调用SQLDriverConnect来通过MS Access ODBC 驱动程序连接test.mdb数据库.如果文件test.mdb不存在,ODBC driver将提示用户输入该文件的位置,因为我们已经设定了SQL_DRIVER_COMPLETE标志.当SQLDriverConnect成功返回时, Conn 被填入由ODBC驱动程序创建的完整连接字符串.我们通过一个message box来将其显示给用户. SwitchMenuState是一个单纯切换菜单选项可用的函数.
现在,到数据库的连接已经建立并被打开,并一直保持打开状态直到用户选择关闭.
当用户选择了"View All Records"命令, 对话框过程将调用RunQuery.函数
RunQuery proc hDlg:DWORDinvoke ShowWindow, hList, SW_SHOWinvoke SendMessage, hList, LVM_DELETEALLITEMS,0,0
由于listview control在创建时是不可见的,现在我们把它显示出来.还有 要把其中的所有元素(如果有的话)删掉.
invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
接下来,程序将获得一个语句句柄.
invoke SQLExecDirect, hStmt, addr SQLStatement, sizeof SQLStatement.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
通过 SQLExecDirect执行已准备好的SQL语句.我这里选择SQLExecDirect 的原因是只须执行一次.
invoke FillData
执行SQL语句后,将返回一个结果集.我们使用 FillData函数来从结果集中解出数据并将其放入listview control中.
FillData procLOCAL lvi:LV_ITEMLOCAL row:DWORDinvoke SQLBindCol, hStmt,1,SQL_C_CHAR, addr TheName, sizeof TheName,addr NameLengthinvoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheSurname, sizeof TheSurname,addr SurnameLengthinvoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TelNo, sizeof TelNo,addr TelNoLength
现在,结果集被返回.我们要绑定结果集的所有三列到我们提供的缓冲区中.这是调用SQLBindCol来实现的.注意我们要对每一列分别调用.并且我们并不需要绑定所有的列:只要绑定要获得数据的列就行了.
mov row,0.while TRUEmov byte ptr ds:[TheName],0mov byte ptr ds:[TheSurname],0mov byte ptr ds:[TelNo],0
当列中没有数据时,我们初始化缓冲区为NULLs.更好的方法是用SQLBindCol指定的变量中数据的长度.在我们的例子中,我们可以检查NameLength, SurnameLength和TelNoLength中的值的确切长度.
invoke SQLFetch, hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOmov lvi.imask,LVIF_TEXT+LVIF_PARAMpush rowpop lvi.iItem mov lvi.iSubItem,0mov lvi.pszText, offset TheNamepush rowpop lvi.lParaminvoke SendMessage,hList, LVM_INSERTITEM,0, addr lvi
其它都很简单了.调用SQLFetch 来获得结果集的一行,并将其存入listview control的缓冲区中.当没有更多的行供检索时(已到达文件尾), SQLFetch返回SQL_NO_DATA并且程序跳出循环.
invoke SQLCloseCursor, hStmtinvoke SQLFreeHandle, SQL_HANDLE_STMT, hStmt
当完成对结果集的操作时,调用SQLCloseCursor关闭结果集并调用SQLFreeHandle释放语句句柄.
当用户选择"Query"命令,程序显示另一个对话框供用户输入要查询的名字.
.elseif uMsg==WM_INITDIALOGinvoke ShowWindow, hList, SW_SHOWinvoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFOinvoke lstrcpy, addr Conn, addr SQLStatementinvoke lstrcat, addr Conn, addr WhereStatementinvoke SQLBindParameter,hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,25,0, addr SearchName,25,addr StrLeninvoke SQLPrepare, hStmt, addr Conn, sizeof Conn
对话框做的第一件事是显示listview control.接下来分配一个语句句柄以创建SQL语句.这个SQL语句有一个"where"子句及一个参数标志符"?". 完整的SQL语句是:
select * from main where name=?
接着程序调用SQLBindParameter 来建立参数标志符与缓冲区SearchName的连接,这样当SQL语句被执行时,ODBC驱动程序就可从SearchName中获得需要的字符串.接下来,程序调用SQLPrepare来编译SQL语句. 这样我们只要准备/编译SQL语句一次就可多次使用.因为SQL语句已被编译过,接下来的执行过程会快一些.
.if ax==IDC_OKinvoke GetDlgItemText, hDlg, IDC_NAME, addr SearchName, 25.if ax==0invoke MessageBox, hDlg,addr NoData, addr AppName, MB_OK+MB_ICONERRORinvoke GetDlgItem, hDlg, IDC_NAMEinvoke SetFocus, eax.else
当用户在编辑框(edit control)中填入了一些名字并按下回车键, 程序将获得编辑框中的文本并检查是否是空字符串.如果是,则显示一个message box并将键盘焦点设在编辑框上,提示用户输入名字.
invoke lstrlen,addr SearchNamemov StrLen,eaxinvoke SendMessage, hList, LVM_DELETEALLITEMS,0,0invoke SQLExecute, hStmtinvoke FillDatainvoke SQLCloseCursor, hStmt
如果编辑框中已有字符串,程序会获得它的长度并将其放入StrLen中供ODBC驱动程序使用(记住我们已将StrLen的地址传送给了SQLBindParameter). 接下来程序使用获得的语句句柄调用SQLExecute执行已准备好的SQL语句.当 SQLExecute返回时,程序调用FillData在listview control显示结果.因为我们不会再用到结果集,调用SQLCloseCursor来关闭它.