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
|
