dbfunc.bas

电脑销售管理系统是典型的信息管理系统（MIS）

Attribute VB_Name = "dbfunc"
'此模块用来管理工程中与数据库操作相关的声明


'== 声明odbc32.dll中定义的ODBC API函数 ==
Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal Hdbc&, phstmt&) As Integer
Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal Hdbc&, ByVal szDSN$, _
ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szPWD$, ByVal cbPWD%) As Integer
Public Declare Function SQLColAttributes Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, _
ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer
Public Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal Hstmt&, ByVal szSqlStr$, _
ByVal cbSqStr&) As Integer
Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal Hstmt&) As Integer
Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal Henv&) As Integer
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal Hstmt&, ByVal fOption%) As Integer
Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fCType%, _
ByVal rgbValue As String, ByVal cbValueMax&, pcbValue%) As Integer
Public Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal Hstmt&, pccol%) As Integer
Public Declare Function SQLGetDiagRec Lib "odbc32.dll" (ByVal HandleType%, ByVal Handle&, _
ByVal RecNumber%, Sqlstate%, NativeErrorPtr%, MessageText As String, _
ByVal Bufferlenchgth%, TextlenchgthPtr%)
'== 标记数据库是否连接 ==
Private IsConnect As Boolean
'== 标记执行Connect()函数后，访问数据库的次数 ==
Private Connect_Num As Integer    '是整型变量，它也是DbFunc模块的私有变量，它的作用是记录连接数据库后，使用SQL语句访问数据库的次数
Private Henv As Long     '环境句柄
Private Hdbc As Long     '连接句柄
Private Rc As Long       'Rc变量通常用来接受SQLDisconnect(),SQLFreeconnect()和SQLFREEENV()等函数的返回值
Public Hstmt As Long     'Hstmt变量是ODBC的语句句柄，使用SQLAllocStmt()函数可以得到与连接句柄Hdbc相关联的语句句柄
'连接数据库
Private Sub connect()
  Dim TmpStat As Long  'SQLConnect()函数的返回值
  '如果连接标记为真，则返回。否则会出错
  If IsConnect = True Then
    Exit Sub
  End If
  '分配环境句柄，保存在变量Henv中
  If SQLAllocEnv(Henv) Then    'SQLAllocEnv用来分配环境句柄
    MsgBox "无法初始化ODBC环境!", , "ODBC API执行错误"
    End
  End If
  If SQLAllocConnect(Henv, Hdbc) Then    'SQLAllocConnect用来分配连接句柄
    MsgBox "无法连接ODBC!", , "ODBC API执行错误"
    End
  End If
  '根据连接句柄、数据源、用户名和密码连接指定的数据库
  TmpStat = SQLConnect(Hdbc, DSN, Len(DSN), DB_USER_NAME, _
            Lench(DB_USER_NAME), DB_PASSWORD, Len(DB_PASSWORD))
  '在const中已经定义了DSN为字符型常量值为程序所用的数据源的名称"stocks"
  'len(DSN)是计算数据源名称的长度
  'DBUSE_NAME指用户标识符，已经在const中定义其值为"sa"
  '如果连接不成功,则退出程序
  If TmpStat <> SQL_SUCCESS And _
     TmpStat <> SQL_SUCCESS_WITH_INFO Then
    MsgBox "无法获得连接句柄!", , "ODBC API执行错误"
    IsConnect = True
    Disconnect
    'SQL_SUCCESS表示SQL语句执行成功
    'SQL_SUCCESS_WITH_INFO表示执行SQL语句返回一个警告信息
    End
  End If
  '设置连接标记
  IsConnect = True
End Sub



'断开与数据库的连接

Private Sub Disconnect()
  Dim Rc As Long
  '如果连接标记为假，表明已经断开连接，则直接返回
  If IsConnect = False Then
    Exit Sub
  End If
  '断开连接
  Rc = SQLDisconnect(Hdbc)
  '释放连接句柄
  Rc = SQLFreeConnect(Hdbc)
  '释放环境句柄
  Rc = SQLFreeEnv(Henv)
  'Rc变量通常用来接受SQLDisconnect(),SQLFreeconnect()和SQLFREEENV()等函数的返回值
  IsConnect = False
End Sub
'使用Connect_Num控制数据库连接
Public Sub DB_Connect()
  Connect_Num = Connect_Num + 1       '是整型变量，它也是DbFunc模块的私有变量，它的作用是记录连接数据库后，使用SQL语句访问数据库的次数
  connect
End Sub
Public Sub DB_Disconnect()
  If Connect_Num >= CONNECT_LOOP_MAX Then       'CONNECT_LOOP_MAX是一次执行connect操作可以访问数据库的次数
    Connect_Num = 0
    Disconnect
  End If
End Sub
'强制关闭以api方式访问的数据库，计数器复位
Public Sub DBapi_Disconnect()
  Connect_Num = 0
  Disconnect                       '调用了Disconnect过程来断开数据库
End Sub

'执行ODBC数据库操作语句
Public Sub OdbcExt(ByVal TmpSQLstmt As String)       '
  '根据连接句柄，分配语句句柄
  If SQLAllocStmt(Hdbc, Hstmt) Then                  'SQLAllocStmt用来分配语句句柄
    MsgBox "句柄分配失败", , "ODBC API执行错误"
    DBapi_Disconnect                                 '断开数据库的连接
    End
  End If
  '执行SQL语句，Lench是用户自定义函数，计算包含汉字的字符串长度
  If SQLExecDirect(Hstmt, TmpSQLstmt, Lench(TmpSQLstmt)) Then              'Hstmt变量是ODBC的语句句柄，使用SQLAllocStmt()函数可以得到与连接句柄Hdbc相关联的语句句柄
    MsgBox "数据库访问语句执行失败", , "ODBC API执行错误"                  'SQLExecDirect可以直接执行参数指定的SQL语句，这是执行SQL语句最快捷的方法
    MsgBox TmpSQLstmt
    DBapi_Disconnect
    End
  End If
End Sub
'使用Data控件连接数据库，将执行指定的SQL语句
Public Sub DBdataExt(TmpData As Data, ByVal TmpSource As String)
  '关闭已有的ODBC连接
  DBapi_Disconnect
  'Data控件连接数据库
  TmpData.connect = "ODBC;DATABASE=" + DATABASE _
                  + ";UID=" + DB_USER_NAME + ";PWD=" _
                  + DB_PASSWORD + ";DSN=" + DB_NAME
  TmpData.RecordSource = TmpSource
  TmpData.Refresh
End Sub