SQL.RETRIEVE

Macro Sheets Only

Retrieves all or part of the results from a previously executed query. The connection used must have already been established using the macro function SQL.OPEN. Also, a query must already have been executed using SQL.EXEC.QUERY and results must be pending.

If this function is not available, you must install the Microsoft ODBC add-in (XLODBC.XLA).

Syntax

SQL.RETRIEVE(connection_num, destination_ref, max_columns, max_rows, col_names_logical, row_nums_logical, named_rng_logical, fetch_first_logical)

Connection_num is the unique connection ID for a data source. The data source specified must have pending query results. Pending query results are generated by a call to SQL.EXEC.QUERY on the same connection.

• If there are no pending results on the connection SQL.RETRIEVE returns the #N/A error value.
• If connection_num is not valid, SQL.EXEC.QUERY returns the #VALUE! error value.

Destination_ref specifies where the results should be placed. It is either a reference to a single cell or it is omitted.

• If destination_ref refers to a single cell then SQL.RETRIEVE will return all of the pending results in the cells to the right, below, and including destination_ref. This is the same convention used in Microsoft Excel when multiple cells are pasted into a single-cell selection. Any previous values contained in the destination cells will be overwritten without confirmation.
• If destination_ref is omitted then the bindings established by previous calls to SQL.BIND will be used to return results. If no such bindings exist for the current connection then SQL.RETRIEVE will return the #REF! error value. If a particular result column has not been bound then its results will be discarded. Max_rows specifies the number of rows that will be returned under each bound column. The first row of results will be placed in the bound cell and any additional rows will be placed in the rows immediately under the bound cell.

Max_columns is the maximum number of columns to be retrieved. It is only used when destination_ref is not omitted.

• If max_columns specifies more columns than are available in the results, SQL.RETRIEVE will place data in the columns for which data is available and clear the additional columns.
• If max_columns specifies fewer columns than are available in the results, the rightmost result columns will be discarded to fit the chosen size. Column position will be determined by the order in which the data source returned them.
• If max_columns is omitted then all of the result columns will be returned.

Max_rows is the maximum number of rows to be returned.

• If max_rows specifies more rows than are available in the results, SQL.RETRIEVE will place data in the rows for which data is available and clear the additional rows.
• If max_rows specifies fewer rows than are available in the results, SQL.RETRIEVE will place data in the selected rows but will not discard the additional rows. These extra rows can be retrieved via additional calls to SQL.RETRIEVE. This process is described in the fetch_first_logical argument description.
• If max_rows is omitted then all rows in the result set will be returned.

Col_names_logical is a logical value which, if TRUE, causes the column names to be returned as the first row of results. It FALSE or omitted, the column names will not be returned.

Row_nums_logical is used only when destination_ref is included. If row_nums_logical is TRUE then the first column in the result set will contain row numbers. If FALSE then row numbers will not be returned. This column of row numbers will not have a column name and the column heading will be left blank. Row numbers can also be retrieved by binding column number 0 with SQL.BIND.

Named_rng_logical is a logical value which, if TRUE, sets each column of results to be declared as a named range on the worksheet. The name of the each range will be the result column name. The named range will only include the rows that were fetched with this SQL.RETRIEVE function call. If FALSE, the results will not be declared as a named range.

Fetch_first_logical is a logical value that allows you to request results from the beginning of the result set.

• If the first call to SQL.RETRIEVE did not return all of the rows in the result set then SQL.RETRIEVE may be called again to return the next set of rows. This process can be repeated until no more result rows are available, at which time SQL.RETRIEVE will return the value 0 (zero). This will not halt the running of the macro. During each of these calls, including the first call, fetch_first_logical should be set to FALSE.
• If you want to move the cursor back to the beginning of the result set then fetch_first_logical should be set to TRUE. This causes the same SQL query text to be executed again on the data source. The cursor will then be positioned at the top of the result set and SQL.RETRIEVE will fill destination_ref beginning with the first row of results. Further calls to SQL.RETRIEVE, for the purpose of retrieving additional rows, can then be made with fetch_first_logical set to FALSE .

Remarks

• Before calling SQL.RETRIEVE a connection must be established with a data source using SQL.OPEN.
• If SQL.RETRIEVE is unable to retrieve the results on the specified data source then an error value will be returned. In such a case SQL.RETRIEVE will place error information in memory for the SQL.ERROR function, if such information is available.
• If SQL.RETRIEVE is able to successfully return rows of results on the specified connection it will return the number of rows that were actually returned. If there were no results pending on the connection then SQL.RETRIEVE will return the #N/A error value.If no data was found then SQL.RETRIEVE returns 0 (zero).
• A successful call to SQL.OPEN returns a unique connection ID number, which is used in a call to SQL.EXEC.QUERY to send a SQL language query. Following this call to SQL.EXEC.QUERY, SQL.RETRIEVE uses the same connection ID number to retrieve query results.

Example

SQL.RETRIEVE(conn1,sheet1!C1,1) stores data obtained from the data source conn1 on Sheet1 from left to right in cell C1, using only column 1.

Related Functions

SQL.OPEN Establishes a connection with a data source

SQL.EXEC.QUERY Sends a query to a data source

SQL.BIND Specifies storage for a result column

SQL.RETRIEVE.TO.FILE Retrieves query results and places them in a file

SQL.GET.SCHEMA Gets information about a connected data source.

SQL.CLOSE Close a data source connection

SQL.ERROR Returns detailed error information

List of Command-Equivalent Functions