This example opens the data source named "SblTest," gets the names in the ODBC data sources, and closes the connection.
Sub main
' Declarations
'
Dim outputStr As String
Dim connection As Long
Dim prompt As Integer
Dim datasources(1 To 50) As Variant
Dim retcode As Variant
Dim action1 as Integer
Dim qualifier as String
prompt = 5
' Open the datasource "SblTest"
connection = SQLOpen("DSN=SblTest", outputStr, prompt:=5)
action1 = 1 ' Get the names of the ODBC datasources
retcode = SQLGetSchema(connection:=connection,action:=1, qualifier:=qualifier, ref:=datasources())
' Close the datasource connection
retcode = SQLClose(connection)
SQLError Example
This example forces an error to test SQLError function.
sub main
' Declarations
Dim connection As long
Dim prompt as integer
Dim retcode as long
Dim errors(1 To 3, 1 To 10) as Variant
Dim outputStr as String
' Open the datasource
connection = SQLOpen("DSN=SBLTESTW;UID=DBA;PWD=SQL",outputStr,prompt:=3)
' force an error to test SQLError select a nonexistent table
retcode = SQLExecQuery(connection:=connection,query:="select * from notable ")
' Retrieve the detailed error message information into the errors array
SQLError destination:=errors
retcode = SQLClose(connection)
end sub
SQLExecQuery Example
This example performs a query on the data source.
Sub main
' Declarations
'
Dim connection As Long
Dim destination(1 To 50, 1 To 125) As Variant
Dim retcode As long
Dim outputStr as String
Dim query as String
' open the connection
connection = SQLOpen("DSN=SblTest",outputStr,prompt:=3)
'
' Execute the query
query = "select * from customer"
retcode = SQLExecQuery(connection,query)
'
' retrieve the first 50 rows with the first 6 columns of each row into
' the array destination, omit row numbers and put column names in the
' first row of the array
'
retcode = SQLRetrieve(connection:=connection,destination:=destination,columnNames:=1,rowNumbers:=0,maxRows:=50, maxColumns:=6,fetchFirst:=0)
' Get the next 50 rows of from the result set
retcode = SQLRetrieve(connection:=connection,destination:=destination,columnNames:=1,rowNumbers:=0,maxRows:=50, maxColumns:=6)
' Close the connection
retcode = SQLClose(connection)
End Sub
SQLGetSchema Example
This example opens the data source named "SblTest," gets the names in the ODBC data sources, and closes the connection.
Sub main
' Declarations
'
Dim outputStr As String
Dim connection As Long
Dim prompt As Integer
Dim datasources(1 To 50) As Variant
Dim retcode As Variant
Dim action1 as Integer
Dim qualifier as String
prompt = 5
' Open the datasource "SblTest"
connection = SQLOpen("DSN=SblTest", outputStr, prompt:=5)
action1 = 1 ' Get the names of the ODBC datasources
retcode = SQLGetSchema(connection:=connection,action:=1, qualifier:=qualifier, ref:=datasources())
' Close the datasource connection
retcode = SQLClose(connection)
SQLOpen Example
This example opens the data source named "SblTest," gets the names in the ODBC data sources, and closes the connection.
Sub main
' Declarations
'
Dim outputStr As String
Dim connection As Long
Dim prompt As Integer
Dim datasources(1 To 50) As Variant
Dim retcode As Variant
Dim action1 as Integer
Dim qualifier as String
prompt = 5
' Open the datasource "SblTest"
connection = SQLOpen("DSN=SblTest", outputStr, prompt:=5)
action1 = 1 ' Get the names of the ODBC datasources
retcode = SQLGetSchema(connection:=connection,action:=1, qualifier:=qualifier, ref:=datasources())
' Close the datasource connection
retcode = SQLClose(connection)
SQLRequest Example
This example will open the datasource SBLTESTW and execute the query specified by query and return the results in destination
Sub main
' Declarations
'
Dim destination(1 To 50, 1 To 125) As Variant
Dim prompt As integer
Dim retcode as Variant
Dim query as String
Dim outputStr as String
' The following will open the datasource SBLTESTW and execute the query
' specified by query and return the results in destination
'
query = "select * from class"
retcode = SQLRequest("DSN=SBLTESTW;UID=DBA;PWD=SQL",query,outputStr,prompt,0,destination())
End Sub作者: 大唐盛世 时间: 2004-9-14 19:09
SQLRetrieve 功能函数
在由connection指定的连接上获取待定查询结果并将结果返回到destination()数组里。
SQLRetrieve( connection& , destination() , maxColumns% , maxRows% , columnNames% , rowNumbers% , fetchFirst% )
语法:
参 数 解 释
connection& 长型long
destination() 2维变量数组
maxColumns% 整形,可选参数,用来指定在查询中取回的栏列数目
maxRows% 整形,可选参数,用来指定在查询中取回的行的数目
columnNames% 整形,可选参数,默认为0
rowNumbers% 整形,可选参数,默认为0
fetchFirst% 整形,可选参数,默认为0
注解:
返回值是结果集的行的数目或请求的最大行。如果函数不能在指定连接上获得结果,返回-1。如果没有发现数据,函数返回0。
参数是必需参数。返回变量。
如果maxColumns或maxRows被缺省,数组大小被用来确定获得的行列的最大数目,并返回整个结果集是一个尝试。通过再次使用SQLRetrieve和把fetchFirst设置为0,额外行可以被获得。如果maxColumns指定比结果中可用的更少的列,SQLRetrieve抛弃右边结果列只到结果与指定大小相适合。
当columnNames是非0,数组的第1行将放置数据库计划(database schema)指定的列名称。 当 rowNumbers是非0,行数目返回到destination()的第1列。SQLRetrieve将清空用户的数组来获得结果。
当fetchFirst 是非0,它将结果重新配置到第一行,前提是如果数据库支持此功能。如果数据库不支持此功能,结果设置 –1错误被返回。
如果结果集有比可以被destination()数组包含还多的行或比用maxRows请求还多的行,用户可以重复调用SQLRetrieve只到返回值为0为止。
SQLRetrieve Example
This example retrieves information from a data source.
Sub main
' Declarations
'
Dim connection As Long
Dim destination(1 To 50, 1 To 125) As Variant
Dim retcode As long
Dim query as String
Dim outputStr as String
connection = SQLOpen("DSN=SblTest",outputStr,prompt:=3)
'
' Execute the query
query = "select * from customer"
retcode = SQLExecQuery(connection,query)
' retrieve the first 50 rows with the first 6 columns of each row into
' the array destination, omit row numbers and put column names in the
' first row of the array
' Get the next 50 rows of from the result set
retcode = SQLRetrieve(connection:=connection,destination:=destination,columnNames:=1,rowNumbers:=0,maxRows:=50, maxColumns:=6)
' Close the connection
retcode = SQLClose(connection)
End Sub
SQLRetrieveToFile Example
This example opens a connection to a data source and retrieves information to a file.
Sub main
' Declarations
'
Dim connection As Long
Dim destination As String
Dim retcode As Long
Dim query as String
Dim outputStr as String
Dim filename as String
Dim columnDelimiter as String
'
' Open the connection
connection = SQLOpen("DSN=SblTest",outputStr,prompt:=3)
' Execute the query
'
query = "select * from customer"
retcode = SQLExecQuery(connection,query)
' Place the results of the previous query in the file named by
' filename and put the column names in the file as the first row.
' The field delimiter is %
'
filename = "c:\myfile.txt"
columnDelimiter = "%"
retcode = SQLRetrieveToFile(connection:=connection,destination:=filename, columnNames:=1,columnDelimiter:=columnDelimiter)
retcode = SQLClose(connection)
End Sub作者: hxf 时间: 2004-9-17 17:13
谢谢作者: hawkhao 时间: 2004-9-23 14:51
thank you very much作者: zhangxiumei 时间: 2004-10-14 14:53
谢谢作者: yang 时间: 2004-10-15 15:38
不错呀。呵呵。