Embedded SQL Quick Reference

The following reference provides basic syntax descriptions and examples of embedded SQL statements. Refer to your database or database driver’s documentation for a more detailed explanation of these statements, and to determine the full range of SQL statements that are available to you for your specific data source.

ALTER TABLE

Command:

ALTER TABLE

Syntax:

ALTER TABLE <table-name> ADD

<column1> <sql-data-definition>

Description:

Changes some component of a database table.

Example Usage:

EXEC SQL

alter table customer add

balance decimal(6,2)

END-EXEC.

CLOSE

Command:

CLOSE

Syntax:

CLOSE <cursor-name>

Description:

Closes a cursor defined by a DECLARE command.

Example Usage:

EXEC SQL

close cust_cursor

END-EXEC.

COMMIT

Command:

COMMIT

Syntax:

COMMIT

Description:

Commits the most recent changes to a database.

Example Usage:

EXEC SQL

commit

END-EXEC.

CREATE INDEX

Command:

CREATE INDEX

Syntax:

CREATE INDEX <index-name> ON <table-name> (<column1>,…<columnX>)

Description:

Creates an index for a database table.

Example Usage:

EXEC SQL

create index cust_index on customer (firstname)

END-EXEC.

CREATE TABLE

Command:

CREATE TABLE

Syntax:

CREATE TABLE <table-name>

( <column1> <sql-data-definition>,

<column2> <sql-data-definition>,

:

<columnX> <sql-data-definition> ),

Description:

Creates a table inside a database.

Example Usage:

EXEC SQL

create table customer

( firstname varchar(20),

lastname varchar(20),

description varchar(50))

END-EXEC.

DECLARE

Command:

DECLARE

Syntax:

DECLARE <cursor-name> CURSOR FOR

SELECT <column1>,

<column2>,

:

<columnX>

FROM <table-name>

WHERE <condition>

Description:

Defines a result set to be traversed with the FETCH command.

Example Usage:

EXEC SQL

declare cust_cursor cursor for

select firstname, dollar_amount

from customer

order by firstname

END-EXEC.

DELETE

Command:

DELETE

Syntax:

DELETE FROM <table-name>

<condition>

Description:

Deletes a row from a database table.

Example Usage:

EXEC SQL

delete from customer

where firstname = 'dean6'

END-EXEC.

DROP INDEX

Command:

DROP INDEX

Syntax:

DROP INDEX <index-name> ON <table-name>

Description:

Removes an index for a table from the database.

Example Usage:

EXEC SQL

drop index cust_index on customer

END-EXEC.

DROP TABLE

Command:

DROP TABLE

Syntax:

DROP TABLE <table-name>

Description:

Removes a table from a database.

Example Usage:

EXEC SQL

drop table customer

END-EXEC.

FETCH

Command:

FETCH

Syntax:

FETCH [ {NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {<int-constant> | <cobolscript-host-variable> }

| RELATIVE {<int-constant> | <cobolscript-host-variable> }} ]

<cursor-name> INTO host-variable [,...]

Description:

Retrieves data from a single row in a result set defined by a DECLARE command.

In most databases, the end-of-cursor state is signified by a sqlstate value of `S1010`. Check for this sqlstate value (or, for sqlstate NOT = `00000`) to gracefully terminate a FETCH loop.

Example Usage:

EXEC SQL

fetch relative :row-position cust_cursor

into :customer-first-name, :customer-dollar-amount

END-EXEC.

INSERT

Command:

INSERT

Syntax:

INSERT INTO <table-name>

VALUES ( <literal | cobolscript-host-variable>,

:

<literal | cobolscript-host-variable>)

Description:

Insert data from host cobolscript variables or literals into a database table.

Example Usage:

EXEC SQL

insert into customer

values (:customer-first-name,

:customer-last-name,

:customer-description) END-EXEC.

OPEN

Command:

OPEN

Syntax:

OPEN <cursor-name>

Description:

Opens a cursor defined by a DECLARE command.

Example Usage:

EXEC SQL

open cust_cursor

END-EXEC.

ROLLBACK

Command:

ROLLBACK

Syntax:

ROLLBACK

Description:

Rollback or undo the most recent changes to a database.

Example Usage:

EXEC SQL

rollback

END-EXEC.

SELECT

Command:

SELECT

Syntax:

SELECT <column1>,

<column2>,

:

<columnX>

INTO <:cobolscript-host-variable1>,

<:cobolscript-host-variable2>,

:

<:cobolscript-host-variableX>

FROM <table-name>

WHERE <condition>

Description:

Retrieves data from a table and places it in host cobolscript variables.

Example Usage:

EXEC SQL

select firstname, lastname, description

into :customer-first-name,

:customer-last-name ,

:customer-description

from customer

where firstname = 'dean8 '

END-EXEC.

UPDATE

Command:

UPDATE

Syntax:

UPDATE <table-name>

SET <column1> = <literal | :cobolscript-host-variable>

:

<condition>

Description:

Updates a column or columns in a table.

Example Usage:

EXEC SQL

update customer

set description = 'update test again'

where firstname = :customer-first-name and

lastname = :customer-last-name

END-EXEC.