EXECUTE Statement

EXECUTE Statement

Sends DBMS-specific, nonquery SQL statements to the DBMS.

Optional statement

Syntax
Arguments
Useful Statements to Include in EXECUTE Statements
Special Informix Considerations
Examples

Syntax

EXECUTE (DBMS-specific SQL-statement) BY dbms-name | alias;

specifies a dynamic, DBMS-specific SQL statement that does not select data. This argument is required and must be enclosed in parentheses. The statement is case sensitive and is passed to the DBMS exactly as you type it.

Note: If you use quotes in your Informix SQL Pass-through statements, your DELIMIDENT environment variable must be set to DELIMIDENT=YES, or your statements will be rejected by Informix. [cautionend]

Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement executes.

dbms-name

identifies the database management system to which you direct the DBMS-specific SQL statement, in this case, informix. The keyword BY must appear before the dbms-name argument. You must specify either a DBMS name or an alias in the EXECUTE statement.

alias

specifies an alias that was defined in the CONNECT statement. (You cannot use an alias if the CONNECT statement was omitted.)

The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS and processes those statements.

In some SAS/ACCESS interfaces, you can issue an EXECUTE statement directly without first connecting to a DBMS. If you omit the CONNECT statement, an implicit connection is performed (by using default values for all connection arguments) when the first EXECUTE statement is passed to the DBMS.

The EXECUTE statement cannot be stored as part of a Pass-Through query in a PROC SQL view.

Useful Statements to Include in EXECUTE Statements

This section lists some of the statements that you can pass to the DBMS by using the Pass-Through facility's EXECUTE statement.

Note: The statements passed using the EXECUTE statement cannot contain a semicolon (;) because to SAS software a semicolon represents the end of a statement. [cautionend]

CREATE: creates a DBMS table, view, index, or other DBMS objects, depending on how the statement is specified.
DELETE: deletes rows from a DBMS table.
DROP: deletes a DBMS table, view, or other DBMS objects, depending on how the statement is specified.
GRANT: gives users the authority to access or modify objects such as tables or views.
INSERT: adds rows to a DBMS table.
REVOKE: revokes the access or modifies privileges that were given to users by the GRANT statement.
UPDATE: modifies the data in columns of a row in a DBMS table.

For more information and restrictions on these and other SQL statements, see your Informix SQL documentation.

Special Informix Considerations

The Pass-Through Facility recognizes two types of stored procedures in Informix that perform only database functions. The method for executing the two types of stored procedures is different.

Procedures that return no values to the calling application:
Stored procedures that do not return values can be executed directly by using the Informix SQL EXECUTE statement. Stored procedure execution is initiated with the Informix EXECUTE PROCEDURE statement. The following example executes the stored procedure make_table. The stored procedure has no input parameters and returns no values.
```
execute (execute procedure make_table())
   by informix;
```
Procedures that return values to the calling application:
Stored procedures that return values must be executed by using the PROC SQL SELECT statement with a CONNECTION TO component. The following example executes the stored procedure read_address, which has one parameter, "Putnum".
The values that are returned by read_address serve as the contents of a virtual table for the PROC SQL SELECT statement.
```
select * from connection to informix
        (execute procedure read_address ("Putnum"));
```
For example, when you try to execute a stored procedure that returns values from a PROC SQL EXECUTE statement, you get the following error message:
```
execute (execute procedure read_address
   ("Putnum")) by informix;

ERROR: Informix EXECUTE Error: Procedure
   (read_address) returns too many values.
```

Examples

The following example grants UPDATE and INSERT authority to user gomez on the Informix ORDERS table. Because the CONNECT statement is omitted, an implicit connection is made that uses a default value of informix as the connection alias and default values for the DATABASE and SERVER arguments. Informix is a case-sensitive database; therefore, the database object ORDERS is in uppercase, as it was created.

proc sql;
   execute (grant update, insert on ORDERS
   to gomez) by informix;
quit;

The next example connects to Informix and drops (that is, removes) the table tempdata from the stores7 database. The alias temp5 that is specified in the CONNECT statement is used in the EXECUTE statement's BY clause.

proc sql;
   connect to informix as temp5
   (database='//online/stores7');
   execute (drop table tempdata) by temp5;
   disconnect from temp5;
quit;

Chapter Contents
Previous
Next
Top of Page

EXECUTE Statement

Syntax

Arguments