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

原创 2001年07月21日 21:33:00
<!--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 

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

  • zgqtxwd
  • zgqtxwd
  • 2008年05月01日 05:35
  • 128

使用ODBC访问数据库(一)

使用ODBC API之前要用到的头文件和LIB库#include "sql.h"              // This is the the main include for ODBC Core ...
  • vavale
  • vavale
  • 2005年03月22日 22:26
  • 903

使用ODBC连接Microsoft SQL server数据库

因为最近要用C对SQL Server进行连接,但发现网上关于这方面的资料不多,就把这两天查到的资料和心得归拢了下,留着以后自己看。 使用C语言通过ODBC(开放式数据库互连)对SQL Server进...
  • hanghangde
  • hanghangde
  • 2015年12月05日 13:43
  • 3031

R通过 ODBC方式连接sql server

一、先配置好ODBC数据源 二、R中连接操作 1、加载包 > library(RODBC) 2、创建连接: > channel 3、查看某张表里的内容...
  • longtingjing
  • longtingjing
  • 2015年09月20日 11:17
  • 444

java访问SQLServer2008数据库方式一:jdbc-odbc桥连接

import java.sql.*; public class Test2 { public static void main(String[] args) { //定义需要的对象 Co...
  • Geek_ymv
  • Geek_ymv
  • 2013年09月07日 14:55
  • 3895

通过unixODBC访问PostgreSQL数据库

步骤1,先安装PostgreSQL 具体不详述,参考PostgreSQL的官方文档或下载、解压后的INSTALL文件中的指令即可 运行configure时指定安装目录:./configur...
  • yangbingzhou
  • yangbingzhou
  • 2015年04月30日 10:04
  • 994

使用ODBC连接SQL Server数据库进行增删查改操作全过程

#include #include #include #include #include #include #include #include using namespace std; ch...
  • gukesdo
  • gukesdo
  • 2011年11月30日 22:00
  • 6468

使用MFC ODBC +SQL Server2005 实现数据库编程

最近在项目中添加了用户数据管理功能,使用MFC ODBC连接SQL Server2005数据库。由于以前没有接触过数据库具体的实现,因此花了两周时间学习了数据库的知识,并实现了项目中的基本功能。感觉虽...
  • ljinddlj
  • ljinddlj
  • 2007年09月20日 18:33
  • 6005

c++利用SqlApi访问SqlServer数据库(存储过程篇)

一、SqlApi介绍     SqlApi++是一个为访问Sql数据库而编写的库。支持对Oracle, SQL Server, DB2, Sybase, Informix, InterBas...
  • qingzai_
  • qingzai_
  • 2016年04月28日 09:28
  • 2029

VB6 用ODBC连接数据库

遇到的问题一:调用Dim hdbc As Long '连接句柄 rc = SQLAllocConnect(henv, hdbc) '获取连接句柄 MsgBox rc If rc 0 Th...
  • cf643487053
  • cf643487053
  • 2017年01月12日 16:13
  • 720
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:直接通过 ODBC API 访问 SQL 数据库
举报原因:
原因补充:

(最多只允许输入30个字)