Syntax
SQLQueryExec(ConnectNum, Query$)
Remarks
Sends a query to a data source using an existing connection.
Note
Unlike SQLExecQuery, SQLQueryExec does not store the entire query result in memory. To avoid resource problems when creating complex database solutions using the ODBC add-in library, use SQLQueryExec.
Before calling SQLQueryExec, a connection must be established with a data source using SQLOpen. A successful call to SQLOpen returns a unique connection ID number. SQLQueryExec uses that connection ID number to send SQL language queries to the data source.
SQLQueryExec only executes a query; results generated from the query are
not returned. Retrieving results is handled by the SQLQueryFetch, SQLQueryRetrieve, SQLRetrieveColSize, and SQLSetRowPos functions. If SQLQueryExec is called using an existing connection ID number, all pending results on that connection will automatically be discarded. The connection ID will then refer to the new query and its results.
You cannot use SQLRetrieveItem$ or SQLRetrieveToDocument to return data from a SQLQueryExec instruction.
Argument | Explanation |
ConnectNum | The unique connection ID of the data source you want to query returned by a previously executed SQLOpen function. If ConnectNum is not valid, SQLQueryExec returns 0 (zero). |
Query$ | The SQL language query that is to be executed on the data source. The query should follow SQL grammar; the Help file for the appropriate ODBC driver also describes any SQL language limitations or modifications for the given DBMS. If SQLQueryExec is unable to execute Query$ on the specified data source, SQLQueryExec returns 0 (zero). The exact error can be obtained from the error functions. |
If SQLQueryExec is able to successfully execute the query on the specified connection, it will return one of three values, depending on the type of SQL statement that was executed.
SQL statement | Return value |
SELECT | The number of result columns available |
UPDATE, INSERT or DELETE | The number of rows affected by the statement |
Other | A positive value |
If there was an error executing the query, SQLQueryExec returns a negative error value.
Note
If SQLQueryExec successfully executes the query but the result of the query contains no data, the function will return 0 (zero). To determine whether this return value indicates an error or simply that the result contains no data, check for error messages with SQLCountErrors. If SQLCountErrors returns 0 (zero), then SQLQueryExec successfully executed the SQL query but the result is empty.
The following example sends a simple SELECT query to an established data source connection during asynchronous processing. While SQLQueryExec continues to return the value –2, the query is still being processed; the macro should wait for a final return value from SQLQueryExec before determining whether an error occurred.
ret = -2
While ret = -2
ret = SQLQueryExec(connect_num, "select * from authors")
Wend
If ret <= 0 Then Goto ParseErrors