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)