Syntax

SQLRetrieve(ID,array[,[maxcolumns] [,[ maxrows] [,[isColumnNames] [, isFetchFirst]]]])

Description

Retrieves the results of a query.

Comments

This function is called after a connection to a data source is established, a query is executed, and the desired columns are bound. The following table describes the parameters to the SQLRetrieve function:

Parameter

Description

ID

Long identifying a valid connected data source with pending query results.

array

Two-dimensional array of variants to receive the results. The array has x rows by y columns. The number of columns is determined by the number of bindings on the connection.

maxcolumns

Optional Integer expression specifying the maximum number of columns to be returned. If maxcolumns is greater than the number of columns bound, the additional columns are set to empty. If maxcolumns is less than the number of bound results, the rightmost result columns are discarded until the result fits.

maxrows

Optional Integer specifying the maximum number of rows to be returned. If maxrows is greater than the number of rows available, all results are returned, and additional rows are set to empty. If maxrows is less than the number of rows available, the array is filled, and additional results are placed in memory for subsequent calls to SQLRetrieve.

isColumnNames

Optional Boolean specifying whether column names should be returned as the first row of results. The default is FALSE.

isFetchFirst

Optional Boolean expression specifying whether results are retrieved from the beginning of the result set. The default is False.

Before you can retrieve the results from a query, you must (1) initiate a query by calling the SQLExecQuery function and (2) specify the fields to retrieve by calling the SQLBind function.

This function returns a Long specifying the number of rows available in the array.

The Basic Control Engine generates a runtime error if SQLRetrieve fails. Additional error information is placed in memory.

Example

This example executes a query on the connected data source, binds columns, and retrieves them.

Sub Main()
  Dim b() As Variant
  Dim c() As Variant
  id& = SQLOpen("DSN=SAMPLE",,3)
  qry& = SQLExecQuery(id&,"Select * From c:\sample.dbf")
  i% = SQLBind(id&,b,3)
  i% = SQLBind(id&,b,1)
  i% = SQLBind(id&,b,2)
  i% = SQLBind(id&,b,6)
  l& = SQLRetrieve(id&,c)
  For x = 0 To Ubound(c)
    For y = 0 To Ubound(b)
      MsgBox c(x,y)
    Next y
  Next x
  id& = SQLClose(id&)
End Sub

See Also

SQLOpen (function); SQLExecQuery (function); SQLClose (function); SQLBind (function); SQLRetrieveToFile (function).

S