Introducing SQL
SQL Functionality
SQL statements enable you to:
• Manipulate database objects—Create, modify, and destroy a variety of database objects, such as tables, views, indexes, and database procedures.
• Manipulate data—Select, insert, update, and delete data in database tables.
• Manage groups of statements as transactions—Process a group of database statements as a single transaction. Transaction management includes the ability to undo (roll back) a transaction, either in whole or in part.
• Perform other database management functions—Set runtime options, copy data between tables and files, modify the characteristics of a database table, and perform many other database management functions.
Types of SQL Statements
SQL statements are categorized according to the task performed:
Data Definition Language (DDL)
Creates or deletes objects such as tables, indexes, and database procedures.
Data Manipulation Language (DML)
Allows data manipulation in tables.
Interactive and Embedded SQL
SQL statements come in two releases:
Interactive SQL
SQL statements are entered from a terminal and query results display on the terminal screen.
Embedded SQL
SQL statements can be included in programming languages such as C or Fortran.
Interactive SQL
Interactive SQL statements are entered through the Terminal Monitor.
Line-Based Terminal Monitor
The line-based Terminal Monitor accepts SQL statements in a line-oriented style. The line-based Terminal Monitor is invoked by typing sql at the operating system prompt.
You can use the statement Help SQL to display information about SQL statements, tables, views, and other database objects.
For a complete discussion of the line-based Terminal Monitor, see the appendix “Terminal Monitor.”
Forms Based Terminal Monitor
The forms-based Terminal Monitor accepts SQL statements in a screen-oriented style. The forms based Terminal Monitor is invoked by typing isql at the operating system prompt.
Embedded SQL
Embedded SQL statements can be embedded in a procedural (3GL) programming language. The procedural language is referred to as the host language.
Embedded SQL Support
Embedded SQL is supported in the following host languages:
Windows:
• C
• C++
• COBOL
• Fortran
UNIX:
• C
• C++
• COBOL
• Fortran
• Verdix Ada
VMS:
• C
• C++
• BASIC
• COBOL
• Fortran
• Pascal
• Ada
How Embedded SQL Differs From Interactive SQL
Embedded SQL statements can be mixed with the full range of host language statements and provide your applications with full access to Ingres databases. The statements available in embedded SQL include those available in interactive SQL; embedded SQL, however, differs from interactive SQL in the following ways:
• Use of Host Language Variables - Embedded SQL allows host variables to be used in place of many syntactic elements.
• Error and Status Handling - In interactive SQL, error and status messages are sent directly to the terminal screen. Embedded SQL stores error and status information in a data structure called the SQL Communications Area (SQLCA).
• Cursors - To enable an application to process the result of a query one row at a time, embedded SQL provides cursor versions of the data manipulation statements SELECT, UPDATE, and DELETE. A database cursor points to the row currently being processed by the application.
• Forms Statement - Embedded SQL allows the creation of applications based on forms that have been created through Visual-Forms-Editor (VIFRED). Using forms statements, your application can:
• Display VIFRED forms
• Transfer data from the form to the database, and vice-versa
• Respond to user actions (such as menu selections, control keys, and function keys)
• Validate user entries
• Display help screens
• Dynamic Programming - Embedded SQL allows you to create and execute statements dynamically, specifying portions of SQL statements in program variables at runtime.
The dynamic programming feature of embedded SQL allows you to specify tables, columns, and queries at runtime. Dynamic programming allows generic applications to be written that can be used with any table. Details can be found in Dynamic Programming in the chapter “Embedded SQL.”
• Multiple Sessions - An embedded SQL application can use multiple sessions to connect to different databases or to establish multiple connections to the same database.
• Additional Database Access Statements - Embedded SQL includes several statements not available in interactive SQL. For example, there are embedded statements that allow your application to connect to a particular database and to manipulate cursors.