ODBC API 的VB Sample

Example:

Private Sub Command1()
Dim a_hEnv As Long
Dim a_hDBC As Long
Dim s_District As Long
Dim u_District As Long<p>Dim Query As String
Dim aToken As Long
Dim Ret As Integer<p>Dim s_parm1(256) As Byte
Dim s_parm2 As Integer
Dim s_parm3(256) As Byte
Dim s_parm4 As Integer
Dim aDTax As Single
Dim aNextOrder As Integer
Dim bNextOrder As Integer
Dim cbValue1 As Long
Dim cbValue2 As Long
Dim cbValue3 As Long
Dim cbValue4 As Long
Dim cbValue5 As Long
Dim i As Integer
’ Variables that hold the length of the parameters
cbValue1 = SQL_NTS
cbValue2 = 0
cbValue3 = SQL_NTS
cbValue4 = 0
cbValue5 = 0
’ Connect to an AS/400
Ret = SQLAllocEnv(a_hEnv) ’ Allocates the SQL environment
Ret = SQLAllocConnect(a_hEnv, a_hDBC) ’ Allocates connection
Ret = SQLConnect(a_hDBC, "BANANA", SQL_NTS, "SPEED", SQL_NTS, "SPEED2", SQL_NTS)
’ Create a prepared statement to select data
Ret = SQLAllocStmt(a_hDBC, s_District)
Query = "Select DTAX, DNXTOR from QUSER.DSTRCT where (DWID=? and DID=?)"
Ret = SQLSetStmtOption(s_District, SQL_CONCURRENCY, SQL_CONCUR_READ_ONLY)
Ret = SQLPrepare(s_District, Query, SQL_NTS)


’ Bind the parameters for the select query
Ret = SQLBindParameter(s_District, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 4, 0, s_parm1(0), 0, cbValue1)
Ret = SQLBindParameter(s_District, 2, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 3, 0, s_parm2, 0, cbValue2)
Ret = SQLBindCol(s_District, 1, SQL_C_FLOAT, aDTax, 0, 0&)
Ret = SQLBindCol(s_District, 2, SQL_C_SSHORT, aNextOrder, 0, 0&)
’ Create a prepared statement to update data
Ret = SQLAllocStmt(a_hDBC, u_District)
Query = "Update DSTRCT set DNXTOR=? where (DWID=? and DID=?)"
Ret = SQLPrepare(u_District, Query, SQL_NTS)
’ Bind the parameters for the Update query
Ret = SQLBindParameter(u_District, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, bNextOrder, 0, cbValue5)
Ret = SQLBindParameter(u_District, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 4, 0, s_parm3(0), 0, cbValue3)
Ret = SQLBindParameter(u_District, 3, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 3, 0, s_parm4, 0, cbValue4)
’ You should not pass character pointers to DLL’s so we convert them to
’ byte arrays (actually from Unicode charater strings to byte arrays)
Call StringToBytes("0001", 256, s_parm1()) ’ Warehouse
Call StringToBytes("0001", 256, s_parm3()) ’ Warehouse
’ This loop will increment a field in ten different rows
For i = 1 To 10
’ Set the parameters
s_parm2 = i ’ District
s_parm4 = i ’ District
’ Execute the select query.
Ret = SQLExecute(s_District)
’ Fetch results.
Ret = SQLFetch(s_District)
Ret = SQLFreeStmt(s_District, SQL_CLOSE)
’ Do some processing of this row...
’ Then increment the field
bNextOrder = aNextOrder 1
’ Execute the update
Ret = SQLExecute(u_District)
Ret = SQLFreeStmt(u_District, SQL_CLOSE)
Next
’ Free the ODBC resources
Ret = SQLFreeStmt(s_District, SQL_DROP)
Ret = SQLFreeStmt(u_District, SQL_DROP)
Ret = SQLDisconnect(a_hDBC) ’ Disconnecting and deallocating.
Ret = SQLFreeConnect(a_hDBC)
Ret = SQLFreeEnv(a_hEnv)
End Sub

Private Function BytesToString(byte_array() As Byte) As String
’ convert byte array to string.
Dim Data As String, StrLen As String
Data = StrConv(byte_array(), VBUnicode)
StrLen = InStr(Data, Chr(0)) - 1
BytesToString = Left(Data, StrLen)
End Function

Private Sub StringToBytes(Data As String, ByteLen As Integer, return_buffer() As Byte)
’ convert string to byte array.
Dim StrLen As Integer, Count As Integer
For Count = 0 To Len(Data) - 1
return_buffer(Count) = Asc(Mid(Data, Count 1, 1))
Next Count
For Count = Len(Data) To ByteLen
return_buffer(Count) = 0
Next Count
End Sub

上一篇： VB5.0与Windows API 间的呼叫技巧（二）
下一篇： VB5.0与Windows API 间的呼叫技巧（一）

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有