直接通过 ODBC API 访问 SQL 数据库

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<!--StartFragment-->*********************************
ODBC - Open DataBase Connectivity
*********************************

Basic Steps

Connecting to the SQL Server DataBase for retrieving information from tables


*************************************************************
The steps 1 - 3 are for connecting to the SQL Server Database
*************************************************************


1. Allocate ODBC Environment Handle

If SQLAllocEnv(glEnv) <> 0 Then
　　MsgBox "Unable to initialize ODBC API drivers!"
　　End
End If
______________________________________________________________

2. Allocate ODBC Database Handle

Dim iStatus As Integer

If SQLAllocConnect(glEnv, glDbc) <> 0 Then
　　MsgBox "Could not allocate memory for connection Handle!"
　　 ODBCInit = False

　　' Free the Environment
　　iStatus = SQLFreeEnv(lEnv)

　　If iStatus = SQL_ERROR Then
　　　　MsgBox "Error Freeing Environment From ODBC Drivers"
　　End If

　　' Quit the Application
　　End
End If
______________________________________________________________

3. Connect using the sConnect string - SQLDriverConnect

Dim sResult As String
Dim iSize As Integer
Dim sConnect As String

sConnect = "DSN=" & gsDSN & ";UID=" & gsLoginID & ";PWD=" & gsPassword & ";APP=" & gsAppCode & ";DATABASE=" & gsDatabase

If SQLDriverConnect(glDbc, Screen.ActiveForm.hWnd, sConnect, Len(sConnect), sResult, Len(sResult), iSize, 0) <= 0 Then
　　MsgBox "Could not establish connection to ODBC driver!"
End If
______________________________________________________________


***************************************************
The steps 4 - 8 are for retrieving data from tables
***************************************************


4. Allocate ODBC Statement Handle

If SQLAllocStmt(glDbc, glStmt) <> 0 Then

　　　MsgBox "Could not allocate memory for a statement handle!"

End If
______________________________________________________________

5. Execute ODBC Statement - SQLExecDirect

Dim lRet As Long, lErrNo As Long
Dim iLen As Integer
Dim s SQLState As String * MAX_DATA_BUFFER
Dim sErrorMsg As String * MAX_DATA_BUFFER
Dim sMsg As String

s SQL = "SELECT name, location FROM authors"

If SQLExecDirect(glStmt, s SQL, Len(s SQL)) <> SQL_SUCCESS Then
　　' Also Check for ODBC Error message - SQLError
　　lRet = SQLError(glEnv, gldbc, glStmt, s SQLState, lErrNo, sErrorMsg, MAX_DATA_BUFFER, iLen)
　　sMsg = "Error Executing SQL Statement" & Chr$(13) & Chr$(10)
　　sMsg = sMsg & " ODBC State = " & Trim$(Left$(s SQLState, InStr(s SQLState, Chr$(0)) - 1)) & Chr$(13) & Chr$(10)
　　sMsg = sMsg & " ODBC Error Message = " & Left$(sErrorMsg, iLen)
　　MsgBox sMsg, vbInformation, "Execute Query"
End If
______________________________________________________________

6. Fetch one row of results from executed ODBC Statement - SQLFetch

Code in Step 7.
______________________________________________________________

7. Get the Data in each field of the Fetched row - SQLGetData

Dim bPerform As Integer, iStatus As Integer
Dim sData As String * MAX_DATA_BUFFER
Dim lOutLen As Long

bPerform = SQLFetch(glStmt)

Do While bPerform
　　bPerform = SQLFetch(lStmt)　　　　 ' Get the next row of data
　　If bPerform = SQL_SUCCESS Then　　　　‘ If rows of data available　　
　　　　bPerform = True

　　　　' Get Author Name -　iColumn = 1 for first field i.e. name in s SQL
　　　　iStatus = SQLGetData(glStmt, iColumn, 1, sData, MAX_DATA_BUFFER, lOutLen)

　　　　' lOutlen = length of the valid data in sData
　　　　' Data value will be = Left$(sData, lOutlen),　lOutlen = -1 if no data or Null data

　　　　' Get Location -　iColumn = 2 for second field i.e. location in s SQL
　　　　iStatus = SQLGetData(glStmt, iColumn, 1, sData, MAX_DATA_BUFFER, lOutLen)

　　　　 ' Add the Field Data to Correponding Data Display Controls for this row
　　Else
　　　　bPerform = False　　' No more rows available
　　End If
Loop

'Release the ODBC Statement Handle
bPerform = SQLFreeStmt(glStmt, SQL_DROP)
______________________________________________________________

8. Release the ODBC Statement Handle - SQLFreeSTmt

Code in Step 7.
______________________________________________________________


*******************************************************************
The steps 9 - 11 are for Disconnecting from the SQL Server DataBase
*******************************************************************


9. Disconnect from ODBC Database - SQLDisconnect

iStatus = SQLDisconnect(glDbc)
______________________________________________________________

10. Release the ODBC Database Handle - SQLFreeConnect

iStatus = SQLFreeConnect(glDbc)
______________________________________________________________

11. Release the ODBC Environment Handle - SQLFreeEnv

iStatus = SQLFreeEnv(glEnv)
______________________________________________________________



***********************************************************************
The following entries are required in the ODBC API module
***********************************************************************


' ODBC Variables and Constants
　
Global glEnv As Long
Global glDbc As Long
Global s SQL As String
　
Global Const MAX_DATA_BUFFER = 255
Global Const SQL_SUCCESS = 0
Global Const SQL_SUCCESS_WITH_INFO = 1
Global Const SQL_ERROR = -1
Global Const SQL_NO_DATA_FOUND = 100
Global Const SQL_CLOSE = 0
Global Const SQL_DROP = 1
Global Const SQL_CHAR = 1
Global Const SQL_NUMERIC = 2
Global Const SQL_DECIMAL = 3
Global Const SQL_INTEGER = 4
Global Const SQL_SMALLINT = 5
Global Const SQL_FLOAT = 6
Global Const SQL_REAL = 7
Global Const SQL_DOUBLE = 8
Global Const SQL_VARCHAR = 12
Global Const SQL_DATA_SOURCE_NAME = 6
Global Const SQL_USER_NAME = 8


' ODBC Declarations
'The hWnd is a Long in Windows 95 & Windows NT

#If Win32 Then
　　Declare Function SQLAllocEnv Lib " ODBC32.dll" (env As Long) As Integer
　　Declare Function SQLFreeEnv Lib " ODBC32.dll" (ByVal env As Long) As Integer
　　Declare Function SQLAllocConnect Lib " ODBC32.dll" (ByVal env As Long, ldbc As Long) As Integer
　　Declare Function SQLConnect Lib " ODBC32.dll" (ByVal ldbc As Long, ByVal Server As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal serverlen As Integer, ByVal uid As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal uidlen As Integer, ByVal pwd As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal pwdlen As Integer) As Integer

　　Declare Function SQLDriverConnect Lib " ODBC32.dll" (ByVal ldbc As Long, ByVal hWnd As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal szCSIn As String, ByVal cbCSIn As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal szCSOut As String, ByVal cbCSMax As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　cbCSOut As Integer, ByVal f As Integer) As Integer

　　Declare Function SQLFreeConnect Lib " ODBC32.dll" (ByVal ldbc As Long) As Integer
　　Declare Function SQLDisconnect Lib " ODBC32.dll" (ByVal ldbc As Long) As Integer
　　Declare Function SQLAllocStmt Lib " ODBC32.dll" (ByVal ldbc As Long, lStmt As Long) As Integer
　　Declare Function SQLFreeStmt Lib " ODBC32.dll" (ByVal lStmt As Long, ByVal EndOption As Integer) As Integer
　　Declare Function SQLTables Lib " ODBC32.dll" (ByVal lStmt As Long, ByVal q As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbq As Integer, ByVal o As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal cbo As Integer, ByVal t As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbt As Integer, ByVal tt As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbtt As Integer) As Integer

　　Declare Function SQLExecDirect Lib " ODBC32.dll" (ByVal lStmt As Long, ByVal SQLString As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal SQLstrlen As Long) As Integer

　　Declare Function SQLNumResultCols Lib " ODBC32.dll" (ByVal lStmt As Long, NumCols As Integer) As Integer
　　Declare Function SQLDescribeCol Lib " ODBC32.dll" (ByVal lStmt As Long, ByVal colnum As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal colname As String, ByVal Buflen As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　colnamelen As Integer, dtype As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　dl As Long, ds As Integer, n As Integer) As Integer

　　Declare Function SQLFetch Lib " ODBC32.dll" (ByVal lStmt As Long) As Integer
　　Declare Function SQLGetData Lib " ODBC32.dll" (ByVal lStmt As Long, ByVal col As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal wConvType As Integer, ByVal lpbBuf As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal dwbuflen As Long, lpcbout As Long) As Integer

　　Declare Function SQLGetInfo Lib " ODBC32.dll" (ByVal ldbc As Long, ByVal hWnd As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal szInfo As String, ByVal cbInfoMax As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　 cbInfoOut As Integer) As Integer

　　Declare Function SQLError Lib " ODBC32.dll" (ByVal env As Long, ByVal ldbc As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal lStmt As Long, ByVal SQLState As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 NativeError As Long, ByVal Buffer As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal Buflen As Integer, Outlen As Integer) As Integer

#Else

　　Declare Function SQLAllocEnv Lib " ODBC.dll" (env As Long) As Integer
　　Declare Function SQLFreeEnv Lib " ODBC.dll" (ByVal env As Long) As Integer
　　Declare Function SQLAllocConnect Lib " ODBC.dll" (ByVal env As Long, ldbc As Long) As Integer
　　Declare Function SQLConnect Lib " ODBC.dll" (ByVal ldbc As Long, ByVal Server As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal serverlen As Integer, ByVal uid As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal uidlen As Integer, ByVal pwd As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal pwdlen As Integer) As Integer

　　Declare Function SQLDriverConnect Lib " ODBC.dll" (ByVal ldbc As Long, ByVal hWnd As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal szCSIn As String, ByVal cbCSIn As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal szCSOut As String, ByVal cbCSMax As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　cbCSOut As Integer, ByVal f As Integer) As Integer

　　Declare Function SQLFreeConnect Lib " ODBC.dll" (ByVal ldbc As Long) As Integer
　　Declare Function SQLDisconnect Lib " ODBC.dll" (ByVal ldbc As Long) As Integer
　　Declare Function SQLAllocStmt Lib " ODBC.dll" (ByVal ldbc As Long, lStmt As Long) As Integer
　　Declare Function SQLFreeStmt Lib " ODBC.dll" (ByVal lStmt As Long, ByVal EndOption As Integer) As Integer
　　Declare Function SQLTables Lib " ODBC.dll" (ByVal lStmt As Long, ByVal q As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbq As Integer, ByVal o As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbo As Integer, ByVal t As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbt As Integer, ByVal tt As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal cbtt As Integer) As Integer

　　Declare Function SQLExecDirect Lib " ODBC.dll" (ByVal lStmt As Long, ByVal SQLString As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal SQLstrlen As Long) As Integer

　　Declare Function SQLNumResultCols Lib " ODBC.dll" (ByVal lStmt As Long, NumCols As Integer) As Integer
　　Declare Function SQLDescribeCol Lib " ODBC.dll" (ByVal lStmt As Long, ByVal colnum As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal colname As String, ByVal Buflen As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 colnamelen As Integer, dtype As Integer, dl As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ds As Integer, n As Integer) As Integer

　　Declare Function SQLFetch Lib " ODBC.dll" (ByVal lStmt As Long) As Integer
　　Declare Function SQLGetData Lib " ODBC.dll" (ByVal lStmt As Long, ByVal col As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal wConvType As Integer, ByVal lpbBuf As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal dwbuflen As Long, lpcbout As Long) As Integer

　　Declare Function SQLGetInfo Lib " ODBC.dll" (ByVal ldbc As Long, ByVal hWnd As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 ByVal szInfo As String, ByVal cbInfoMax As Integer, _
　　　　　　　　　　　　　　　　　　　　　　　　　　　 cbInfoOut As Integer) As Integer

　　Declare Function SQLError Lib " ODBC.dll" (ByVal env As Long, ByVal ldbc As Long, _
　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal lStmt As Long, ByVal SQLState As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　NativeError As Long, ByVal Buffer As String, _
　　　　　　　　　　　　　　　　　　　　　　　　　　ByVal Buflen As Integer, Outlen As Integer) As Integer

#End If 
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>