Use the API to implement the operation of the database!

Data | Database In fact, there are many ways to operate the database, such as DAO, ADO and so on. However, these methods in the release of the need to bring some run-time library, a few trillion, more than more than 10 trillion. Originally very simple to the operation of the database, the release of the time with these libraries, the program has more than 10 trillion. In practice, the author sums up the method of manipulating database with API, which can be implemented in some simple database operation. The biggest advantage is that you can save the Run-time library support. Large size of simple small installation package.



Ti Jianmin

Email:cuizm@163.com



First add a module, Method: Menu-> Engineering-> Add module, the code is as follows:



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&amp, ByVal szdsn$, ByVal cbdsn%, ByVal szuid$, ByVal cbuid%, Byv Al szauthstr$, ByVal cbauthstr%) As Integer
Declare Function sqlcolattributesstring Lib "Odbc32.dll" Alias "SQLColAttributes" (ByVal hstmt&, ByVal icol%, ByVal FD Esctype%, 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&amp, ByVal icol%, ByVal fctype%, ByVal rgbvalue as String, Byva L Cbvaluemax&amp, pcbvalue&) as Integer
Declare Function sqlsetdata Lib "Odbc32.dll" (ByVal hstmt&amp, ByVal icol%, ByVal fctype%, ByVal rgbvalue as String, Byva L Cbvaluemax&amp, 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 ' Comment: Return code for ODBC function
Public henv as Long ' Note: ODBC environment handle
Public Hdbc as Long


Add a MSFlexGrid control to display the data that is queried from the database, as follows:

Option Explicit

Private Sub Command1_Click ()
Unload Me
End Sub

Private Sub Form_Load ()
rc = sqlallocenv (henv)
If RC <> 0 Then
MsgBox "Cannot initialize ODBC"
End
End If

rc = SQLAllocConnect (henv, HDBC)
If RC <> 0 Then
MsgBox "Cannot get connection handle"
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 "cannot establish a connection to an ODBC data source"
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 "Cannot get SQL statement handle"
Exit Sub
End If

sqlstmt = "SELECT * FROM Exam_xref_info"
rc = SQLExecDirect (hstmt, sqlstmt, Len (sqlstmt))
If RC <> sql_success Then
MsgBox "SQL statement execution failed"
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


When implemented, the ODBC name and username and password in the program can be changed accordingly.