Bookshelf Home | Contents | Index | Search | PDF

Siebel VB Language Reference > Siebel VB Language Reference >

SQLRetrieve Function

---

This custom VB function fetches the results of a pending query on the connection specified by connection and returns the results in the destination() array.

NOTE:  Siebel VB SQLxxxx functions are supported for non-Unicode databases only.

Syntax

SQLRetrieve(connection, destination()[, maxColumns][, maxRows]
[, columnNames][, rowNumbers][, fetchFirst])

Argument	Description
connection	The long integer returned by the SQLOpen function
destination()	A two-dimensional array of type variant. The first index of the array cannot exceed 100.
maxColumns	The number of columns to be retrieved in the request; defaults to 0 if this parameter is not used
maxRows	The number of rows to be retrieved in the request; if this argument is not supplied, 0 is assumed
columnNames	An integer with a value of 0 or nonzero. When columnNames is nonzero, column names are returned as the first row of the ref() array. If columnNames is omitted, the default is 0.
rowNumbers	An integer with a value of 0 or nonzero. When rowNumbers is nonzero, row numbers are returned as the first row of the ref() array. If rowNumbers is omitted, the default is 0.
fetchFirst	A positive integer value that causes the result set to be repositioned to the first row of the database, if the database supports this action; returns -1 if this cannot be accomplished

Returns

A variant containing the following values:

Result	Returns
Success	The number of rows in the result set or the maxRows requested
Unable to retrieve results, or no results pending	-1
No data found by the query	0

Usage

If maxColumns or maxRows is omitted, the array size is used to determine the maximum number of columns and rows retrieved, and an attempt is made to return the entire result set. Extra rows can be retrieved by using SQLRetrieve again and by setting fetchFirst to 0. If maxColumns specifies fewer columns than are available in the result, SQLRetrieve discards the rightmost result columns until the results fit the specified size.

When columnNames is nonzero, the first row of the array is set to the column names as they are specified by the database schema. When rowNumbers is nonzero, row numbers are returned in the first column of destination(). SQLRetrieve clears the user's array prior to fetching the results.

When fetchFirst is nonzero, it causes the result set to be repositioned to the first row if the database supports the function. If the database does not support repositioning, the result set -1 error is returned.

If there are more rows in the result set than can be contained in the destination() array or than have been requested using maxRows, the user can make repeated calls to SQLRetrieve until the return value is 0.

CAUTION:  This function, as well as the other SQL functions available in Siebel VB, should not be used to query the underlying database. Use the Siebel Object Interfaces to query Siebel data. Use the SQL functions only to query non-Siebel data.

Example

This example retrieves information from a data source.

Sub Button_Click
' Declarations

    Dim connection As Long
    Dim destination(1 To 50, 1 To 125) As Variant
    Dim retcode As long

   ' 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

See Also

SQLClose Function
SQLError Function
SQLExecQuery Function
SQLGetSchema Function
SQLOpen Function
SQLRequest Function
SQLRetrieveToFile Function

Bookshelf Home | Contents | Index | Search | PDF