Data Manipulation Statements

Data manipulation statements let you access and modify the contents of your database. Pervasive.SQL allows you to construct data manipulation statements to do the following:

Retrieve data from tables.

Modify data in tables.

Define transactions.

Create and delete views.

Execute stored procedures.

Execute triggers.

The following sections briefly describe the SQL statements associated with each of these tasks.

Retrieving Data

All statements you use to retrieve information from a database are based on the SELECT statement.

Table 1-9 Data Manipulation Statements-Retrieving Data


SELECT	Retrieves data from one or more tables in the database.

When you create a SELECT statement, you can use various clauses to specify different options. (See the entry for the SELECT statement in ODBC Engine Reference for detailed information about each type of clause.) The types of clauses you use in a SELECT statement are as follows:

Table 1-10 Data Manipulation Statements-Retrieving Data Options


FROM	Specifies the tables or views from which to retrieve data.
WHERE	Defines search criteria that qualify the data a SELECT statement retrieves.
GROUP BY	Combines sets of rows according to the criteria you specify and allows you to determine aggregate values for one or more columns in a group.
HAVING	Allows you to limit a view by specifying criteria that the aggregate values of a group must meet.
ORDER BY	Determines the order in which Pervasive.SQL returns selected rows.

In addition, you can use the UNION keyword to obtain a single result table from multiple SELECT queries.

Modifying Data

You can add, change, or delete data from tables and views by issuing statements such as the following:

Table 1-11 Data Manipulation Statements-Modifying Data


INSERT	Adds rows to one or more tables or a view.
UPDATE	Changes data in a table or a view.
DELETE	Deletes rows from a table or a view.

When you create a DELETE or UPDATE statement, you can use a WHERE clause to define search criteria that restrict the data upon which the statement acts.

Defining Transactions

To update the data in a database, you can issue SQL statements individually or you can define transactions (logical units of related statements). By defining transactions, you can ensure that either all the statements in a unit of work are executed successfully or none are executed. You can use transactions to group statements to ensure the logical integrity of your database.

Pervasive.SQL supports the ODBC API SQLTransact. See the Microsoft ODBC Programmer's Reference for more information.

Creating and Deleting Views

You can create and delete views by constructing SQL using the following statements:

Table 1-12 Data Manipulation Statements-Views


CREATE VIEW	Defines a database view and stores the definition in the dictionary.
DROP VIEW	Deletes a view from the data dictionary.

Executing Stored Procedures

A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, construct statements using the following:

Table 1-13 Data Manipulation Statements- Stored Procedures


CALL or EXEC[UTE]	Recalls a previously compiled procedure and executes it.

Executing System Stored Procedures

A system stored procedure helps you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a psp_ prefix. To execute stored procedures, construct statements using the following:

Table 1-14 Data Manipulation Statements-System Stored Procedures


CALL or EXEC[UTE]	Recalls a system stored procedure and executes it.

For more details, see System Stored Procedures .

Executing Triggers

A trigger consists of statements you can precompile and save in the dictionary. Triggers are executed automatically by the engine when the specified conditions occur.