one example
This chapter, we have to set up the knowledge we have learned. Specifically, let's write a program using ODBC APIS. For simplicity, I use Microsoft Access database (Microsoft Access 97) in this program.
Download an example source.
Note: If you use Windows.inc a 1.18 and its following versions, you want to modify a small bug before starting compiling. Find "SQL_NULL_HANDLE" in Windows.inc and you will get below:
SQL_NULL_HANDLE EQU 0L
Delete "L" behind 0, like this:
SQL_NULL_HANDLE EQU 0
This program is a dialog-based program with a simple menu. When the user selects "Connect", it will try to connect the Test.mdb database. If the connection is successful, the full connection string returned by the ODBC driver will be displayed. Next, the user can select the "View All Records" command, the program uses ListView Control to display all the data in the database. Users can also select the "Query" command to query a specific record. The example program will display a small dialog prompt. The user enters the name you want to find. When the user presses the OK button or the Enter key, the program will perform a query to find the qualified record. When the user completes the operation of the database, you can select the "disconnect" command and database disconnection. .
Look at the source program now:
.386
.Model flat, stdcall
INCLUDE /MASM32/INCLUDE/Windows.inc
INCLUDE /MASM32/INCLUDE / WANEL32.INC
INCLUDE /MASM32/INCLUDE/odbc32.inc
INCLUDE /MASM32/INCLUDE/ComctL32.inc
INCLUDE /MASM32/INCLUDE/USER32.INC
INCLUDELIB /MASM32/LIB/odbc32.lib
INCLUDELIB /MASM32/LIB/ComctL32.lib
INCLUDELIB /MASM32/LIB/kernel32.lib
INCLUDELIB /MASM32/LIB/USER32.LIB
IDD_MAINDLG EQU 101
IDR_MainMenu EQU 102
IDC_DATALIST EQU 1000
IDM_CONNECT EQU 40001
IDM_DISCONNECT EQU 40002
IDM_QUERY EQU 40003
IDC_NAME EQU 1000
IDC_OK EQU 1001
IDC_CANCEL EQU 1002
IDM_CUSTOMQUERY EQU 40004
IDD_QUERYDLG EQU 102
DLGPROC Proto HDLG: DWORD, UMSG: DWORD, WPARAM: DWORD, LPARAM: DWORD
QueryProc Proto HDLG: DWORD, UMSG: DWORD, WPARAM: DWORD, LPARAM: DWORD
SwitchMenuState Proto: DWORD
ODBCCONNECT Proto: DWORD
OdbcdisConnect Proto: DWORD
Runquery Proto: DWORD
.DATA?
Hinstance DD?
HENV DD?
HCONN DD?
HSTMT DD?
CONN DB 256 DUP (?)
Strlen DD?
HMENU DD?; Main menu handle
HLIST DD?; ListView Control handle
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 (?)
.DATA
SQLStatement DB "SELECT * from mail", 0
WHERESTATEMENT DB "Where name =?", 0
StrConnect DB "driver = {Microsoft Access Driver (* .mdb)}; dbq =", 0
DBNAME DB "Test.mdb", 0
ConnectCaption DB "Complete Connection String", 0
Disconnect DB "Disconnect Successful", 0
AppName DB "ODBC TEST", 0
Allocenvfail DB "Environment Handle Allocation Failed", 0
AllocConnfail DB "Connection Handle Allocation Faled", 0
SetAttrfail DB "Cannot Set Desired Odbc Version", 0
Nodata DB "You Must Type the name in the edit box", 0
Executefail DB "Execution of Sql Statement Failed", 0
Connfail DB "Connection Attempt Failed", 0
Allocstmtfail DB "Statement Handle Allocation Failed", 0
Heading1 DB "Name", 0
Heading2 DB "Surname", 0
Heading3 db "telephone no.", 0
.code
Start:
Invoke getModuleHandle, NULL
Mov Hinstance, EAX
Call getProgrampath
Invoke Dialogboxparam, Hinstance, IDD_MAINDLG, 0, AddR DlgProc, 0
Invoke EXITPROCESS, EAX
Invoke INITCOMMONCONTROLS
DLGPROC Proc HDLG: DWORD, UMSG: DWORD, WPARAM: DWORD, LPARAM: DWORD
.IF uMSG == WM_INITDIALOG
Invoke GetMenu, HDLG
Mov Hmenu, EAX
Invoke getdlgitem, hdlg, IDC_DATALIST
Mov Hlist, EAX
Call insertColumn
.ELSEIF uMSG == WM_Close
Invoke getmenustate, HMENU, IDM_CONNECT, MF_BYCOMMAND
.IF EAX == mf_grayed
Invoke odbcdisconnect, HDLG
.endif
Invoke EndDialog, HDLG, 0
.ELSEIF uMSG == WM_COMMAND
.IF lparam == 0
Mov Eax, WPARAM
.IF AX == IDM_CONNECT
Invoke odbcconnect, HDLG
.ELSEIF AX == IDM_Disconnect
Invoke odbcdisconnect, HDLG
.ELSEIF AX == IDM_QUERYINVOKE RUNQUERY, HDLG
.ELSEIF AX == IDM_CUSTOMQUERY
Invoke Dialogboxparam, Hinstance, IDD_Querydlg, HDLG, Addr QueryProc, 0
.endif
.endif
.lse
Mov Eax, False
RET
.endif
Mov Eax, True
RET
DLGPROC ENDP
GetProgrampath Proc
Invoke GetModuleFileName, Null, Addr Progpath, SizeOf ProgPath
STD
Mov Edi, Offset ProgPath
Add Edi, SizeOf ProgPath-1
MOV Al, "/"
Mov ECX, SizeOf ProgPath
Repne scaSB
CLD
MOV Byte Ptr [EDI 2], 0
RET
GetProgrampath Endp
SwitchMenuState Proc Flag: DWORD
.IF flag == TRUE
Invoke EnableMenuItem, HMENU, IDM_CONNECT, MF_GRAYED
Invoke EnableMenuItem, HMenu, IDM_Disconnect, MF_ENABLED
Invoke EnableMenuItem, HMenu, IDM_Query, MF_ENABLED
Invoke EnableMenuItem, Hmenu, IDM_CUSTOMQUERY, MF_ENABLED
.lse
Invoke EnableMenuItem, HMENU, IDM_CONNECT, MF_ENABLED
Invoke EnableMenuItem, HMenu, IDM_Disconnect, MF_GRAYED
Invoke EnableMenuItem, Hmenu, IDM_Query, MF_GRAYED
Invoke EnableMenuItem, Hmenu, IDM_CUSTOMQUERY, MF_GRAYED
.endif
RET
SwitchMenuState Endp
ODBCCONNECT Proc HDLG: DWORD
Invoke SQLACHANDLE, SQL_HANDLE_ENV, SQL_NULL_HANDLE, AddR Henv
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Invoke Sqlsetenvattr, Henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Invoke SQLACHANDLE, SQL_HANDLE_DBC, HENV, AddR HCONN
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Invoke lstrcpy, Addr ConnectString, Addr strconnect
Invoke Lstrcat, Addr ConnectString, Addr ProgPath
Invoke Lstrcat, Addr ConnectString, Addr DBNAME
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 SWITCHUSTATATE, TRUE
Invoke Messagebox, HDLG, AddR Conn, Addr ConnectCaption, MB_OK MB_ICONITIONMATION
.lse
Invoke SQLFreeHandle, SQL_HANDLE_DBC, HCONN
Invoke Sqlfreehandle, SQL_HANDLE_ENV, HENV
Invoke Messagebox, HDLG, Addr Connfail, Addr AppName, MB_OK MB_ICONERROR
.endif
.lse
Invoke Sqlfreehandle, SQL_HANDLE_ENV, HENV
Invoke Messagebox, HDLG, Addr Allocconnfail, Addr Appname, MB_OK MB_ICONERROR
.endif
.lse
Invoke Sqlfreehandle, SQL_HANDLE_ENV, HENV
Invoke Messagebox, HDLG, AddR SetAttrfail, Addr Appname, MB_OK MB_ICONERROR
.endif
.lse
Invoke Messagebox, HDLG, Addr Allocenvfail, Addr Appname, MB_OK MB_ICONERROR
.endif
RET
ODBCCONNECT ENDP
Odbcdisconnect Proc HDLG: DWORD
Invoke Sqldisconnect, HCONN
Invoke SQLFreeHandle, SQL_HANDLE_DBC, HCONN
Invoke Sqlfreehandle, SQL_HANDLE_ENV, HENV
Invoke SwitchMenustate, False
Invoke ShowWindow, Hlist, SW_HIDE
Invoke Messagebox, HDLG, AddR Disconnect, Addr Appname, MB_OK MB_ICONICONFORMATION
RET
ODBCDisConnect ENDP
INSERTCOLUMN PROC
LOCAL LVC: LV_COLUMN
MOV LVC.IMASK, LVCF_TEXT LVCF_WIDTH
Mov lvc.psztext, offset heading1
MOV LVC.LX, 150
Invoke SendMessage, HLIST, LVM_INSERTCOLUMN, 0, AddR LVC
Mov lvc.psztext, offset heading2
Invoke SendMessage, HLIST, LVM_INSERTCOLUMN, 1, AddR LVC
Mov lvc.psztext, offset heading3
Invoke SendMessage, HLIST, LVM_INSERTCOLUMN, 3, AddR LVC
RET
INSERTCOLUMN ENDP
FillData Proc
Local LVI: LV_Item
Local Row: DWORD
Invoke Sqlbindcol, HSTMT, 1, SQL_C_CHAR, Addr thename, Sizeof the, Addr Namelength
Invoke Sqlbindcol, HSTMT, 2, SQL_C_CHAR, Addr There, Sizeof THESURNAME, ADDR SURNAMELENGTH
Invoke Sqlbindcol, HSTMT, 3, SQL_C_CHAR, AddR Telno, Sizeof Telno, Addr Telnolengthmov Row, 0
.While true
MOV BYTE PTR DS: [THENAME], 0
MOV BYTE PTR DS: [THESURNAME], 0
MOV BYTE PTR DS: [Telno], 0
Invoke SQLFETCH, HSTMT
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
MOV LVI.IMASK, LVIF_TEXT LVIF_PARAM
Push row
POP LVI.IITEM
Mov lvi.isubitem, 0
Mov lvi.psztext, offset thename
Push row
POP LVI.LPARAM
Invoke SendMessage, HLIST, LVM_INSERTITEM, 0, AddR LVI
Mov Lvi.imask, LVIF_Text
Inc lvi.isubitem
Mov lvi.psztext, offset thespsuame
Invoke SendMessage, HLIST, LVM_SETITEM, 0, AddR LVI
Inc lvi.isubitem
Mov lvi.psztext, offset telno
Invoke SendMessage, HLIST, LVM_SETITEM, 0, AddR LVI
Inc Row
.lse
.break
.endif
.endw
RET
FillData ENDP
Runquery Proc HDLG: DWORD
Invoke ShowWindow, Hlist, SW_SHOW
Invoke SendMessage, Hlist, LVM_DELETEALLITEMS, 0, 0
Invoke Sqlalochandle, 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
Invoke FillData
.lse
Invoke ShowWindow, Hlist, SW_HIDE
Invoke Messagebox, HDLG, Addr Executefail, Addr Appname, MB_OK MB_ICONERROR
.endif
Invoke SqlClosecursor, HSTMT
Invoke Sqlfreehandle, SQL_HANDLE_STMT, HSTMT
.lse
Invoke ShowWindow, Hlist, SW_HIDE
Invoke Messagebox, HDLG, Addr Allocstmtfail, Addr Appname, MB_OK MB_ICONERROR
.endif
RET
Runquery Endp
QueryProc Proc HDLG: DWORD, UMSG: DWORD, WPARAM: DWORD, LPARAM: DWORD
.IF uMSG == WM_Close
Invoke Sqlfreehandle, SQL_HANDLE_STMT, HSTMT
Invoke EndDialog, HDLG, 0
.ELSEIF uMSG == WM_INITDIALOG
Invoke ShowWindow, Hlist, SW_SHOW
Invoke SQLACHANDLE, SQL_HANDLE_STMT, HCONN, ADDR HSTMT.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Invoke Lstrcpy, Addr Conn, Addr SqlStatement
Invoke Lstrcat, Addr Conn, Addr Wherestatement
Invoke SqlbindParameter, HSTMT, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 25, 0, AddR Searchname, 25, Addr Strlen
Invoke SqlpRepare, HSTMT, Addr Conn, Sizeof Conn
.lse
Invoke ShowWindow, Hlist, SW_HIDE
Invoke Messagebox, HDLG, Addr Allocstmtfail, Addr Appname, MB_OK MB_ICONERROR
Invoke EndDialog, HDLG, 0
.endif
.ELSEIF uMSG == WM_COMMAND
Mov Eax, WPARAM
SHR EAX, 16
.IF AX == BN_Clicked
Mov Eax, WPARAM
.IF AX == IDC_OK
Invoke Getdlgitemtext, HDLG, IDC_Name, AddR searchname, 25
.IF AX == 0
Invoke Messagebox, HDLG, AddR Nodata, Addr Appname, MB_OK MB_ICONERROR
Invoke getdlgitem, hdlg, idc_name
Invoke setfocus, EAX
.lse
Invoke lstrlen, AddR searchname
Mov Strlen, EAX
Invoke SendMessage, Hlist, LVM_DELETEALLITEMS, 0, 0
Invoke Sqlexecute, HSTMT
Invoke FillData
Invoke SqlClosecursor, HSTMT
.endif
.lse
Invoke Sqlfreehandle, SQL_HANDLE_STMT, HSTMT
Invoke EndDialog, HDLG, 0
.endif
.endif
.lse
Mov Eax, False
RET
.endif
Mov Eax, True
RET
QueryProc ENDP
End Start
analysis
Start:
Invoke getModuleHandle, NULL
Mov Hinstance, EAX
Call getProgrampath
When the program starts, an instance handle will be obtained and the path is obtained. By default, the database Test.mdb should be in the same folder with the program.
GetProgrampath Proc
Invoke GetModuleFileName, Null, Addr Progpath, SizeOf ProgPath
STD
Mov Edi, Offset ProgPath
Add Edi, SizeOf ProgPath-1
MOV Al, "/"
Mov ECX, SizeOf ProgPath
Repne scaSB
CLD
MOV Byte Ptr [EDI 2], 0
RET
GetProgrampath Endp
GetProgramPath calls getModuleFileName to get a full path name of the program. Then look for the last "/" in "in the path, by obtaining the first word of the file name to 0" file name. So we get in ProgPath The path name of the program. Then the program will use the DialogBoxParam to display the main dialog. When the primary dialog is loaded, it will get a menu handle and a ListView Control handle. Next to insert three columns in ListView Control (because we have already Know that the result set will contain three columns. Because it is our first table.)
Now, it will wait for the user's actions. If the user selects "Connect" in the menu, the ODBCCONNECT function will be called.
ODBCCONNECT Proc HDLG: DWORD
Invoke SQLACHANDLE, SQL_HANDLE_ENV, SQL_NULL_HANDLE, AddR Henv
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
The first thing it does is to call SQLAllocHandle to assign an environment handle.
Invoke Sqlsetenvattr, Henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
After obtaining the environment handle, the program calls SQLSETENVATTR to indicate the syntax of the ODBC 3.x will be used.
Invoke SQLACHANDLE, SQL_HANDLE_DBC, HENV, AddR HCONN
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
If everything goes well, the program will achieve the connection by calling the SQLAllocHandle to get the handle.
Invoke lstrcpy, Addr ConnectString, Addr strconnect
Invoke Lstrcat, Addr ConnectString, Addr ProgPath
Invoke Lstrcat, Addr ConnectString, Addr DBNAME
Then fill in the connection string. The full connection string will be used in 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_INFO
Invoke SwitchMenuState, True
Invoke Messagebox, HDLG, AddR Conn, Addr ConnectCaption, MB_OK MB_ICONITIONMATION
When the connection string is complete, the program will call SqldriverConnect to connect to the test.mdb database via the MS Access ODBC driver. If the file test.mdb does not exist, the ODBC Driver will prompt the user to enter the location of the file, because we have set the SQL_Driver_complete flag When SqldriverConnect is successfully returned, CONN is filled in a full connection string created by the ODBC driver. We use a Message Box to display it to the user. SwitchMenustate is a function available for a simple handover menu option.
Now, the connection to the database has been established and opened, and keeps open until the user selects off.
When the user selects the "View All Records" command, the dialog process will call the Runquery. Function
Runquery Proc HDLG: DWORD
Invoke ShowWindow, Hlist, SW_SHOWINVOKE SendMessage, Hlist, LVM_DELETEALLITEMS, 0, 0 Due to ListView Control is invisible when creating, now we show it. There is also to delete all of them (if any). Invoke Sqlalochandle, SQL_HANDLE_STMT, HCONN, ADDR HSTMT
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO Next, the program will get a statement handle.
Invoke Sqlexecdirect, HSTMT, AddR SqlStatement, Sizeof SqlStatement
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Execute the ready-made SQL statement through SQLEXECDirect. I have selected SQLExecDirect here because it is only necessary.
Invoke FillData
After executing the SQL statement, a result set will be returned. We use the FillData function to solve data from the result set and put it in listview control.
FillData Proc
Local LVI: LV_Item
Local Row: DWORD
Invoke Sqlbindcol, HSTMT, 1, SQL_C_CHAR, Addr thename, Sizeof the, Addr Namelength
Invoke Sqlbindcol, HSTMT, 2, SQL_C_CHAR, Addr There, Sizeof THESURNAME, ADDR SURNAMELENGTH
Invoke Sqlbindcol, HSTMT, 3, SQL_C_CHAR, ADDR TELNO, SIZEOF TELNO, ADDR Telnolength
Now, the result set is returned. We want to bind all three columns to all three columns we provide. This is to call SQLBindcol. Note that we have to call each column. And we don't need to bind all Columns: Just bind to get the column to get the data.
Mov row, 0
.While true
MOV BYTE PTR DS: [THENAME], 0
MOV BYTE PTR DS: [THESURNAME], 0
MOV BYTE PTR DS: [Telno], 0
When there is no data in the column, we initialize the buffer as nulls. Better method is to use the length of data in the variable specified by SQLBINDCOL. In our example, we can check the exact length of the value in Namelength, SurnameLength and Telnolength.
Invoke SQLFETCH, HSTMT
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
MOV LVI.IMASK, LVIF_TEXT LVIF_PARAM
Push row
POP LVI.IITEM
Mov lvi.isubitem, 0
Mov lvi.psztext, offset thename
Push row
POP LVI.LPARAM
Invoke SendMessage, HLIST, LVM_INSERTITEM, 0, AddR LVI
Others are very simple. Call SQLFETCH to get a row of the result set and store it in the buffer of listView control. When there is no more row to retrieve (the file end), SQLFETCH returns SQL_NO_DATA and the program jumps out of the loop .
Invoke SqlClosecursor, HSTMT
Invoke Sqlfreehandle, SQL_HANDLE_STMT, HSTMT
When completing the operation of the result set, call SQLCloseCurSor to turn off the result set and call the SQLFreeHandle to release the clause handle. When the user selects the "query" command, the program displays another dialog for the user to enter the name you want to query ..elseif umsg == WM_INITDIALOG
Invoke ShowWindow, Hlist, SW_SHOW
Invoke Sqlalochandle, SQL_HANDLE_STMT, HCONN, ADDR HSTMT
.IF AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Invoke Lstrcpy, Addr Conn, Addr SqlStatement
Invoke Lstrcat, Addr Conn, Addr Wherestatement
Invoke SqlbindParameter, HSTMT, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 25, 0, AddR Searchname, 25, Addr Strlen
Invoke SqlpRepare, HSTMT, Addr Conn, Sizeof Conn
The first thing to do in the dialog is to display ListView Control. Next to assign a statement handle to create a SQL statement. This SQL statement has a "WHERE" clause and a parameter flag "?". The complete SQL statement is:
SELECT * from mail where name =?
Then the program calls SQLBINDPARAMETER to establish a connection to the buffer Searchname so that the ODBC driver can get the required string from Searchname when the SQL statement is executed. Next, the program calls SQLPRepare to compile SQL statements. Thus We can use multiple times as long as we prepare / compile SQL statements. Because the SQL statement has been compiled, the next execution process will be fast.
.IF AX == IDC_OK
Invoke Getdlgitemtext, HDLG, IDC_Name, AddR searchname, 25
.IF AX == 0
Invoke Messagebox, HDLG, AddR Nodata, Addr Appname, MB_OK MB_ICONERROR
Invoke getdlgitem, hdlg, idc_name
Invoke setfocus, EAX
.lse
When some names are filled in the edit box (Edit Control) and press Enter key, the program will get the text in the edit box and check if it is a null string. If yes, display a message box and focus on the keyboard Set on the Edit box, prompt the user to enter the name.
Invoke lstrlen, AddR searchname
Mov Strlen, EAX
Invoke SendMessage, Hlist, LVM_DELETEALLITEMS, 0, 0
Invoke Sqlexecute, HSTMT
Invoke FillData
Invoke SqlClosecursor, HSTMT
If there is a string in the edit box, the program gets its length and put it in Strlen for the ODBC driver (remember that we have transmitted the Strlen's address to SQLBINDPARAMETER). Next program to use the list of statements Call SQLEXECUTE Execute an prepared SQL statement. When SQLExecute returns, the program calls FillData display results in ListView Control. Because we will not use the result set, call SqlClosecursor to turn it.
