谁能给我一个ODBC的API的样例代码
谁能给我一个ODBC的API的样例代码
要包含得到表列表的功能,
代码可以是C#,也可以是VB,最好是PB
谢谢了
[解决办法]
标准模块代码:
Option ExplicitPublic lngEnv As Long 'ODBC环境句柄Public lngDBHandle As Long 'ODBC数据库句柄Public lngStmt As Long '执行SQL语句的句柄 Global Const MAX_DATA_BUFFER = 255 '最大数据缓冲区Global Const SQL_SUCCESS = 0 '执行SQL成功Global Const SQL_SUCCESS_WITH_INFO = 1 'Global Const SQL_ERROR = -1 '执行SQL失败Global 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 = 8Public Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, _ ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As LongRem %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%Rem 取得ODBC环境句柄。env:环境句柄Public Declare Function SQLAllocEnv Lib "odbc32.dll" (env As Long) As IntegerRem 释放ODBC环境句柄。env:环境句柄Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal env As Long) As IntegerRem 取得ODBC数据库句柄。env环境句柄; ldbc:数据库句柄Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal env As Long, ldbc As Long) As IntegerRem 连接ODBC数据源。Public 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 IntegerRemPublic 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 IntegerRemPublic Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal ldbc As Long) As IntegerRemPublic Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal ldbc As Long) As IntegerRem 分配执行SQL语句的句柄。ldbc:数据库句柄; lStmt:执行SQL语句的句柄Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal ldbc As Long, lStmt As Long) As IntegerRemPublic Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal lStmt As Long, ByVal EndOption As Integer) _ As IntegerRemPublic 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 IntegerRemPublic Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal lStmt As Long, ByVal sqlString As String, _ ByVal sqlstrlen As Long) As IntegerRemPublic Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal lStmt As Long, NumCols As Integer) As _ IntegerRemPublic 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 IntegerRem 从SQL结果集中取得一条记录。lStmt:执行SQL语句的句柄Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal lStmt As Long) As IntegerRem 从一条记录中取得自定字段的内容。lStmt:执行SQL语句的句柄; col:字段排序编号(从1开始计数)RemPublic 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 IntegerRemPublic 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 IntegerRem 取得执行SQL语句的错误描述Public 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 IntegerRem 以下定义为注册ODBC数据源的部分Public Const ODBC_ADD_DSN = 1 '添加数据源Public Const ODBC_CONFIG_DSN = 2 '设置(编辑)数据源Public Const ODBC_REMOVE_DSN = 3 '移除数据源Public Const vbAPINull As Long = 0& '空指针Public Declare Function SQLConfigDataSource Lib "odbccp32.dll" ( _ ByVal hwndParent As Long, _ ByVal fRequestas As Integer, _ ByVal lpszDriver As String, _ ByVal lpszAttributes As String) As Long
[解决办法]
窗体代码:
Option ExplicitDim iStatus As IntegerDim sResult As StringDim iSize As IntegerDim strConnect As StringDim lRet As Long, lErrNo As LongDim iLen As IntegerDim sSQLState As String * MAX_DATA_BUFFERDim sErrorMsg As String * MAX_DATA_BUFFERDim sMsg As StringDim bolP As IntegerDim lOutLen As LongDim ODBCInit As Boolean '标记初始化ODBC是否成功Dim strDSN As String 'DSN数据源名称Dim strUID As String '用户名称Dim strPWD As String '用户密码Dim strAPP As String 'Dim strDataBase As StringDim strSQL As String '用于存储SQL语句Dim lngP As LongDim strName As String * MAX_DATA_BUFFERDim strAge As String * MAX_DATA_BUFFERDim strNumber As String * MAX_DATA_BUFFERDim lngRow As LongPrivate Sub btnP_Click() strDSN = "Team" strUID = "" strPWD = "" strAPP = "" strDataBase = App.Path & "\DBList.mdb" '1 分配ODBC连接环境的句柄 If SQLAllocEnv(lngEnv) <> 0 Then MsgBox "不能取得ODBC环境句柄,初始化ODBC连接失败!" End End If '2. 分配ODBC数据库句柄 If SQLAllocConnect(lngEnv, lngDBHandle) <> 0 Then MsgBox "没有内存空间分配给数据库句柄!" ODBCInit = False iStatus = SQLFreeEnv(lngEnv) '初始化ODBC失败,释放ODBC环境句柄 Rem 释放出错时,提示 If iStatus = SQL_ERROR Then MsgBox "释放ODBC环境时出错!" End If End End If '3.根据连接字符串连接指定的ODBC数据库 lngP = SQLConnect(lngDBHandle, strDSN, Len(strDSN), strUID, Len(strUID), strPWD, Len(strPWD)) If (lngP <> SQL_SUCCESS) And (lngP <> SQL_SUCCESS_WITH_INFO) Then MsgBox "不能连接指定的ODBC数据库!" End If '4. Allocate ODBC Statement Handle If SQLAllocStmt(lngDBHandle, lngStmt) <> 0 Then MsgBox "分配执行SQL语句的环境句柄失败!" End If '5. 执行SQL语句 strSQL = "SELECT * FROM TB_Dealer" 'SQL语句 Rem 执行SQL语句,如果执行失败,那么取得错误信息 If SQLExecDirect(lngStmt, strSQL, Len(strSQL)) <> SQL_SUCCESS Then lRet = SQLError(lngEnv, lngDBHandle, lngStmt, sSQLState, lErrNo, sErrorMsg, MAX_DATA_BUFFER, iLen) sMsg = "执行SQL语句出错!" & Chr$(13) & Chr$(10) sMsg = sMsg & "ODBC状态: = " & Trim$(Left$(sSQLState, InStr(sSQLState, Chr$(0)) - 1)) & Chr$(13) & Chr$(10) sMsg = sMsg & "ODBC错误信息: = " & Left$(sErrorMsg, iLen) MsgBox sMsg, vbInformation, "系统消息" End If '6.从SQL结果集中一条记录一条记录的读取记录 lngRow = 1 Do bolP = SQLFetch(lngStmt) '取得一条记录集 If bolP = SQL_SUCCESS Then '如果取记录成功,那么取得记录中的各个字段内容 bolP = True iStatus = SQLGetData(lngStmt, 1, 1, strName, MAX_DATA_BUFFER, lOutLen) iStatus = SQLGetData(lngStmt, 2, 1, strAge, MAX_DATA_BUFFER, lOutLen) iStatus = SQLGetData(lngStmt, 3, 1, strNumber, MAX_DATA_BUFFER, lOutLen) msgTable.TextMatrix(lngRow, 0) = strName msgTable.TextMatrix(lngRow, 1) = strAge msgTable.TextMatrix(lngRow, 2) = strNumber Else bolP = False ' No more rows available End If lngRow = lngRow + 1 Loop Until Not bolP Or lngRow >= msgTable.Rows Rem 读取记录集完毕,释放执行SQL语句的句柄 bolP = SQLFreeStmt(lngStmt, SQL_DROP) '7.断开数据库连接句柄和数据库的连接 iStatus = SQLDisconnect(lngDBHandle) '8.释放数据库连接句柄 iStatus = SQLFreeConnect(lngDBHandle) '9.释放ODBC环境句柄 iStatus = SQLFreeEnv(lngEnv)End SubPrivate Sub Form_Load() Dim strDriver As String Dim strAttributes As String Dim strDSN As String Dim strServerName As String Dim strDataBase As StringOn Error GoTo errLoad With msgTable .Cols = 3 .Rows = 8 .FormatString = "<姓 名|^年 纪|^号 码" .ColWidth(0) = 1000 .ColWidth(1) = 2000 .ColWidth(2) = 4000 End With strDSN = "Team" strServerName = "Microsoft Access Driver (*.mdb)" strDataBase = App.Path & "\DBList.mdb" strDriver = "Microsoft Access Driver (*.mdb)" strAttributes = "DSN=" & strDSN & Chr$(0) strAttributes = strAttributes & "Server=" & strServerName & Chr$(0) strAttributes = strAttributes & "Database=" & strDataBase lngP = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes) If lngP = 0 Then MessageBox hwnd, "创建ODBC数据源失败", "系统提示", vbOKOnly + vbExclamation' Else' MessageBox hwnd, "创建ODBC数据源成功", "系统提示", vbOKOnly + vbExclamation End If Exit SuberrLoad: Debug.Print Err.DescriptionEnd SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Dim lngP As Long Rem 动态注销ODBC数据源' lngP = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _' "SQL Server" & Chr$(0), _' "DSN=DreamTeamSql" & Chr$(0))' If lngP = 0 Then' MessageBox hwnd, "删除ODBC数据源失败", "系统提示", vbOKOnly + vbExclamation' End IfEnd Sub