直接透過ODBC API訪問SQL資料庫 (轉)

直接透過ODBC API訪問SQL資料庫 (轉)[@more@]

*********************************
OC - Open DataBase Connectivity
*********************************

Basic Steps

Connecting to the SERVER DataBase for retrieving information from tables

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

1. Allocate ODBC Environment Handle

If SQLAllocEnv(glEnv) <> 0 Then
　　MsgBox "Unable to initialize ODBC s!"
　　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 nnect string - SQLDriverConnect

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

sConnect = "DSN=" & gsDSN & ";UID=" & gsLoginID & ";PWD=" & gsPass & ";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 sSQLState As String * MAX_DATA_BUFFER
Dim sErrorMsg As String * MAX_DATA_BUFFER
Dim sMsg As String

sSQL = " name, location FROM authors"

If SQLExecDirect(glStmt, sSQL, Len(sSQL)) <> SQL_SUCCESS Then
　　' Also Check for ODBC Error message - SQLError
　　lRet = SQLError(glEnv, gldbc, glStmt, sSQLState, lErrNo, sErrorMsg, MAX_DATA_BUFFER, iLen)
　　sMsg = "Error Executing SQL Statement" & Chr$(13) & Chr$(10)
　　sMsg = sMsg & "ODBC State = " & Trim$(Left$(sSQLState, InStr(sSQLState, 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 bPerfoAs 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 sSQL
　　　　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 sSQL
　　　　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
L

'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 ODBCAPI module
***********************************************************************

' ODBC Variables and Constants
　
Global glEnv As Long
Global glDbc As Long
Global sSQL 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__NAME = 6
Global Const SQL_USER_NAME = 8

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

#If 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 cAs 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