Table Deletion Example

The following example demonstrates the task of deleting rows from a newly-created table and then subsequetly deleting the table itself.

The first command in this example creates a table named SQLUser.WordPairs with three columns.

CREATE TABLE SQLUser.WordPairs (
    Lang        CHAR(2) NOT NULL,
    Firstword   CHAR(30),
    Lastword    CHAR(30))

The next few commands insert six records into the table.

INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','hello','goodbye')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','bonjour','au revoir')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('It','pronto','ciao')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','oui','non')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','howdy','see ya')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Es','hola','adios')

The following commands delete all English records using cursor-based Embedded SQL.

#sqlcompile path=Sample
  NEW %ROWCOUNT,%ROWID
  &sql(DECLARE WPCursor CURSOR FOR 
        SELECT Lang FROM WordPairs
        WHERE Lang='En')
   &sql(OPEN WPCursor)
        QUIT:(SQLCODE'=0)
   FOR { &sql(FETCH WPCursor)
        QUIT:SQLCODE 
        &sql(DELETE FROM WordPairs
       WHERE CURRENT OF WPCursor)
    IF SQLCODE=0 {
    WRITE !,"Delete succeeded"
    WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
    ELSE {
    WRITE !,"Delete failed, SQLCODE=",SQLCODE }
    }
    &sql(CLOSE WPCursor)

This command then deletes all French records.

DELETE FROM WordPairs WHERE Lang='Fr'

The final two commands display the remaining records in the table and delete the table.

SELECT %ID,* FROM SQLUser.WordPairs
DROP TABLE SQLUser.WordPairs