The EXECUTE command uses the specified connection object to pass any SQL statement to a remote SAND database for execution. The connection object (containing the database name, authorization credentials, and nucleus.ini connection section information) must have been defined prior to the issuing of the EXECUTE statement.
Required Privileges
The user authorization defined in the connection object must possess the privileges required to execute the SQL statement on the remote database.
Syntax
SQL command
The SQL statement (delimited by double colons "::") that will be passed unchanged to the remote database for execution. The SQL statement must not include a terminating semicolon (";"), nor can it be contained between any quotation marks. Parentheses—that is, "(...)"—are the only type of brackets that can surround the embedded SQL statement.
connection
The connection object used to connect to the remote database. This connection object (defined via the CREATE CONNECTION command) must already exist in the local database.
Description
The EXECUTE command sends a complete SQL statement, enclosed between double-colon delimiters (::), to a remote database, where it will be executed. The database name, user authorization/password, and nucleus.ini connection section used to access the remote database are provided by the connection object specified in the EXECUTE statement.
In practice, the EXECUTE command offers an alternative to establishing a user session on a remote database via the nisqlm .SESS CONNECT command and then executing the SQL directly. It also provides a way to administer a remote database from the local server, if the administrator is not using nisqlm.
Any single legal SQL statement can be passed to the remote database via the EXECUTE command. The SQL statement is a "pass-through" command. It is executed as is at the remote database, and there is no validation of the SQL statement locally. If there is a remote execution error, this will generate error state Q0200 ("Remote database error"), along with the remote error message.
All pass-through SQL commands that return row information (SELECT, CREATE TABLE...SELECT, INSERT, INSERT...SELECT, and DELETE) will display the same "n rows affected" message locally. A pass-through SELECT statement will not return the actual records selected to the local client; it will just state the number of records returned by the command, similar to SELECT COUNT. A pass-through SELECT COUNT statement, on the other hand, will always return "1 row affected" regardless of how many rows there are in the queried table (the one row represents the remote text output that mentions the number of rows returned by the query).
Any remote changes made through EXECUTE statements can be saved by issuing the COMMIT [WORK] command locally, which will also preserve any changes made to the local database in the current transaction. To save only the changes to a remote database, the COMMIT [WORK] command should be embedded within an EXECUTE statement that targets the specific database. Similarly, the ROLLBACK [WORK] command can be used to erase changes in the current transaction to the local and remote database(s), or ROLLBACK [WORK] within an EXECUTE statement can erase the changes from a specific remote database.
Users should note that all objects referenced in the embedded SQL statement must be in the context of the remote database, not the local one.
Examples