Direct Execute Immediate Statement
Use the direct execute immediate statement to send a local DBMS-specific statement to the local DBMS.
Ingres Star assumes that the statement being sent is an update operation to the local database. Ingres Star uses the two-phase commit protocol if the transaction involves an update to at least one other site.
The direct execute immediate statement has the following format:
direct execute immediate 'string_constant'
[with
[node = node_name,
database = database_name]
[, dbms =server_class]]
node = node_name
The Net defined vnode name (the virtual node name) of the remote node that holds the local database to which you want to connect. The default node is the current node. It can be delimited with double quotes.
If you specify the node = clause, you also must specify the database = clause.
database = database_name
The name of the local database to which you want to connect. The default database is the coordinator database. It can be delimited with double quotes.
If you specify the database = clause, you also must specify the node = clause.
The default for the database and node is the coordinator database on the current node.
dbms = server_class
The type of local DBMS that contains the local database. It can be delimited with double quotes.
The server_class must be Ingres or one of the SQL Enterprise Access products. If you do not specify a server class the default is the value in default_server_class on the remote installation (Ingres, unless defined otherwise.) Use the Configure Name Server screen of the CBF utility to view or change this value.
The with clause enables you to specify the node, database, and type of server to which you want to connect. No other with clauses are allowed when presented to Ingres Star.
Example: Direct Execute Immediate
The following example illustrates how you send a create integrity statement to a local DBMS to be executed using direct execute immediate:
create table employee (name char(100),
dept integer, salary money)
with node=remote1, database=mydb;
direct execute immediate 'create integrity
on employee is salary>0'
with node=remote1, database=mydb;
Direct Execute Immediate Statement Process
When the direct execute immediate statement is presented to Ingres Star, it strips off the direct keyword, the with clauses, and the quotes around the string_constant and sends the following statement to the local DBMS:
execute immediate string_constant
The local DBMS, possibly an Enterprise Access, strips off the execute immediate and then parses the query represented by string_constant.
If the query is a legal query for the execute immediate statement, it is executed. If the query is illegal, an error is returned.
Illegal Direct Execute Immediate Statements
The following statements are not allowed for direct execute immediate:
• Preprocessor directives:
begin declare section
declare
end declare section
include
whenever
• Cursor statements:
open
close
fetch
• Row returning statements:
select
endselect
• Non-database statements:
call
inquire_sql
set_sql
• Transaction statements:
commit
rollback
savepoint
set autocommit on
• Other statements:
connect
disconnect
describe
direct connect
direct disconnect
execute
execute immediate
help
prepare
repeat queries
register as link
Avoiding Execute Errors During Two-phase Commit
It has already been pointed out that the transaction statements commit and rollback are illegal to use for direct execution in an Ingres Star session. These statements must not be passed through Ingres Star for execution on a remote non-distributed database or Enterprise Access because these statements can interfere with two-phase commits. This means that you must not attempt to implement remotely through Ingres Star:
• A direct execute immediate statement to execute commit or rollback
• A registered procedure, or a direct execute immediate statement to execute a procedure, that contains commit or rollback
Ingres Star, under the latest release of Ingres, detects such errors and returns an error message without performing the operation. However, earlier versions of Ingres serving the remote non-distributed database or Enterprise Access may not detect such errors.
IMPORTANT! If a user's commit or rollback statement is passed through Ingres Star and executed by an earlier release of Ingres during two-phase commit, the two-phase commit protocol may be disrupted and could corrupt the database.