User's Guide

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface

EXEC SQL ALL Embedded SQL statements must be preceded with EXEC SQL and end with a semicolon.

Embedded SQL commands are broken into two groups:

1. Standard SQL commands that are part of the SQL language. These commands are further broken into two groups, the data manipulation commands and the data definition commands. They are used by simply placing them in a C program enclosed with EXEC SQL and a semi-colon. CONNECT, DELETE, SELECT, SET and UPDATE have additional formats only available in Embedded SQL. The additional formats fall into the second category of Embedded SQL specific commands. All commands are described in detail in "Watcom-SQL Language Reference".
2. Data Manipulation Commands:

Statement	Description
CALL	Invoke a database procedure
CHECKPOINT	Cause the database engine to write all modified information
COMMIT	Commit changes to the database
DELETE	Delete rows from a database table
INSERT	Insert rows into database tables
PREPARE TO COMMIT	First phase of two-phase commit
RELEASE SAVEPOINT	Release a savepoint
ROLLBACK	Undo changes to database
ROLLBACK TO SAVEPOINT	Undo changes to database
SAVEPOINT	Establish a savepoint within a transaction
SELECT	Retrieve information from the database
UPDATE	Update rows in the database.

1. Data Definition Commands: :

   Statement	Description
   ALTER	Modify a database table definition
   COMMENT ON	Fill in remarks column for tables and columns
   CREATE	Create database objects
   DROP	Remove objects from the database
   GRANT	Create userids, grant privileges to users
   REVOKE	Delete userids, revoke privileges from users
   SET OPTION	Set database options
   VALIDATE TABLE	Check validity of table contents and all indexes on table

In addition to the standard SQL data manipulation and data definition commands, there are several SQL commands that are specific to Embedded SQL and can only be used in a C program. These Embedded SQL commands are also described in "Watcom-SQL Language Reference".

• CLOSE close a cursor
• CONNECT connect to the database
• Declaration Section declare host variables for database communication
• DECLARE declare a cursor
• DELETE (positioned) delete the row at the current position in a cursor
• DESCRIBE describe the host variables for a particular SQL statement
• DISCONNECT disconnect from database engine
• DROP STATEMENT free resources used by a prepared statement
• EXECUTE execute a particular SQL statement
• EXPLAIN explain the optimization strategy for a particular cursor
• FETCH fetch a row from a cursor
• GET OPTION get the setting for a particular database option
• INCLUDE include a file for SQL preprocessing
• OPEN open a cursor
• PREPARE prepare a particular SQL statement
• PUT insert a row into a cursor
• SET CONNECTION change active connection
• SET OPTION change a database option value
• SET SQLCA use an SQLCA other than the default global one
• UPDATE (positioned) update the row at the current location of a cursor
• WHENEVER specify actions to occur on errors in SQL statements