我用几个常用的ODBC API操作SQLserver的时候取不出数据,就是SQLGetData函数取不出数据????
几个API分别是:SQLAllocConnect,SQLAllocStmt,SQLConnect,SQLExecDirect,SQLFreeConnect,SQLFreeEnv
,SQLFreeStmt,SQLGetDataAPI声明如下:
Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal Hdbc&, phstmt&) As Integer
Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal Hdbc&, ByVal szDSN$, _
ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szPWD$, ByVal cbPWD%) As Integer
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal Hstmt&, ByVal szSqlStr$, _
ByVal cbSqStr&) As Integer
Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal Hstmt&) As Integer
Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal Henv&) As Integer
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal Hstmt&, ByVal fOption%) As Integer
Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fCType%, _
ByVal rgbValue As String, ByVal cbValueMax&, pcbValue%) As Integer程序代码如下:
dim Henv As Long '环境句柄
dim Hdbc As Long '连接句柄
dim Hstmt As Long 'ODBC语句句柄
Dim Data As String
dim Rc As Long
dim TmpSQLstmt as string '存放要执行的SQL语句Private Sub Form_Load()
Dim TmpStat As Long
If SQLAllocEnv(Henv) Then
MsgBox "环境句柄分配失败!"
End If
If SQLAllocConnect(Henv, Hdbc) Then
MsgBox "连接句柄分配失败"
End If
TmpStat = SQLConnect(Hdbc, DSN, Len(DSN), USERNAME, Len(USERNAME), PASSWORD, Len(PASSWORD))
If TmpStat <> SQL_SUCCESS And TmpStat <> SQL_SUCCESS_WITH_INFO Then '这些常量都没问题
MsgBox "连接数据库失败!"
Disconnect
End IfIf SQLAllocStmt(Hdbc, Hstmt) Then
MsgBox "语句句柄分配失败!"
Disconnect
End If
TmpSQLstmt = "select * from Users"
If SQLExecDirect(Hstmt, TmpSQLstmt, Len(TmpSQLstmt)) Then
MsgBox "语句执行失败"
MsgBox TmpSQLstmt
Disconnect
End If
End SubPrivate Sub Command1_Click()
If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
Rc = SQLGetData(Hstmt, 1, 1, Data, Len(Data), pcblen)
Text1.Text = Val(Data)
Text2.Text = pcblen
Text3.Text = Len(Data)
End If
End Sub点command1以后,text1、2、3都显示0,如果把text1.text=val(data)改为text1.text=data则
text1什么也不显示,为什么data变量没有数据,我逐语句调试过,语句都执行正确也没有错误
我看了书上就是这个SQLGetData能取到数据呀?!没别的函数了?我的ODBC数据源,DSN设置,
数据库里都没有问题呀?数据库里也有数据!请各位高手帮帮忙吧!谢谢
几个API分别是:SQLAllocConnect,SQLAllocStmt,SQLConnect,SQLExecDirect,SQLFreeConnect,SQLFreeEnv
,SQLFreeStmt,SQLGetDataAPI声明如下:
Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal Hdbc&, phstmt&) As Integer
Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal Hdbc&, ByVal szDSN$, _
ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szPWD$, ByVal cbPWD%) As Integer
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal Hstmt&, ByVal szSqlStr$, _
ByVal cbSqStr&) As Integer
Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal Hstmt&) As Integer
Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal Henv&) As Integer
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal Hstmt&, ByVal fOption%) As Integer
Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fCType%, _
ByVal rgbValue As String, ByVal cbValueMax&, pcbValue%) As Integer程序代码如下:
dim Henv As Long '环境句柄
dim Hdbc As Long '连接句柄
dim Hstmt As Long 'ODBC语句句柄
Dim Data As String
dim Rc As Long
dim TmpSQLstmt as string '存放要执行的SQL语句Private Sub Form_Load()
Dim TmpStat As Long
If SQLAllocEnv(Henv) Then
MsgBox "环境句柄分配失败!"
End If
If SQLAllocConnect(Henv, Hdbc) Then
MsgBox "连接句柄分配失败"
End If
TmpStat = SQLConnect(Hdbc, DSN, Len(DSN), USERNAME, Len(USERNAME), PASSWORD, Len(PASSWORD))
If TmpStat <> SQL_SUCCESS And TmpStat <> SQL_SUCCESS_WITH_INFO Then '这些常量都没问题
MsgBox "连接数据库失败!"
Disconnect
End IfIf SQLAllocStmt(Hdbc, Hstmt) Then
MsgBox "语句句柄分配失败!"
Disconnect
End If
TmpSQLstmt = "select * from Users"
If SQLExecDirect(Hstmt, TmpSQLstmt, Len(TmpSQLstmt)) Then
MsgBox "语句执行失败"
MsgBox TmpSQLstmt
Disconnect
End If
End SubPrivate Sub Command1_Click()
If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
Rc = SQLGetData(Hstmt, 1, 1, Data, Len(Data), pcblen)
Text1.Text = Val(Data)
Text2.Text = pcblen
Text3.Text = Len(Data)
End If
End Sub点command1以后,text1、2、3都显示0,如果把text1.text=val(data)改为text1.text=data则
text1什么也不显示,为什么data变量没有数据,我逐语句调试过,语句都执行正确也没有错误
我看了书上就是这个SQLGetData能取到数据呀?!没别的函数了?我的ODBC数据源,DSN设置,
数据库里都没有问题呀?数据库里也有数据!请各位高手帮帮忙吧!谢谢
odbc - open database connectivity
*********************************basic stepsconnecting 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 handleif sqlallocenv(glenv) <> 0 then
msgbox "unable to initialize odbc api drivers!"
end
end if
______________________________________________________________2. allocate odbc database handledim istatus as integerif 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 - sqldriverconnectdim sresult as string
dim isize as integer
dim sconnect as stringsconnect = "dsn=" & gsdsn & ";uid=" & gsloginid & ";pwd=" & gspassword & ";app=" & gsappcode & ";database=" & gsdatabaseif 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 handleif sqlallocstmt(gldbc, glstmt) <> 0 then msgbox "could not allocate memory for a statement handle!"end if
______________________________________________________________5. execute odbc statement - sqlexecdirectdim 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 stringssql = "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 - sqlfetchcode in step 7.
______________________________________________________________7. get the data in each field of the fetched row - sqlgetdatadim bperform as integer, istatus as integer
dim sdata as string * max_data_buffer
dim loutlen as longbperform = 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 - sqlfreestmtcode in step 7.
______________________________________________________________
*******************************************************************
the steps 9 - 11 are for disconnecting from the sql server database
*******************************************************************
9. disconnect from odbc database - sqldisconnectistatus = sqldisconnect(gldbc)
______________________________________________________________10. release the odbc database handle - sqlfreeconnectistatus = sqlfreeconnect(gldbc)
______________________________________________________________11. release the odbc environment handle - sqlfreeenvistatus = sqlfreeenv(glenv)
______________________________________________________________ ***********************************************************************
the following entries are required in the odbcapi module
***********************************************************************
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 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