SQL Statement Summary

This appendix lists the SQL statements supported by Ingres Star.

For syntax information, see the SQL Reference Guide. For syntax information for Enterprise Access statements, see the OpenSQL Reference Guide.

Previous Topic

Next Topic

Statements Supported by Ingres Star

Ingres Star supports the following statements.

The tables referred to in these statements are Ingres Star-level objects. Ingres Stars group and role name processing (the -G and -R flags).

Previous Topic

Next Topic

Begin Declare Section

The Begin Declare Section begins a program section that declares host variables to embedded SQL. This is a preprocessor directive.

Previous Topic

Next Topic

Call

This Call statement calls the operating system or an Ingres subsystem.

Previous Topic

Next Topic

Commit

An Ingres Star transaction allows reads to any number of local databases. It allows any updates to any number of local databases providing the local databases updated are managed by non-cluster Ingres DBMSs. If cluster installation Ingres or Enterprise Access products are updating, only one local database can be updated in an Ingres Star transaction.

Ingres Star uses a timeout mechanism to detect any deadlock that may occur between Ingres Star transactions. You must specifically set timeouts to have timeouts of distributed deadlocks.

Previous Topic

Next Topic

Connect

Connects the application to a database and, optionally, to a specified distributed transaction.

Previous Topic

Next Topic

Copy

The copy statement copies data from a file to a table or vice versa. You may not copy an index or view. Also some Enterprise Access products may not support the copy statement.

Previous Topic

Next Topic

Create Link

The create link statement is provided for compatibility with earlier versions of Ingres Star. The register statement is the preferred method for registering local database objects in a distributed database.

Previous Topic

Next Topic

Create Table

The create table statement simultaneously creates new tables in a local database and registers them in the distributed database. The table is deleted with the drop statement.

Previous Topic

Next Topic

Create View

The create view statement creates a Ingres Star-level distributed view. The view may be defined on other Ingres Star-level objects. Views created with create view are deleted with the drop statement.

Previous Topic

Next Topic

Cursor Statements

The Ingres Stars these cursor statements:

Previous Topic

Next Topic

Declare Table

The declare table statement describes the structure of a database table. This is a preprocessor directive.

Previous Topic

Next Topic

Delete

The delete statement deletes rows from a table or a view.

Previous Topic

Next Topic

Direct Connect

The direct connect statement enables you to connect to an Ingres local DBMS or Enterprise Access directly using Ingres Star in pass-through mode.

Previous Topic

Next Topic

Direct Disconnect

The direct disconnect statement enables you to leave the pass-through mode enabled by a previous direct connect statement.

Previous Topic

Next Topic

Direct Execute Immediate

The direct execute immediate statement sends a local DBMS-specific statement to a local DBMS.

Previous Topic

Next Topic

Disconnect

The disconnect statement terminates access to the database.

Previous Topic

Next Topic

Drop

The drop statement removes objects from the distributed database. Only objects created through Ingres Star can be dropped.

Whenever you use the drop statement, you always drop the registration and the underlying locally-stored object. For example, if you use the drop table statement, the underlying table and its data are destroyed. When an Ingres Star-level object is deleted with the drop statement, all views on it also are recursively dropped.

Previous Topic

Next Topic

Drop Link

The drop link statement is provided for compatibility with earlier versions of Ingres Star.

Previous Topic

Next Topic

Drop Table

The drop table statement removes tables from the distributed database. Any underlying table in a local database also is dropped.

Previous Topic

Next Topic

Drop View

The drop view statement removes Ingres Star-level views from the distributed database.

Previous Topic

Next Topic

Dynamic SQL

Dynamic SQL compiles and executes SQL queries at run time.

The following statements are supported in the Ingres Star:

Note: You must embed these statements in an application program; you cannot use them interactively.

Previous Topic

Next Topic

End Declare Section

The end declare section ends declaration of host variables. This is a preprocessor directive.

Previous Topic

Next Topic

Endselect

The Endselect statement terminates a select loop.

Previous Topic

Next Topic

Execute Immediate

The execute immediate statement executes an SQL statement specified as a string literal or in a host language variable.

Previous Topic

Next Topic

Execute Procedure

The execute procedure statement invokes a database procedure.

Previous Topic

Next Topic

Help

A Terminal Monitor statement that displays information about SQL, about the help function itself, or about objects in a database. This statement has these variants:

help [[owner.]objectname {, [owner.]objectname}]
help comment column [owner.]table columnname {, columnname}
help comment table [owner.]table {, [owner.]table }
help default [owner.]tablename
help help
help index [owner.]indexname {, [owner.]indexname}
help procedure [owner.]procedure_name
{, [owner.]procedure_name}
help register [owner.]objectname
help sql [sql_statement]
help table [owner.]tablename {, [owner.]tablename}
help view [owner.]viewname {, [owner.]viewname}

Previous Topic

Next Topic

Include

The include statement includes an external file in source code.

Previous Topic

Next Topic

Inquire_sql

The Inquire_sql statement provides runtime information.

Previous Topic

Next Topic

Insert

The insert statement Inserts rows into a table or view in the distributed database.

The column names specified in the statement are the Ingres Star-level column names. The Ingres Star-level column names may be different than the column names in the local DBMS.

Previous Topic

Next Topic

Register As Link

The register As link statement registers existing local database tables, views, and database procedures in a distributed database.

Previous Topic

Next Topic

Register As Link With Refresh

The Register As Link with Refresh statement updates Ingres Star catalogs when schema or related table information changes in a local table that is part of a distributed database.

Previous Topic

Next Topic

Remove

The remove statement removes registrations of tables, views, and procedures from the distributed database previously registered with the register as link statement.

Removes registrations of tables created with the create table statement at the Ingres Star level. When registration of an Ingres Star object is removed with the remove statement, all views on it also are recursively dropped.

Previous Topic

Next Topic

Repeat Queries

The repeat queries statement compiles a query for repeated execution during a session.

Note: Repeat queries are accepted syntactically by a Star Server but may not result in enhanced performance. If more than one local database is involved, performance will even be degraded. For this reason, repeat should be used sparingly.

Previous Topic

Next Topic

Rollback

The rollback statement rolls back part or all of the current Ingres Star transaction. Rollback to a savepoint is applicable only to an Ingres Star transaction that involves only Ingres local databases.

Previous Topic

Next Topic

Savepoint

The savepoint statement declares a savepoint within a transaction.

This statement only applies to transactions that only involve Ingres local databases. Note that some Enterprise Access products do not support the savepoint statement.

Previous Topic

Next Topic

Select

The select statement retrieves data from one or more tables, views, or indexes.

Previous Topic

Next Topic

Set

The set statement sets an option for the Ingres session, or sets an option for the tables in the distributed database.

If the set option is on a session, the option is sent to each local DBMS currently connected to Ingres Star. Ingres Star also saves the option and sends it to any local DBMS subsequently connected to Ingres Star. Note that the session option cannot be used in a Ingres Star database to alter the lockmode, other than to set the lock timeout. Statements such as "SET LOCKMODE SESSION WHERE READLOCK=NOLOCK" are not supported.

If the set option is on a table, the option is sent to the local DBMS that holds the underlying table.

The set statement has these variants supported by Ingres Star:

Previous Topic

Next Topic

Set_sql

The set_sql statement sets a variety of session characteristics.

Previous Topic

Next Topic

Update

The update statement updates the values of columns in a table or a view in the distributed database.

Only table names or view names may be specified with this statement.

Previous Topic

Next Topic

Whenever

The whenever statement performs an action when a specified condition becomes true. This is a preprocessor directive.


© 2007 Ingres Corporation. All rights reserved.