Database tests are supported via ODBC using the following functions:
SQLOpen, SQLClose, SQLError, SQLRetrieve, SQLRetrieveToFile, SQLExecQuery, SQLGetSchema and SQLRequest.
You can carry out cursor type operations by incrementing arrays of returned datasets.
All SQL queries are supplied as a string. You can execute stored procedures for instance on SQL Server you could use “Exec MyStoredProcedure” and as long as that stored procedure is registered on the SQL Server database then it should execute however you cannot interact as much as you may like by supplying say in/out variables, etc but for most instances it will cover your database test requirements
A sample database test could look like this:
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
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
No comments:
Post a Comment