Please run the two Embedded SQL programs in the order shown. (It is necessary to use two embedded SQL programs here because embedded SQL cannot compile an INSERT statement unless the referenced table already exists.)
WRITE !,"Creating table"
&sql(CREATE TABLE Sample.Students (
StudentName VARCHAR(30),
StudentAge INTEGER,
StudentID IDENTITY))
IF SQLCODE=0 {
WRITE !,"Created table, SQLCODE=",SQLCODE }
ELSEIF SQLCODE=-201 {
WRITE !,"Table already exists, SQLCODE=",SQLCODE }
WRITE !,"Populating table"
NEW %ROWCOUNT,%ROWID
&sql(INSERT INTO Sample.Students (StudentName,StudentAge)
SELECT Name,Age FROM Sample.Person WHERE Age <= '21')
IF SQLCODE=0 {
WRITE !,%ROWCOUNT," records added, last RowID is ",%ROWID,! }
ELSE {
WRITE !,"Insert failed, SQLCODE=",SQLCODE }
&sql(SELECT LAST_IDENTITY()
INTO :insertID
FROM Sample.Students)
WRITE !,"INSERT Last Identity is: ",insertID,!!
/* Cursor-based SELECT Query */
&sql(DECLARE C1 CURSOR FOR
SELECT StudentName INTO :name FROM Sample.Students
WHERE StudentAge = '17')
&sql(OPEN C1)
QUIT:(SQLCODE'=0)
&sql(FETCH C1)
WHILE (SQLCODE = 0) {
WRITE name," is seventeen",!
&sql(FETCH C1) }
&sql(CLOSE C1)
WRITE !,%ROWCOUNT," records queried, last RowID is ",%ROWID,!
&sql(SELECT LAST_IDENTITY()
INTO :qId)
WRITE !,"SELECT Last Identity is: ",qId,!
&sql(DROP TABLE Sample.Students)