分享
 
 
 

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

王朝vb·作者佚名  2006-01-08
窄屏简体版  字體: |||超大  

<!--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 sSQLState As String * MAX_DATA_BUFFER

Dim sErrorMsg As String * MAX_DATA_BUFFER

Dim 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 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

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 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_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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有