以下に示す一連のプログラム例のうち、最初のプログラムでは、3 つの列を持つ SQLUser.WordPairs という名前のテーブルを作成します。その次のプログラムではテーブルに 6 つのレコードを挿入します。以降のプログラムでは、カーソル・ベースの埋め込み SQL を使用してすべての English レコードを削除し、ダイナミック SQL を使用してすべての French レコードを削除します。最後のプログラムでは、残りのレコードを表示し、その後テーブルを削除します。
CreateTable
&sql(CREATE TABLE SQLUser.WordPairs (
Lang CHAR(2) NOT NULL,
Firstword CHAR(30),
Lastword CHAR(30) )
)
IF SQLCODE=0 {
WRITE !,"Table created" }
ELSEIF SQLCODE=-201 {WRITE !,"Table already exists" QUIT}
ELSE {
WRITE !,"CREATE TABLE failed. SQLCODE=",SQLCODE }
InsertSixRecords
#sqlcompile path=Cinema,Sample
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('En','hello','goodbye'))
IF SQLCODE = 0 { WRITE !,"1st record inserted" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE
QUIT}
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Fr','bonjour','au revoir'))
IF SQLCODE = 0 { WRITE !,"2nd record inserted" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE QUIT}
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('It','pronto','ciao'))
IF SQLCODE = 0 { WRITE !,"3rd record inserted" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE QUIT}
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Fr','oui','non'))
IF SQLCODE = 0 { WRITE !,"4th record inserted" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE QUIT}
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('En','howdy','see ya'))
IF SQLCODE = 0 { WRITE !,"5th record inserted" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE QUIT}
&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Es','hola','adios'))
IF SQLCODE = 0 { WRITE !,"6th record inserted",!!
SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data" }
ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE }
EmbeddedSQLDeleteEnglish
#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)
DynamicSQLDeleteFrench
SET sqltext = "DELETE FROM WordPairs WHERE Lang=?"
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(sqltext)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rtn = tStatement.%Execute("Fr")
IF rtn.%SQLCODE=0 {
WRITE !,"Delete succeeded"
WRITE !,"Row count=",rtn.%ROWCOUNT," RowID of last record=",rtn.%ROWID }
ELSE {
WRITE !,"Delete failed, SQLCODE=",rtn.%SQLCODE }
DisplayAndDeleteTable
SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
&sql(DROP TABLE SQLUser.WordPairs)
IF SQLCODE=0 {
WRITE !!,"Table deleted"
QUIT }
ELSE {
WRITE !,"Table delete failed, SQLCODE=",SQLCODE }