Chapter 55 SQL command INSERT (4)

Embedded SQL and dynamic SQL examples

The following embedded SQL example creates a new table SQLUser.MyKids. The following example uses INSERT to populate this table with data. After inserting the sample, an example of deleting SQLUser.MyKids is provided.

ClassMethod Insert2()
{
	&sql(
		CREATE TABLE SQLUser.MyKids 
		(
			KidName VARCHAR(16) UNIQUE NOT NULL,
			KidDOB INTEGER NOT NULL,
			KidPetName VARCHAR(16) DEFAULT 'no pet'
		) 
	)
	if SQLCODE=0 {
		w !,"Table created" 
	} elseif SQLCODE=-201 {
		w !,"Table already exists"  
		q
	} else {
		w !,"CREATE TABLE Failed. SQLCODE=",SQLCODE 
	}
}

The following embedded SQL example inserts a row with two field values (the third field KidPetName takes the default value). Note that the table schema name is provided by the #SQLCompile Path macro as the schema search path:

ClassMethod Insert3()
{
	#SQLCompile Path = Sample
	n SQLCODE,%ROWCOUNT,%ROWID
	&sql(
		INSERT INTO MyKids 
		(
			KidName, KidDOB
		) 
		VALUES 
		(
			'Molly', 60000
		)
	)
	if SQLCODE = 0 {
		w !,"Insert successful"
		w !,"Row count=",%ROWCOUNT
		w !,"Row ID=",%ROWID
		q 
	} elseif SQLCODE = -119 {
		w !,"Duplicate record not written",!
		w %msg,!
		q 
	} else {
		w !,"Insert failed, SQLCODE=",SQLCODE 
	}
}

The following embedded SQL example inserts rows with three field values using the column order of the table:

ClassMethod Insert4()
{

	n SQLCODE,%ROWCOUNT,%ROWID
	&sql(
		INSERT INTO SQLUser.MyKids VALUES ('Josie','40100','Fido') 
	)
	if SQLCODE = 0 {
		w !,"Insert successful"
		w !,"Row count=",%ROWCOUNT
		w !,"Row ID=",%ROWID
		q 
	} elseif SQLCODE = -119 {
		w !,"Duplicate record not written",!
		w %msg,!
		q 
	} else {
		w !,"Insert failed, SQLCODE=",SQLCODE 
	}
}

The following embedded SQL example uses host variables to insert rows with two field values. The insertion syntax used here specifies the COLUMN=VALUE pair:

ClassMethod Insert5()
{
	#SQLCompile Path=Sample
	n SQLCODE,%ROWCOUNT,%ROWID
	s x = "Sam"
	s y = "57555"
	&sql(
		INSERT INTO MyKids 
		SET 
			KidName = :x, KidDOB = :y 
	)
	if SQLCODE = 0 {
		w !,"Insert successful"
		w !,"Row count=",%ROWCOUNT
		w !,"Row ID=",%ROWID
		q 
	} elseif SQLCODE = -119 {
		w !,"Duplicate record not written",!
		w %msg,!
		q 
	} else {
		w !,"Insert failed, SQLCODE=",SQLCODE 
	}
}

The following embedded SQL example inserts a row with three field values using an array of host variables. Array elements are numbered in column order. Please note that the array value provided by the user starts with myarray(2); The first array element corresponds to the RowID column, which is automatically provided and cannot be defined by the user:

ClassMethod Insert6()
{
	#SQLCompile Path=Sample
	n SQLCODE, %ROWCOUNT, %ROWID
	s myarray(2) = "Deborah"
	s myarray(3) = 60200
	s myarray(4) = "Bowie"
	&sql(
		INSERT INTO MyKids VALUES :myarray()
	)
	if SQLCODE = 0 {
		w !,"Insert successful"
		w !,"Row count=",%ROWCOUNT
		w !,"Row ID=",%ROWID
		q 
	} elseif SQLCODE = -119 {
		w !,"Duplicate record not written",!
		w %msg,!
		q 
	} else {
		w !,"Insert failed, SQLCODE=",SQLCODE 
	}
}

The following dynamic SQL example uses the% SQL.Statement class to insert a row with three field values. Note that the table schema name is provided as a schema search path in the% New() method:

ClassMethod Insert7()
{
	s x = "Noah"
	s y = "61000"
	s z = "Luna"
	s sqltext = "INSERT INTO MyKids (KidName,KidDOB,KidPetName) VALUES (?,?,?)"
	s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
	s qStatus = tStatement.%Prepare(sqltext)
	if qStatus '= 1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rtn = tStatement.%Execute(x, y, z)
	if rtn.%SQLCODE = 0 {
		w !,"Insert successful"
		w !,"Row count=",rtn.%ROWCOUNT
		w !,"Row ID=",rtn.%ROWID 
	} elseif rtn.%SQLCODE = -119 {
		w !,"Duplicate record not written",!,rtn.%Message
		q 
	} else {
		w !,"Insert failed, SQLCODE=",rtn.%SQLCODE 
	}
}

The following embedded SQL example shows the inserted record and then deletes the SQLUser.MyKids table:

ClassMethod Insert8()
{
	s myquery = "SELECT * FROM SQLUser.MyKids"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus '= 1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
	&sql(DROP TABLE SQLUser.MyKids)
	if SQLCODE = 0 {
		w !,"Deleted table"
		q 
	} else {
		w !,"Table delete failed, SQLCODE=",SQLCODE 
	}
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).Insert8()
KidName KidDOB  KidPetName
Molly   60000   no pet
Josie   40100   Fido
Sam     57555   no pet
Deborah 60200   Bowie
Noah    61000   Luna
 
5 Rows(s) Affected
End of data
Deleted table

The following embedded SQL example demonstrates the use of host variable arrays. Note that for host variable arrays, you can use a dynamic local array with an unspecified last subscript to pass an array of values to insert at run time. For example:

ClassMethod Insert9()
{
  n SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO Sample.Employee VALUES :emp('profile',))
  w !,"SQL Error code: ",SQLCODE," Row Count: ",%ROWCOUNT
}

Set each field in the inserted Employee row to:

emp("profile",col)

Where, "col" is the column number of the field in the Sample.Employee table.

The following example shows how to use the results of a SELECT query as data input to an INSERT statement to provide data for multiple rows:

INSERT INTO StudentRoster (NAME,GPA,ID_NUM)
     SELECT FullName,GradeAvg,ID
     FROM temp WHERE SchoolYear = '2004'