*********************************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!" EndEnd 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 EndEnd If______________________________________________________________
3. Connect using the sConnect string - SQLDriverConnect
Dim sResult As StringDim iSize As IntegerDim 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 LongDim iLen As IntegerDim sSQLState As String * MAX_DATA_BUFFERDim sErrorMsg As String * MAX_DATA_BUFFERDim sMsg As String
sSQL = "SELECT 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 bPerform As Integer, iStatus As IntegerDim sData As String * MAX_DATA_BUFFERDim 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 IfLoop
'Release the ODBC Statement HandlebPerform = 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 LongGlobal glDbc As LongGlobal sSQL As String Global Const MAX_DATA_BUFFER = 255Global Const SQL_SUCCESS = 0Global Const SQL_SUCCESS_WITH_INFO = 1Global Const SQL_ERROR = -1Global Const SQL_NO_DATA_FOUND = 100Global Const SQL_CLOSE = 0Global Const SQL_DROP = 1Global Const SQL_CHAR = 1Global Const SQL_NUMERIC = 2Global Const SQL_DECIMAL = 3Global Const SQL_INTEGER = 4Global Const SQL_SMALLINT = 5Global Const SQL_FLOAT = 6Global Const SQL_REAL = 7Global Const SQL_DOUBLE = 8Global Const SQL_VARCHAR = 12Global Const SQL_DATA_SOURCE_NAME = 6Global 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
