其实操作数据库有很多种方式,比如DAO、ADO等。不过这些方式在发布的时候都需要带上一些运行库,少则几兆,多则十几兆。本来很简单的对数据库的操作,发布的时候带上这些库之后,程序有十几兆。笔者在实践中,总结了用API进行操作数据库的方法,对于一些简单的数据库操作还是可以用这种方法实现的。最大的优点就是可以省去运行库的支持。大大的简小安装包的尺寸。
崔占民
EMAIL:[email protected]
首先添加一个模块,方法:菜单->工程->添加模块,代码如下:
Option Explicit
Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv&, phdbc&) As Integer Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hdbc&, phstmt&) As Integer Declare Function SQLConnect Lib "odbc32.dll" (ByVal hdbc&, ByVal szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szAuthStr$, ByVal cbAuthStr%) As Integer Declare Function SQLColAttributesString Lib "odbc32.dll" Alias "SQLColAttributes" (ByVal hstmt&, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc&) As Integer Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&, ByVal szSqlStr$, ByVal cbSqlStr&) As Integer Declare Function SQLFetch Lib "odbc32.dll" (ByVal hstmt&) As Integer Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc&) As Integer Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv&) As Integer Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hstmt&, ByVal fOption%) As Integer Declare Function SQLGetData Lib "odbc32.dll" (ByVal hstmt&, ByVal icol%, ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue&) As Integer Declare Function SQLSetData Lib "odbc32.dll" (ByVal hstmt&, ByVal icol%, ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue&) As Integer Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal hstmt&, pccol%) As Integer Declare Function SQLNumResultRols Lib "odbc32.dll" (ByVal hstmt&, pcRol%) As Long
Global Const SQL_C_CHAR As Long = 1 Global Const SQL_COLUMN_LABEL As Long = 18 Global Const SQL_DROP As Long = 1 Global Const SQL_ERROR As Long = -1 Global Const SQL_NO_DATA_FOUND As Long = 100 Global Const SQL_SUCCESS As Long = 0
Public rc As Long '注释:ODBC函数的返回码 Public henv As Long '注释:ODBC环境句柄 Public hdbc As Long
添加一个MSFLEXGRID控件,用来显示从数据库中查询出来的数据,代码如下:
Option Explicit
Private Sub Command1_Click() Unload Me End Sub
Private Sub Form_Load() rc = SQLAllocEnv(henv) If rc <> 0 Then MsgBox "无法初始化ODBC" End End If rc = SQLAllocConnect(henv, hdbc) If rc <> 0 Then MsgBox "无法获得连接句柄" rc = SQLFreeEnv(henv) End End If Dim DSN As String, UID As String, PWD As String DSN = "Powersoft Demo DB V6" UID = "dba" PWD = "sql" rc = SQLConnect(hdbc, DSN, Len(DSN), UID, Len(UID), PWD, Len(UID)) If rc = SQL_ERROR Then MsgBox "无法建立与ODBC数据源的连接" Unload Me End If End Sub
Private Sub cmdQuery_Click() On Error Resume Next Dim hstmt As Long Dim SQLstmt As String Dim RSCols As Integer, RSRows As Long Dim i As Integer, j As Integer Dim ColVal As String * 1024 Dim ColValLen As Long, ColLabLen As Integer, larg As Long Grid1.Redraw = False rc = SQLAllocStmt(hdbc, hstmt) If rc <> SQL_SUCCESS Then MsgBox "无法获得SQL语句句柄" Exit Sub End If SQLstmt = "SELECT * FROM exam_xref_info" rc = SQLExecDirect(hstmt, SQLstmt, Len(SQLstmt)) If rc <> SQL_SUCCESS Then MsgBox "SQL语句执行失败" Exit Sub End If rc = SQLNumResultCols(hstmt, RSCols) If RSCols > 1 Then Grid1.Cols = RSCols Grid1.Rows = 10 Grid1.Row = 0 Else Exit Sub End If For i = 1 To RSCols rc = SQLColAttributesString(hstmt, i, SQL_COLUMN_LABEL, ColVal, 255, ColLabLen, larg) Grid1.Col = i Grid1.Text = Left(ColVal, ColLabLen) Next i Do Until SQLFetch(hstmt) = SQL_NO_DATA_FOUND ColVal = String$(1024, 0) If Grid1.Row + 1 >= Grid1.Rows Then Grid1.Rows = Grid1.Rows + 1 End If Grid1.Row = Grid1.Row + 1 For i = 1 To RSCols rc = SQLGetData(hstmt, i, SQL_C_CHAR, ColVal, Len(ColVal), ColValLen) Grid1.Col = i Grid1.Text = Left$(ColVal, ColValLen) Next i Loop rc = SQLFreeStmt(hstmt, SQL_DROP) Grid1.Redraw = True End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Dim rc As Integer If hdbc <> 0 Then rc = SQLDisconnect(hdbc) End If rc = SQLFreeConnect(hdbc) If henv <> 0 Then rc = SQLFreeEnv(henv) End If End Sub
实现的时候,将程序中的ODBC名称及用户名与密码改成相应的就可以了。 
|