In this tutorial, we will put everything we have learned so far together. Specifically, we will code a program that makes use of ODBC APIs. For simplicity, I choose Microsoft Access database (Microsoft Access 97) for this program.
Download the example.
Note: If you use windows.inc version 1.18 or below, you have to fix a small bug before you can assemble the example. Search windows.inc for "SQL_NULL_HANDLE". You'll find the line:
SQL_NULL_HANDLE equ 0L
Delete the "L" following 0, like this:
SQL_NULL_HANDLE equ 0
This program is a dialog-based one with a simple menu. When the user selects "connect", it tries to connect to test.mdb, our database. After the connection was successful, it displays the final complete connection string returned by the ODBC driver. After that, the user can select "View All Records", to populate the listview control with all data in the database. Alternatively, the user can select "Query" to search for a specific record. The program will present a small dialog box prompting the user to type the name of the person he wants to search. When the user presses OK button or just kits the return key, the program runs a query to select the record(s) that match(es) the name. When the user is done, he can select "disconnect" to disconnect from the database.
Now let's see the source code:
.386
.model flat,stdcall
include \masm32\include\windows.inc
include \masm32\include\kernel32.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 ? ; handle to the main menu
hList dd ? ; handle to the 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(?)
.data
SQLStatement db "select * from main",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 failed",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_QUERY
invoke RunQuery,hDlg
.elseif ax==IDM_CUSTOMQUERY
invoke DialogBoxParam, hInstance, IDD_QUERYDLG,hDlg, addr QueryProc, 0
.endif
.endif
.else
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
.else
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 SQLAllocHandle, 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 SQLAllocHandle, 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_INFO
invoke SwitchMenuState,TRUE
invoke MessageBox,hDlg, addr Conn,addr ConnectCaption,MB_OK+MB_ICONINFORMATION
.else
invoke SQLFreeHandle, SQL_HANDLE_DBC, hConn
invoke SQLFreeHandle, SQL_HANDLE_ENV, hEnv
invoke MessageBox, hDlg, addr ConnFail, addr AppName, MB_OK+MB_ICONERROR
.endif
.else
invoke SQLFreeHandle, SQL_HANDLE_ENV, hEnv
invoke MessageBox, hDlg, addr AllocConnFail, addr AppName, MB_OK+MB_ICONERROR
.endif
.else
invoke SQLFreeHandle, SQL_HANDLE_ENV, hEnv
invoke MessageBox, hDlg, addr SetAttrFail, addr AppName, MB_OK+MB_ICONERROR
.endif
.else
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_ICONINFORMATION
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 TheName,addr NameLength
invoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheSurname, sizeof TheSurname,addr SurnameLength
invoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TelNo, sizeof TelNo,addr TelNoLength
mov 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 TheSurname
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
.else
.break
.endif
.endw
ret
FillData endp
RunQuery proc hDlg:DWORD
invoke ShowWindow, hList, SW_SHOW
invoke SendMessage, hList, LVM_DELETEALLITEMS,0,0
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
invoke FillData
.else
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
.else
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 SQLAllocHandle, 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
.else
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
.else
invoke lstrlen,addr SearchName
mov StrLen,eax
invoke SendMessage, hList, LVM_DELETEALLITEMS,0,0
invoke SQLExecute, hStmt
invoke FillData
invoke SQLCloseCursor, hStmt
.endif
.else
invoke SQLFreeHandle, SQL_HANDLE_STMT, hStmt
invoke EndDialog, hDlg,0
.endif
.endif
.else
mov eax,FALSE
ret
.endif
mov eax,TRUE
ret
QueryProc endp
end start
start: invoke GetModuleHandle, NULL mov hInstance,eax call GetProgramPath
When the program starts, it obtains the instance
handle and then finds out its own path. The assumption is that the database,
test.mdb, would be in the same folder as 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 obtain the full pathname of the program. After that, it searchs for the last "\" in the pathname so that it can "truncate" the filename from the path by replacing the first character of the filename with 0. Thus we got the path of the program in ProgPath.
The program then displays the main dialog box with DialogBoxParam. The first time the main dialog box is loaded, it obtains the menu handle and the handle to the listview control. It next inserts three columns into the listview control (because we know beforehand that the result set consists of three columns. After all we are the one who created the table in the first place.)
After that, it waits for the user's action. If the user selects "connect" from the menu, it calls ODBCConnect function.
ODBCConnect proc hDlg:DWORD invoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_NULL_HANDLE, addr hEnv .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
The first thing it does is to allocate
an environment handle with SQLAllocHandle.
invoke SQLSetEnvAttr, hEnv,SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3,0 .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
After it obtained the environment handle,
it specifies that it wants to use ODBC 3.x syntax by calling SQLSetEnvAttr.
invoke SQLAllocHandle, SQL_HANDLE_DBC, hEnv, addr hConn .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
If all goes well, it can begin the connection
attempt by allocating a connection handle with SQLAllocHandle.
invoke lstrcpy,addr ConnectString,addr strConnect invoke lstrcat,addr ConnectString, addr ProgPath invoke lstrcat, addr ConnectString,addr DBName
Construct the connection
string. The final connection string we will use is 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_ICONINFORMATION
When the connection string is ready, it calls SQLDriverConnect to attempt to connect to test.mdb with MS Access ODBC driver. If test.mdb is not found, the ODBC driver will prompt the user for its location because we specify SQL_DRIVER_COMPLETE flag. When SQLDriverConnect returned successfully, Conn is filled with the complete connection string constructed by the ODBC driver. We display that string to the user with a message box. SwitchMenuState is a simple function that enables/grays out the appropriate menu items.
Right now, the connection to the database is open until the user chooses to close it.
When the user selects "View All Records",
the dialog box procedure calls RunQuery.
RunQuery proc hDlg:DWORD invoke ShowWindow, hList, SW_SHOW invoke SendMessage, hList, LVM_DELETEALLITEMS,0,0
Since the listview control is created hidden, it's now
the time to show it. Also we need to delete all items (if any) from the listview
control.
invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
Next, the program allocates a statement
handle.
invoke SQLExecDirect, hStmt, addr SQLStatement, sizeof SQLStatement .if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
Execute the premade SQL statement with
SQLExecDirect. I choose to use SQLExecDirect
here because thiis SQL statement is usually called only once.
invoke FillData
After executing the SQL statement, a result set should be returned. We extract the data in the result set into the listview control in FillData function.
FillData proc LOCAL lvi:LV_ITEM LOCAL row:DWORD invoke SQLBindCol, hStmt,1,SQL_C_CHAR, addr TheName, sizeof TheName,addr NameLength invoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheSurname, sizeof TheSurname,addr SurnameLength invoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TelNo, sizeof TelNo,addr TelNoLength
Here, the result set is returned.
We need to bind all three columns of the result set to the buffers we provided.
We call SQLBindCol to do that. Note that
we need a separate call for each column. And we need not bind ALL columns: only
the columns we need to obtain the data from.
mov row,0 .while TRUE mov byte ptr ds:[TheName],0 mov byte ptr ds:[TheSurname],0 mov byte ptr ds:[TelNo],0
We initialize the buffers to NULLs in case there is no data in the column(s). The better way is to use the length of data returned in the variables you specified in SQLBindCol. In our example, we could check the values in NameLength, SurnameLength and TelNoLength for the actual length of the strings returned.
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
The rest is simple. Call SQLFetch to retrieve a row from the result set and then store the values in the buffers into the listview control. When no more row is available (we have reached the end of file), SQLFetch returns SQL_NO_DATA and we exit the infinite loop.
invoke SQLCloseCursor, hStmt invoke SQLFreeHandle, SQL_HANDLE_STMT, hStmt
When we are done with the result set, we
must close it with SQLCloseCursor and then
free the statement handle with SQLFreeHandle.
When the user selects "Query"
menu item, the program displays another dialog box prompting the user for the
name to search for.
.elseif uMsg==WM_INITDIALOG invoke ShowWindow, hList, SW_SHOW invoke SQLAllocHandle, 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 the dialog box does is to show the listview control. It then allocates a statement handle, create an SQL statement. This SQL statement has the "where" clause with a parameter marker,?. The full SQL statement is:
select * from main where name=?
Next, it calls SQLBindParameter to associate the parameter marker with the buffer, SearchName, so that when the SQL statement is executed, the ODBC driver can obtain the string it needs to use from SearchName. Afer that, it calls SQLPrepare to compile the SQL statement. The rationale is that, you prepare/compile the SQL statement only once and then you execute it many times. Since the SQL statement was compiled, the subsequent execution is faster.
.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 .else
When the user typed some name in the edit
control and pressed return key, the program retrieves the text from the edit
control and checks if it's a NULL string. If so, it displays a message box and
sets the keyboard focus to the edit control to prompt the user for a name.
invoke lstrlen,addr SearchName mov StrLen,eax invoke SendMessage, hList, LVM_DELETEALLITEMS,0,0 invoke SQLExecute, hStmt invoke FillData invoke SQLCloseCursor, hStmt
If a name is typed into the edit control, we find out its length and store the value into StrLen for use by ODBC driver (remember that we passed the address of StrLen to SQLBindParameter). Then it calls SQLExecute on the statement handle to execute the prepared SQL statement. When SQLExecute returns, it calls FillData to display the result in the listview control. Since we have no use for the result set anymore, we close it by calling SQLCloseCursor.
[Iczelion's Win32 Assembly Homepage]