用api实现数据库的操作！

　　其实操作数据库有很多种方式，比如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

　　实现的时候，将程序中的ＯＤＢＣ名称及用户名与密码改成相应的就可以了。

本文地址：http://com.8s8s.com/it/it44067.htm