用api實現資料庫的操作!

來源:互聯網
上載者:User
資料|資料庫 其實操作資料庫有很多種方式,比如DAO、ADO等。不過這些方式在發布的時候都需要帶上一些運行庫,少則幾兆,多則十幾兆。本來很簡單的對資料庫的操作,發布的時候帶上這些庫之後,程式有十幾兆。筆者在實踐中,總結了用API進行操作資料庫的方法,對於一些簡單的資料庫操作還是可以用這種方法實現的。最大的優點就是可以省去運行庫的支援。大大的簡小安裝包的尺寸。



                         崔占民

                     EMAIL:CUIZM@163.COM



首先添加一個模組,方法:菜單->工程->添加模組,代碼如下:



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名稱及使用者名稱與密碼改成相應的就可以了。



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。