ODBCAPI的VBSample

核心提示：Example:PRivateSubCommand1()Dima_hEnvAsLongDima_hDBCAsLongDims_DistrictAsLongDimu_DistrictAsLong<p>DimQueryAsStringDimaTokenAsLongDimRetAsInteger<p>

Example:

PRivateSubCommand1()
Dima_hEnvAsLong
Dima_hDBCAsLong
Dims_DistrictAsLong
Dimu_DistrictAsLong<p>DimQueryAsString
DimaTokenAsLong
DimRetAsInteger<p>Dims_parm1(256)AsByte
Dims_parm2AsInteger
Dims_parm3(256)AsByte
Dims_parm4AsInteger
DimaDTaxAsSingle
DimaNextOrderAsInteger
DimbNextOrderAsInteger
DimcbValue1AsLong
DimcbValue2AsLong
DimcbValue3AsLong
DimcbValue4AsLong
DimcbValue5AsLong
DimiAsInteger
’Variablesthatholdthelengthoftheparameters
cbValue1=SQL_NTS
cbValue2=0
cbValue3=SQL_NTS
cbValue4=0
cbValue5=0
’ConnecttoanAS/400
Ret=SQLAllocEnv(a_hEnv)’AllocatestheSQLenvironment
Ret=SQLAllocConnect(a_hEnv,a_hDBC)’Allocatesconnection
Ret=SQLConnect(a_hDBC,"BANANA",SQL_NTS,"SPEED",SQL_NTS,"SPEED2",SQL_NTS)
’Createapreparedstatementtoselectdata
Ret=SQLAllocStmt(a_hDBC,s_District)
Query="SelectDTAX,DNXTORfromQUSER.DSTRCTwhere(DWID=?andDID=?)"
Ret=SQLSetStmtOption(s_District,SQL_CONCURRENCY,SQL_CONCUR_READ_ONLY)
Ret=SQLPrepare(s_District,Query,SQL_NTS)


’Bindtheparametersfortheselectquery
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&)
’Createapreparedstatementtoupdatedata
Ret=SQLAllocStmt(a_hDBC,u_District)
Query="UpdateDSTRCTsetDNXTOR=?where(DWID=?andDID=?)"
Ret=SQLPrepare(u_District,Query,SQL_NTS)
’BindtheparametersfortheUpdatequery
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)
’YoushouldnotpasscharacterpointerstoDLL’ssoweconvertthemto
’bytearrays(actuallyfromUnicodecharaterstringstobytearrays)
CallStringToBytes("0001",256,s_parm1())’Warehouse
CallStringToBytes("0001",256,s_parm3())’Warehouse
’Thisloopwillincrementafieldintendifferentrows
Fori=1To10
’Settheparameters
s_parm2=i’District
s_parm4=i’District
’Executetheselectquery.
Ret=SQLExecute(s_District)
’Fetchresults.
Ret=SQLFetch(s_District)
Ret=SQLFreeStmt(s_District,SQL_CLOSE)
’Dosomeprocessingofthisrow...
’Thenincrementthefield
bNextOrder=aNextOrder 1
’Executetheupdate
Ret=SQLExecute(u_District)
Ret=SQLFreeStmt(u_District,SQL_CLOSE)
Next
’FreetheODBCresources
Ret=SQLFreeStmt(s_District,SQL_DROP)
Ret=SQLFreeStmt(u_District,SQL_DROP)
Ret=SQLDisconnect(a_hDBC)’Disconnectinganddeallocating.
Ret=SQLFreeConnect(a_hDBC)
Ret=SQLFreeEnv(a_hEnv)
EndSub

PrivateFunctionBytesToString(byte_array()AsByte)AsString
’convertbytearraytostring.
DimDataAsString,StrLenAsString
Data=StrConv(byte_array(),vbUnicode)
StrLen=InStr(Data,Chr(0))-1
BytesToString=Left(Data,StrLen)
EndFunction

PrivateSubStringToBytes(DataAsString,ByteLenAsInteger,return_buffer()AsByte)
’convertstringtobytearray.
DimStrLenAsInteger,CountAsInteger
ForCount=0ToLen(Data)-1
return_buffer(Count)=Asc(Mid(Data,Count 1,1))
NextCount
ForCount=Len(Data)ToByteLen
return_buffer(Count)=0
NextCount
EndSub->